Optimisation table avec des millions de lignes

**devnet75** · 23/11/2016, 14h53

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** · 23/11/2016, 15h08

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** · 23/11/2016, 15h13

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** · 23/11/2016, 15h33

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** · 23/11/2016, 15h49

Envoyé par devnet75

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** · 23/11/2016, 16h03

Envoyé par 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+

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

Envoyé par escartefigue

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** · 23/11/2016, 16h43

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

**escartefigue** · 23/11/2016, 16h58

S'il s'agit d'un identifiant primaire, alors c'est une erreur d'avoir utilisé des caractères, qui plus est des caractères spéciaux, le bon type pour un identifiant primaire est l'integer
Mais bon si c'est irrémédiable, il faut faire avec

A défaut du char eut été un peu mieux que du varchar

Vérifiez que votre host variable @numero est bien du même type que vos colonnes NumeroClient et NumeroBisClient

Effectivement le partitionnement peut être une solution pour les perfs mais aussi pour votre exploitation : vous pourrez par exemple archiver ou passer des utilitaires sur les partitions anciennes sans limiter l'utilisation des partitions récentes, c'est un gros avantage

.

**François DORIN** · 23/11/2016, 17h16

Bonjour,

Ici, vous avez plusieurs possibilités. Il faut juste les tester pour déterminer laquelle sera la plus rapide.

Pour ma part, je testerai un index, avec comme ordre des colonnes date, numéro client, numéro bis client. Ainsi, le temps de réponse pour une requête donnée devrait être à peu près constant, quelque soit la volumétrie totale de la table.

La date en premier abord peut sembler étonnant. Mais voici l'explication : la clé primaire est un VARCHAR(20). Faire une recherche la dessus n'est pas pratique, car il faut comparer au moins 3 mots machines (en 64bits !) pour pouvoir faire la comparaison. Avec un datetime, 1 mot suffit. De plus, la comparaison de VARCHAR est impactée par la collation, et cela peut ralentir fortement la recherche.

Ensuite, au niveau des critères de recherche, si possible, préciser la collation et en utiliser une qui fait une recherche sensible à la casse et aux accents (recherche binaire) pour les numéros de client.

Enfin, l'index pourra être tester en étant couvrant ou non couvrant. Difficile de se prononcer sur celui qui sera le plus efficace ! Cela va dépendre des données, des types de données et du nombre d'éléments filtrés.

Pour arriver à cette proposition, je fais deux hypothèses (à vérifier si elles sont vraies !), c'est que, d'une part, la table agrège des données horodatées. Ce que je veux dire, c'est que l'ajout de données ajoute toujours de nouvelles données (date courante), et non pas d'anciennes données (datant par exemple de plus de 1 an). Une sorte de table de log en quelque sorte. D'autre part, les données ne sont pas modifiées (pas de UPDATE).

**escartefigue** · 23/11/2016, 17h24

Envoyé par dorinf

La date en premier abord peut sembler étonnant. Mais voici l'explication : la clé primaire est un VARCHAR(20). Faire une recherche la dessus n'est pas pratique, car il faut comparer au moins 3 mots machines (en 64bits !) pour pouvoir faire la comparaison. Avec un datetime, 1 mot suffit. De plus, la comparaison de VARCHAR est impactée par la collation, et cela peut ralentir fortement la recherche.

Oui, sous réserve que le même numéro n'apparaisse pas de (très) nombreuses fois à des dates différentes ou bien que la plage de recherche de date soit suffisamment étroite pour éviter les allers-retours nombreux qui conduiraient éventuellement à un table-scan ou index-scan (selon que l'index est couvrant ou pas).

**François DORIN** · 23/11/2016, 17h32

Tout à fait. Mais sans données, difficile de donner la solution ! Et comme cette approche n'avait pas encore été proposée, je l'ai fait

**SQLpro** · 23/11/2016, 17h54

Placez un index clustered sur la colonne DATE avant de tenter un partitionnement !

A +

**escartefigue** · 23/11/2016, 19h15

Envoyé par dorinf

Tout à fait. Mais sans données, difficile de donner la solution ! Et comme cette approche n'avait pas encore été proposée, je l'ai fait

Oui oui, j'y souscrit amplement

**aserf** · 24/11/2016, 15h04

Bonjour,

Est ce qu'il ne vous serai pas possible de créer une Vue qui porte le nom de la table et d’éclater la table dans plusieurs table ? (pour que ce soit plus propre)
cette vue retournerai les mêmes colonne dans le même ordre. ensuite il ne resterai plus qu'a modifier l'import des données, mais je pense qu'il ne doit pas y avoir beaucoup d'import différent dans le logiciel.

En lecture le logiciel ne verrai pas la différence et vous modifier juste l'import des données.

Cordialement,

**aserf** · 24/11/2016, 15h41

Bonjour,

Est ce que vous avez déjà essayer de convertir vos varchar(20) en guid par exemple ?
Vous prenez votre varchar, vous faire un hash dessus, et ensuite vous le convertisé en guid. ensuite vous créé une colonne idclientguid et vous le mettre dedans et vous l'indexer.
puis lors de la recherche quand il saisie un idclient vous le hasher, puis convertissé en guid. et vous faite une recherche sur ce guid.

Votre recherche ce fera plus sur une chaîne avec des caractères spéciaux, mais sur un numeric 128bit =>16octet.
de plus si vous avez par exemple une recherche conjointe sur 2 champs vous pouvez hasher les 2 ene même temps, et la recherche n'en sera que plus rapide.

j'ai fait cela sur des très grosse table avec des chaîne et la performance est nettement meilleur.

Bonne chance.

**François DORIN** · 24/11/2016, 16h15

Envoyé par aserf

Est ce qu'il ne vous serai pas possible de créer une Vue qui porte le nom de la table et d’éclater la table dans plusieurs table ? (pour que ce soit plus propre)
cette vue retournerai les mêmes colonne dans le même ordre. ensuite il ne resterai plus qu'a modifier l'import des données, mais je pense qu'il ne doit pas y avoir beaucoup d'import différent dans le logiciel.

Autant utiliser les outils adaptés, et partitionner la table, plutôt que de le faire à la mano avec des tables et des vues.

Envoyé par aserf

Est ce que vous avez déjà essayer de convertir vos varchar(20) en guid par exemple ?
Vous prenez votre varchar, vous faire un hash dessus, et ensuite vous le convertisé en guid. ensuite vous créé une colonne idclientguid et vous le mettre dedans et vous l'indexer.
puis lors de la recherche quand il saisie un idclient vous le hasher, puis convertissé en guid. et vous faite une recherche sur ce guid.

Le hash peut être une bonne idée (attention aux collisions !!), mais nécessite une modification du schéma de la BD. Par contre, pourquoi le convertir en GUID ??? Etape inutile.

Et si on modifie le schéma, dans ce cas, il serait peut être mieux de créer une table contenant la liste des numéros clients (voire numéro bis aussi), et de pouvoir ainsi créer une clé primaire auto incrémenté (rien que sur un INT, ça laisse 4 milliards de possibilités !) et de faire le lien avec la table existante via une jointure.

**aserf** · 24/11/2016, 16h29

Pour ma culture personnel il existe un type de données hash en SqlServer ?

Envoyé par dorinf

Autant utiliser les outils adaptés, et partitionner la table, plutôt que de le faire à la mano avec des tables et des vues.

Le hash peut être une bonne idée (attention aux collisions !!), mais nécessite une modification du schéma de la BD. Par contre, pourquoi le convertir en GUID ??? Etape inutile.

Et si on modifie le schéma, dans ce cas, il serait peut être mieux de créer une table contenant la liste des numéros clients (voire numéro bis aussi), et de pouvoir ainsi créer une clé primaire auto incrémenté (rien que sur un INT, ça laisse 4 milliards de possibilités !) et de faire le lien avec la table existante via une jointure.

**François DORIN** · 24/11/2016, 16h46

Envoyé par aserf

Pour ma culture personnel il existe un type de données hash en SqlServer ?

Non, car cela ne veut rien dire. Un hash peut très bien être une chaîne de caractères, un entier, des octets, etc... Par exemple, CHECKSUM et HASHBYTES sont deux fonctions utilisées pour du hashage. La première renvoi un entier, la seconde un VARBINARY.

**aserf** · 24/11/2016, 16h50

Ok, mais alors il vaut mieux stocker un hash une fois et l'utiliser pour la comparaison, plutôt que d'utilisé la fonction hash sur le champs a chaque requête.
la performance entre les 2 n'ont rien à voir. Le guid(uniqueidentifier) est alors le champs natif le plus proche d'un hash md5, (128bit pour les 2). aucune perte, ni aucun octet en plus.