Discussion :

[devnet75]

Bonjour tout le monde,

je récupère aujourd'hui un projet un peu spécial. Il s'agit d'un outil de statistiques enregistrant énormément de données en base. Pour vous donner un ordre de grandeur, la table principale contient aujourd'hui 716 millions de lignes....

Un outil a été développé pour faire des recherches sur cette table. L'outil effectue une recherche sur deux critères (toujours renseignés):
- un champ date (avec un between)
- un identifiant de client (alpha-numérique pouvant contenir des caractères spéciaux). Cet identifiant est actuellement stocké dans une colonne de type varchar(20).
- un identifiant bis de client (alpha-numérique pouvant contenir des caractères spéciaux). Cet identifiant est actuellement stocké dans une colonne de type varchar(20).

Un index existe sur cette table. Voici ses caractéristiques :
- NonClustered
- Ordre des colonnes : Identifiant client, identifiant bis client, date

La table possède un nombre élevé de colonnes : 50. Toutes les colonnes doivent être remontées lors d'une recherche.

Actuellement, l'outil exécute une requête en 45 - 60 secondes environ. L'idée serait de savoir si il est possible d'optimiser cette requête, car j'ai peur qu'avec un nombre de données croissant, le temps d'exécution devienne de plus en plus long. Qu'en pensez-vous ?

Je vous remercie pour vos avis éclairés

[escartefigue]

Bonjour,

Ces identifiants qui servent de critères de recherche sont des critères fonctionnels par exemple un nom ou un prénom, ou des identifiants techniques ?

Y a -t- il des critères de jointure ou de filtrage sur ces critères (communiquez vos requêtes les plus critiques) ?

Faites vous des recherches génériques (where moncritère like 'xxxx%')

[devnet75]

Bonjour,

tout d'abord merci pour votre réponse

Ces identifiants sont générés par un outil tiers. On ne connait que la taille maximale et on sait qu'ils peuvent contenir n'importe quoi (principalement des chiffres et des signes tels que : + ). Par contre, je ne comprends pas votre seconde question La requête ne sélectionne des données que de cette table, aucune autre table n'est appelée.

Pour votre dernière question, non pas de Like mais directement des idclient = "XXXXX"

Encore merci de vous intéressés à mon cas

[julien94320]

Une solution (pas forcément la plus propre), mais la plus performante de prime abord serait de modifier l'index existant en créant un index couvrant avec l'ensemble des colonnes a afficher c'est a dire via un include dans l'index

voici un peu d'aide :

http://sqlpro.developpez.com/cours/quoi-indexer/

Vous pouvez aussi completer votre index en le filtrant si certaines années ne sont plus recherchez par exemple.

La solution la plus pragmatique serait de modifier la structure de votre table afin de diminuer l'ensemble des colonnes et revoir la modélisation vous pouvez aussi partionner cette table en fonction des années afin d'archiver chaque années sur des espaces disque spécifiques.

A+

[escartefigue]

Ces identifiants sont générés par un outil tiers. On ne connait que la taille maximale et on sait qu'ils peuvent contenir n'importe quoi (principalement des chiffres et des signes tels que : + ).

S'il s'agit d'identifiants techniques, il est nettement préférable, notamment pour les perfs (et pour plein d'autres raison) d'utiliser un type integer (voir bigint si vous devez dépasser à terme 4 294 967 295 lignes).
50 colonnes pour une table, ce n'est pas énorme, par contre si ces colonnes sont larges ou contiennent des images, ça peut couter très cher

Question subsidiaire : Est-ce que l'identifiant est unique, sinon combien de lignes sont ramenées en moyenne par requête ?

Et svp, communiquez la ou les requêtes qui posent souci, quitte à changer les noms des tables et colonnes si confidentialité il y a

[devnet75]

Une solution (pas forcément la plus propre), mais la plus performante de prime abord serait de modifier l'index existant en créant un index couvrant avec l'ensemble des colonnes a afficher c'est a dire via un include dans l'index

voici un peu d'aide :

http://sqlpro.developpez.com/cours/quoi-indexer/

Vous pouvez aussi completer votre index en le filtrant si certaines années ne sont plus recherchez par exemple.

La solution la plus pragmatique serait de modifier la structure de votre table afin de diminuer l'ensemble des colonnes et revoir la modélisation vous pouvez aussi partionner cette table en fonction des années afin d'archiver chaque années sur des espaces disque spécifiques.

A+

La modification de la structure de la table pour l'éclater en plus petites tables n'est pas possible. En effet, l'outil qui intègre les stats est fiable et ne peut pas être modifié. Un partitionnement serait-il une bonne solution du coup ? Sachant que les 716 millions de lignes actuelles ne correspondent qu'aux 4 derniers mois de 2016

S'il s'agit d'identifiants techniques, il est nettement préférable, notamment pour les perfs (et pour plein d'autres raison) d'utiliser un type integer (voir bigint si vous devez dépasser à terme 4 294 967 295 lignes).
50 colonnes pour une table, ce n'est pas énorme, par contre si ces colonnes sont larges ou contiennent des images, ça peut couter très cher

Question subsidiaire : Est-ce que l'identifiant est unique, sinon combien de lignes sont ramenées en moyenne par requête ?

Et svp, communiquez la ou les requêtes qui posent souci, quitte à changer les noms des tables et colonnes si confidentialité il y a

Impossible d'utiliser un integer. Les identifiant comprennent des caractères spéciaux et cela ne peut pas être modifié. Au niveau des colonnes, il ne s'agit que de colonnes de types varchar, int et datetime. Voici comme demandé la requête en question :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT * 
FROM  StatsTable
WHERE 
	Date >= @dateDebut 
	AND Date < @dateFin
	AND
	(
		NumeroClient = @numero
		OR NumeroBisClient = @numero
	)

Merci à tous pour votre aide

[julien94320]

Le partionnement peut etre a tester ( a voir la clé de partionnement Mensuel ? Annuel ? )

Tout comme l'index couvrant voir filtrant ...

Une vue indexer peut aussi répondre a vos besoins ...

Essayer de vous constituer un environement de test avec de la volumétrie et tester les solutions proposés ...

N'oubliez pas de mettre a jour vos statistiques régulièrement vu que la quantité de données augmente régulièrement ... ( un plan de maintenance existe t'il ? )

Bon courage

Julien