Besoins de conseils pour travailler avec une grosse base...
Le
Jerome
Salut à tous,
J'ai une table qui contient environ 2 millions d'informations hierarchisées
sous la forme d'un arbre intervallaire du genre :
A
|>B
| |
| >C
| >D
|>E
Ce qui donne à plat :
Id Lib BG BD Niveau
1 A 1 10 1
2 B 2 7 2
3 C 3 4 3
4 D 5 6 3
5 E 8 9 1
Jusque là rien de bien sorcier. J'ai cependant deux problèmes et une
intérrogation.
Un problème de rapidité car dans mon application (un site web), j'affiche
pour chaque page un fil d'ariane du genre :
A > B > D
pour que l'utilisateur puisse revenir depuis son localisation actuelle au
niveaux précédents. J'ai donc une procédure toute simple qui à partir d'un
id donné (indexé) retrouve l'ensemble des noeuds précédents en retournant
l'id et le libellé de chaque. Bien que simple, la procédure stockée est très
gourmande en ressource et me limite consérablement dans le nombre
d'utilisateur et la célérité du site en devient catastrophique. Rechercher
un "chemin" dans 2 millions d'enregistrements est lourd. Comment je peux
réduire ce temps ? Je vois mal comment réduire la procédure stockée, on peut
pas faire plus simple. Un cache SQL (mais sur 2 millions d'enregistremnts,
quel intéret) ? Un cube OLAP (je connais pas cette techno) ?
L'autre problème concerne l'ajout de données.Toute la journée, nous
importons de nouvelles données dans cette table. Malgré plusieurs essais
infructueux, à chaque import, la lecture est impossible dans la base
(note très bêtement, nous utilisons le même utilisateur dans notre
logiciel d'import et sur le site web) le site web n'est donc plus
disponible. Comment gérer l'import, sans conflit avec les bords et sans
arrêt d'exploitation ?
L'intérogation, tourne autour d'une autre table qui contient pas loin de 60
millions d'enregistrements. Cette table est structurée très simplement de la
manière suivante :
Id IdNode Lib
ou Id correspond à l'id de l'enregistrement, Lib à un libéllé quelconque et
IdNode à l'id de l'une des informations de la hierarchie citée plus haut.
Comment je peux faire, sachant que je mis un index sur IdNode pour accélérer
le traitement d'un simple SELECT Id, Lib FROM matable WHERE IdNode =
@IdNode. Encore une fois rechercher des éléments dans une table de 60
millions de données mais trop de temps. Mais comment font donc les moteurs
de recherche !!! Bon je suppose que eux il partitionne mais moi j'ai pas
les crédits pour
Pour information, la base tourne sur un Dell PowerEdge 2850 BiProc Xeon
3.2Ghz avec 2Go de RAM et 70Go SCSI en Raid5 utilisé à seulement 20%, côté
data. Il n'y a que SQL Server sur cette machine et uniquement cette base de
données (qui fait dans les 20Go).
Si vous avez des conseils à me donner ou des références sur le travail d'une
base avec ce genre de volume d'information je suis preneur !!
Merci
Jérôme
J'ai une table qui contient environ 2 millions d'informations hierarchisées
sous la forme d'un arbre intervallaire du genre :
A
|>B
| |
| >C
| >D
|>E
Ce qui donne à plat :
Id Lib BG BD Niveau
1 A 1 10 1
2 B 2 7 2
3 C 3 4 3
4 D 5 6 3
5 E 8 9 1
Jusque là rien de bien sorcier. J'ai cependant deux problèmes et une
intérrogation.
Un problème de rapidité car dans mon application (un site web), j'affiche
pour chaque page un fil d'ariane du genre :
A > B > D
pour que l'utilisateur puisse revenir depuis son localisation actuelle au
niveaux précédents. J'ai donc une procédure toute simple qui à partir d'un
id donné (indexé) retrouve l'ensemble des noeuds précédents en retournant
l'id et le libellé de chaque. Bien que simple, la procédure stockée est très
gourmande en ressource et me limite consérablement dans le nombre
d'utilisateur et la célérité du site en devient catastrophique. Rechercher
un "chemin" dans 2 millions d'enregistrements est lourd. Comment je peux
réduire ce temps ? Je vois mal comment réduire la procédure stockée, on peut
pas faire plus simple. Un cache SQL (mais sur 2 millions d'enregistremnts,
quel intéret) ? Un cube OLAP (je connais pas cette techno) ?
L'autre problème concerne l'ajout de données.Toute la journée, nous
importons de nouvelles données dans cette table. Malgré plusieurs essais
infructueux, à chaque import, la lecture est impossible dans la base
(note très bêtement, nous utilisons le même utilisateur dans notre
logiciel d'import et sur le site web) le site web n'est donc plus
disponible. Comment gérer l'import, sans conflit avec les bords et sans
arrêt d'exploitation ?
L'intérogation, tourne autour d'une autre table qui contient pas loin de 60
millions d'enregistrements. Cette table est structurée très simplement de la
manière suivante :
Id IdNode Lib
ou Id correspond à l'id de l'enregistrement, Lib à un libéllé quelconque et
IdNode à l'id de l'une des informations de la hierarchie citée plus haut.
Comment je peux faire, sachant que je mis un index sur IdNode pour accélérer
le traitement d'un simple SELECT Id, Lib FROM matable WHERE IdNode =
@IdNode. Encore une fois rechercher des éléments dans une table de 60
millions de données mais trop de temps. Mais comment font donc les moteurs
de recherche !!! Bon je suppose que eux il partitionne mais moi j'ai pas
les crédits pour
Pour information, la base tourne sur un Dell PowerEdge 2850 BiProc Xeon
3.2Ghz avec 2Go de RAM et 70Go SCSI en Raid5 utilisé à seulement 20%, côté
data. Il n'y a que SQL Server sur cette machine et uniquement cette base de
données (qui fait dans les 20Go).
Si vous avez des conseils à me donner ou des références sur le travail d'une
base avec ce genre de volume d'information je suis preneur !!
Merci
Jérôme
Poser une question
colonne contenant le chemin d'accès complet de chaque enregistrement, du
genre "A.B.D". Faire des recherches devient alors un jeu d'enfant mais les
procédures d'insertion et de délétion deviennent un peu plus compliquées.
Vous trouverez un exemple complet dans le livre de Ben-Gan et Moreau,
"Advanced Transact-SQL for SQL Server 2000" mais d'autres exemples existent
également sur le web.
Pour vos problèmes d'insertion et d'interrogation avec votre seconde table,
j'imagine que le design de votre table ou de vos procédures ne sont pas très
au point; cela dit sans vous offenser. Le fait d'ajouter un index va être
inutile si celui-ci n'est pas utilisé mais comme on n'a aucun autre détail,
difficile d'en dire plus. Vous devriez examiner vos plans d'exécution.
Finalement, vous avez fait un mauvais choix en utilisant un Raid 5 si
celui-ci n'est occupé qu'à 20% car les Raids 5 ont une très mauvaise
performance en écriture et ne sont pas à recommander si plus de 10% de la
base de donnée travaille en ré-écriture; ce qui semble être votre cas. Dans
votre cas, l'utilisation d'un Raid 1 aurait probablement été plus appropriée
car vous auriez eu la même performance en lecture qu'un Raid 5 à trois
disques et sans pénalité de performance en écriture.
De plus, en mettant deux disques en Raid 1, vous auriez-pu utiliser le
troisième séparément pour mettre vos fichiers Logs. Séparer les fichiers
Logs des fichiers Datas sur des disques différents est la première chose à
faire pour accélérer SQL-Server. Si vous avez de la place, créer à la place
un Raid 0 pour doubler la vitesse d'écriture pour vos fichiers Logs pourrait
même être une option intéressante dans votre cas.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Jerome" news:
Humm effectivement ça pourrait être une solution. Mais comment stocker une
suite d'ID (puisque ce sont les id de chaque noeud) dans une seule
colonne... Vous savez à quoi je dois chercher pour trouver ce genre d'info ?
Ca ne m'offense pas du tout :) Je suis loin de connaître SQL Server sur le
bout des doigts, c'est pour ça que je demande conseil.
Si si je vous ai donné la structure... en gros :
Id int
IdNode int
Lib varchar(50)
Id est la clé primaire, IdNode est indexé. La requête utilisé est simplement
un SELECT Id, Lib FROM matable WHERE IdNode=@IdNode
Bon attention, aujourd'hui obtenir le résultat de cette requête est très
rapide mais je me demande comment dans l'avenir accéléré le traitement avec
une prévision de 50,100 voir 200 millions d'enregistrements. Je cherche
juste à prévoir.
Merci pour ces informations. Aujourd'hui on fait uniquement de la lecture.
L'écriture fonctionne aujourd'hui via un serveur tiers qui réplique à une
heure donnée sur celui-ci et ce pour différentes raisons. Toujours est-t'il
qu'à chaque tentative d'écriture directement dans la base, nous nous sommes
frottés aux problèmes du blocage de la base en lecture pendant l'ajout de
données (comme je l'indiquais dans mon mail).
Merci
Jérôme
des valeurs numériques d'ID comme "1.400.12.102" que des identifiants
alphanumériques. Si vous ne désirez-pas acheter le livre de Ben-Gan et
Moreau, vous pouvez chercher le group m.p.sqlserver.programming avec des
mot-clefs tel que tree, parent, child, self-referencing table, etc. Par
example:
http://groups.google.ca/group/micro...94be18c652
Pour votre deuxième question, si vous n'avez pas de problème aujourd'hui, je
ne vois pas pourquoi vous en auriez plus tard.
Finalement, pour vos problèmes de blocage, j'imagine que vous travaillez en
mode batch et que toute la table est lockée pendant cette opération. Si
vous voulez assurer une disponibilité en continue de la table, vous allez
probablement devoir abandonner les longues opérations en mode batch.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Jerome" news: