Discussion :

[kheops37]

Bonjour à tous,

Pour mon application, j'analyse des trames réseaux que je décortique puis je stocke les données dans une table. De temps en temps il y a des "évènements" qui sont stockés dans une autre table.
La première table compte environ 4 millions de lignes et la secondes 100 000. Il y a une purge automatique qui permet de ne garder les données que sur 20 jours mais la base fait quand même 2Go avec seulement ces 2 tables !

Lorsque je fais des requêtes sur la base c'est très long. Certaines requêtes mettent plus de 5 min à s'éxecuter (il y a parfois des jointures entre les deux tables aussi...).

J'ai tenté d'optimiser un peu les choses en mettant des index mais ça na pas changer fondamentalement les temps d'exécution (surement qu'il y a mieux à faire mais ma question n'est pas là).

En fait j'aimerai vous demander si pour ce genre d'application il n'y a pas mieux pour stocker les données qu'une base de données. Les requêtes sont simples et je me demande s'il n'y a pas une manière plus efficace de stocker les donner.

J'utilise une base SQL Server Express 2005. Je tiens à vous préciser que ce n'est pas moi qui ai fais les choix de conception, de SGBD, etc. Et les gens qui l'on fait, non pas calculé ni testé l'application avec une base pleine.... et donc maintenant que c'est en production on a des timeout partout parce que les requêtes sont super longues...

Merci d'avance

Matthieu

[CinePhil]

Un article qui peut t'aider à optimiser... ou à argumenter auprès de ta hiérarchie pour améliorer la situation.

[kheops37]

J'ai un peu commencé à lire ces articles. Mais ma question n'est pas là.

La question c'est : est ce qu'une base est bien le bon model de stockage de données?

Merci quand même

Matthieu

[CinePhil]

Tu disais au départ :

j'analyse des trames réseaux que je décortique puis je stocke les données dans une table. De temps en temps il y a des "évènements" qui sont stockés dans une autre table.

Si je comprends bien, tu n'enregistres pas la trame mais le résultat du décorticage pour en faire des données utilisables.

Tout dépend de l'utilisation que tu fais des données générées à partir des trames.
- Si tu utilises ensuite les données générées au niveau de chaque colonne, la base de données me semble un bon outil. Si c'est pour ensuite rassembler les trames la base de donnes ne sert effectivement pas à grand chose.
- Si tu veux faire des statistiques sur le nombre de morceaux de tel type ==> BDD
- Si tu veux pister, parmi les trames, les intrusions inopinées sur ton réseau ==> Pas BDD, ou alors seulement pour enregistrer les incidents (objet de la deuxième table peut-être ?)

Pour visser une vis, on prend un tournevis. Pour en visser 2000, on a peut-être intérêt à sortir la visseuse électrique. Dans les deux cas, on n'utilise pas un marteau !

[kheops37]

Oui c'est ça ! j'enregistre le décorticage des trames dans une table qui s'allonge très vite...

je me demandais donc juste s'il existait une structure de données différente d'une BDD pour stocker ça parce que les traitements sont très (trop) long. Donc je crois qu'il va falloir remanier la base et les requêtes.... aie !

Donc voilà je voulais éviter tout ça sachant qu'en plus (évidemment) l'usine à gaz est en production (euh venait pas faire un braquage, y a même pas de dérivé pétrochimique qui en sort de là )

Merci !

Matthieu

[SQLpro]

Si vous ne respectez pas les règles d'utilisation d'un SGBD relationnel, n'attendez pas de performances !

En effet les SGBD relationnels sont fait, comme leur nom indique, pour stocker des informations sous forme relationnelle, c'est à dire dans des tables liées les unes aux autres par un modèle relationnel.

Tout ce que vous ferez qui ne respecte pas la modélisation de données, et notamment le fait de ne pas respecter des formes normales, ne fera que pourrir les perfoamnces de votre système. Même avec des index cela ne servira à rien.

En revanche, bien modélisé et avec des index, des bases contenant des centaines de Go répondent rapidement !

Lisez les articles que j'ai écrit à ce sujet :
1) http://blog.developpez.com/sqlpro?ti...s_sur_ms_sql_s
2) http://sqlpro.developpez.com/optimisation/1/
3) http://sqlpro.developpez.com/optimisation/3/

A +

[kheops37]

Bonjour,

Je suis d'accord avec ce que vous dites.

Mais voici le format de ma table (exprimé de manière générique):

ID Date ID2 Val1 Val2 Val3 Val4 Val5

ID : clé de la table
Date : date de création de la ligne
ID2 : clé étrangère qui permet d'indiquer quel noeud à généré cette trame
Val1 .. Val5 : valeurs numériques décortiquées des trames

Pensez-vous qu'il y ait une quelconque relation à ajouter ?
Peut-être que je me trompe mais je ne crois pas que ce soit possible.
En ce qui concerne les index et les optimisations des requêtes, il y a assurément des choses à faire mais ce n'était pas l'objet de ma question : cela fait donc remanier une bonne partie de la base et du code qui va avec.

Merci

Matthieu

[CinePhil]

2 millions de lignes avec uniquement des colonnes numériques + une de date, ce n'est pas si énorme que ça !

Peut-être y a t-il aussi un problème de dimensionnement du serveur ?

On peut avoir la requête qui dure 5 minutes et que vous trouvez trop longue ?

[SQLpro]

Vous violez la première forme normale :
Val1 Val2 Val3 Val4 Val5 => sont une énumération de valeurs

Votre modèle devrait être :
TABLE 1 :
T1ID : clé de la table
T1Date : date de création de la ligne
TnID : clé étrangère qui permet d'indiquer quel nœud à généré cette trame (venant de la table n, clef primaire et étrangères doivent avoir le même nom dans les deux tables puisque c'est la même information)

Table 2 :
T2ID : clef de la table
T1ID : clef étrangère référençant la clef primaire de la table 1 (donc contrainte FK)
T2VAL : valeur.

Si besoin est, rajoutez la position de la valeur si cela revêt une certaine importance dans votre cas :
Table 2 :
T2ID : clef de la table
T1ID : clef étrangère référençant la clef primaire de la table 1 (donc contrainte FK)
T2POS : position
T2VAL : valeur.

Vous n'avez indiqué aucune des informations nécessaire :
contraintes PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY, NOT NULL et DEFAULT !

Il faut en sus rjouter les index...

bref vous n'avez fait mal que 20% du travail !

Relisez ce que je vous disais :
Tout ce que vous ferez qui ne respecte pas la modélisation de données, et notamment le fait de ne pas respecter des formes normales, ne fera que pourrir les performances de votre système. Même avec des index cela ne servira à rien.

A +

[kheops37]

Cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select *
from table
where date >= '20081120 00:00:00'
and date <= '20081121 00:00:00'

met beaucoup de temps je trouve (sur une table restreinte de 700Mo, plus de 30sec !) et lis plus de 67 000 pages pour retourner 300 lignes. Je trouve ça vraiment énorme sachant que si je ne mets qu'une condition ça lit autant de pages (et met seulement quelques secondes en plus) pour retourner 250 000 pages ! Où est l'erreur ?

Ok pour la forme normal Mais je n'effectue pas de calcul ni de tri sur ces colonnes.

Merci

Matthieu

PS : dans le but d'accélérer un peu tout ça, j'ai indexé la colonne Date en plus de la clé primaire mais les temps de traitement restent long

[Waldar]

Tout ce que vous ferez qui ne respecte pas la modélisation de données, et notamment le fait de ne pas respecter des formes normales, ne fera que pourrir les performances de votre système. Même avec des index cela ne servira à rien.

J'interviens ici pour tempérer cette remarque, qui est valide pour toute base à vocation applicative et transactionnelle, mais qui n'est pas forcément juste pour des bases dédiées au reporting type datawarehouse / datamart, dans lesquelles la dénormalisation est courante et même vivement conseillée, justement pour accéler les performances (en l'occurence, l'extraction de données).

[CinePhil]

Cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select *
from table
where date >= '20081120 00:00:00'
and date <= '20081121 00:00:00'

met beaucoup de temps je trouve

Au point 7 du tableau du chapitre 9 de cet article de SQLPro, je lis :

évitez les fourchettes < et > pour des valeurs discrètes...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT *
FROM   T_FACTURE
WHERE  FAC_DATE > '2000-06-18' 
  AND  FAC_DATE < '2000-07-15'

...préférez le BETWEEN

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM   T_FACTURE
WHERE  FAC_DATE BETWEEN '2000-06-18' AND '2000-07-14'

[SQLpro]

Ok pour la forme normal Mais je n'effectue pas de calcul ni de tri sur ces colonnes.

mais un modèle de données ne sert pas à faire des tri !!! Et il n'y a pas d'incidence entre un modèle de données et des calculs.
En revanche un modèle de données est optimisé pour faire des requêtes.
tant que vous ne modéliserez pas votre base dans les règles de l'art inutile de demander des performances. C'est la 3eme fois que je vous le dit. Donc je me pose la question : est-vous aveugle, sourd ou autiste ?

A +

[SQLpro]

SELECT *
FROM TABLE
WHERE date >= '20081120 00:00:00'
AND date <= '20081121 00:00:00'

met beaucoup de temps je trouve (sur une table restreinte de 700Mo, plus de 30sec !) et lis plus de 67 000 pages pour retourner 300 lignes.

Y a t-il un index sur DATE ?

A +