Discussion :

[bbo1991]

Hello,

Voilà je cherche les meilleurs moyens pour optimiser des requêtes sur une table avec 3 millions de lignes.

Pour l'instant cette table est équipée d'un clé primaire composite sur les colonnes:
- Numéro Département (nvarchar(5))
- Numéro Client (nvarchar(10))

Les requêtes que je fait sont tous filtrés par une colonne DATE_ACHAT de type date, et ça rame beaucoup pour ramener les données sur une année.
Ex: je ramène tous les achats sur l'année 2009, soit à peu près 350 000 lignes et il me met près de 5 minutes pour le ressortir.

J'ai essayé de créer un index : Numéro Départ + Numéro Client + Date_Achat mais le plan d'exécution montre que ma requête n'utilise jamais cet index, SQL Server préfère utiliser la clé primaire à chaque fois.
Le seul ca où cet index est utilisé a été un test où je ne remonte que Numéro Départ + Numéro Client + Date_Achat .

Bref je trouve dommage qu'on ne puisse tirer profit des filtres que je mets sur la date d'achat dans le plan d'exécution.

A votre avis quels sont les meilleurs moyens à mettre en oeuvre pour optimiser ce type de requête ? Un partitionnement par année, ça existe sous SQL Server comme sur Oracle ça se fait comment ?

[bbo1991]

J'ai oublié je suis encore sous SQL Server 2005 sur un serveur 2003 32 bits.

[Jinroh77]

Il est normal que votre requete n'utilise pas l'index dans ce cas.
Les colonnes composants votre index ont un ordre précis dans celui-ci.

Si la date est le 3° de l'index et que les 2 premier ne font pas partis des filtres de votre requête, alors il ne peut être utilisé.

Si la date est systématiquement présente dans vos requête alors, placez celle-ci en première colonne de l'index.

De plus, il faut voir votre requête pour vérifier que vous n'utilisez pas de fonction sur la date qui rendrait son utilisation non sargable (utilisable par un index).

[bbo1991]

je vais tester tout ça et répondrait à vos questions après. Je suis tout de même à la ramasse avec ces histoires d'index

[elsuket]

Bonjour,

Il y a déjà un chose qui ne va pas sur votre clé primaire : elle utilise deux colonnes de type chaîne de caractère, unicode, en plus.
Le nom de vos colonnes indique que vous stockez des numéros.
Dans ce cas, pourquoi n'avez vous pas utilisé une colonne de type entier ?

Si en effet vous aviez choisi le type INT, vous auriez un index dont la clé aurait une largeur de 8 octets, puisque un INT est stocké sur 4 octets.

Dans votre cas, comme en unicode tout caractère est stocké sur deux octets (avez vous besoin de stocker en unicode ? c'est-à dire : stockez vous dans cette colonne d'autres caractères que ceux de l'alphabet latin ?), en supposant que les valeurs de vos colonnes n'ont pas la longueur maximale autorisée par celle-ci, (j'ai pris 3 caractères pour le département, et 5 pour le client), on est donc à 16 octets.

Ce qui veut dire que pour tout index de la table qui n'est pas celui de votre clé primaire, qu'on appelle non-cluster, ceux-ci référençant la clé primaire de la table, vous stockez deux fois plus d'information que ce qui est en fait nécessaire.
Il en résulte donc que votre index occupe plus de place que nécessaire, entraînant donc des lectures inutiles.

Ensuite, comme les chaînes de caractères n'ont pas la propriété, comme c'est possible de le faire avec des entiers ou des dates, de croître de façon constante et contiguë, la fragmentation de cet index, qui doit être choisi avec la plus grande précaution, doit être bien élevée : voilà encore des lectures inutiles.

Enfin, la comparaison de chaînes de caractères que vous provoquez par jointure sur l'une de ces deux colonnes coûte cher en mémoire et en temps processeur (surtout le second ).

Tout ça pour dire que c'est un mauvais choix

Enfin pour que SQL Server choisisse d'utiliser un index non-cluster sur une table, il faut que la requête retourne environ au plus 30% de la table.
Il détermine cela en fonction de la distributivité des valeurs de la colonne la plus à gauche de votre index ...

@++

[zinzineti]

Enfin pour que SQL Server choisisse d'utiliser un index non-cluster sur une table, il faut que la requête retourne environ au plus 30% de la table.
Il détermine cela en fonction de la distributivité des valeurs de la colonne la plus à gauche de votre index ...

@++

Bonjour elsuket,

C'est exactement l'une de mes préoccupations actuelles :
relation entre utilisation d'Index non-cluster et % de nombre de lignes renvoyées (par rapport au nombre total de lignes de la table) par la requête.
Lors de mes recherches je suis tombé sur cette page :

http://www.sqlskills.com/BLOGS/KIMBE...y-Answers.aspx

La limite de 30% que vous évoquez, est-ce 30 % du nombre total des pages de la table ? ou bien 30 % du nombre total des lignes de la table ?

Vous parler aussi de "distributivité des valeurs de la colonne"
comment peut-on évaluer la distribution des données dans une table ?
Existe-il des instruments de mesure de cette distribution ? j'ai entendu parlé de distribution gaussienne, concrètement comment ça se passe dans la pratique ?

Merci de m'éclairer

[mikedavem]

Bonjour,

Il est possible aussi d'envisager de poser un index cluster sur votre colonne DATE_ACHAT et laisser vos colonnes composant votre clé primaire en tant qu'index unique non cluster mais il faudrait mesurer l'impact d'un tel changement :

Qu'est ce que cela donnerait en terme de mise à jour (INSERTION et UPDATE) ? Si celles-ci ne sont pas monotones ou fréquemment mises à jour, l'index cluster devra être d'autant plus mis à jour et réorganisé. Il se peut aussi que les autres requêtes qui interrogeront votre table seront impactés ... Cela reste à étudier.

Vous pouvez également créer un index couvrant qui permettra de remonter les données demandées par vos requêtes (Avec 2005 vous avez la possibilité de créer des index avec colonnes incluses).. Mais là il faudrait avoir la description de votre ou vos requêtes.

@Zinzineti :

La limite de 30% que vous évoquez, est-ce 30 % du nombre total des pages de la table ? ou bien 30 % du nombre total des lignes de la table ?

Encore une fois cette limite n'est qu'un indicateur et dépend plusieurs facteurs. On doit prendre le nombre de pages avec le nombre de lignes qu'elles contiennent. On doit prendre également les opérateurs que l'optimiseur utilisera pour ramener les données .. la définition des index (Un index non couvrant aura beaucoup moins de chances d'être utilisés qu'un index couvrant et cluster), la fragmentation des index (La fragmentation affecte beaucoup les opérations de recherche mais pas les scans d'index), les statistiques sous jacents (Sont ils à jour à ce moment là ?) etc ......

Dans l'article de Kimberly, celle-ci met en évidence certains aspects de l'utilisation des index en fonction de leur définition .. a mon sens, elle ne cherche pas à définir un seuil d'utilisation des index ... Cependant il existe bien une règle générale qui stipule qu'un index sera utilisé ou non comme les différentes littératures ou BOL le stipulent et qui est valable dans la plupart des cas.

comment peut-on évaluer la distribution des données dans une table ?

Avec DBCC SHOW_STATISTICS tu auras toutes les informations nécessaires à ce que tu recherches.

j'ai entendu parlé de distribution gaussienne, concrètement comment ça se passe dans la pratique ?

La répartion normale ou gaussiène permet de connaître la densité de probabilité d'une variable aléatoire entre 2 écarts type. On peut rapprocher cette définition des statistiques de SQL Server. En effet, il est possible de connaître la fréquence d'une occurence pour chaque valeur distincte dans un ensemble de données. Un ensemble de données est délimité par une frontière (donc 2 valeurs limites de colonne). En fonction de cela, l'optimiseur choisira d'utiliser un index candidat ou non en fonction du prédicat de requête.

++

[bbo1991]

Il y a déjà un chose qui ne va pas sur votre clé primaire : elle utilise deux colonnes de type chaîne de caractère, unicode, en plus.
Le nom de vos colonnes indique que vous stockez des numéros.

Tout à fait d'accord avec toi, sauf que les noms que j'ai donnés "Numéro département" et "Numéro client" étaient de simples exemples.
Je ne manipule pas vraiment des numéros mais des identifiants qui contiennent des données ne pouvant être stockés dans un integer du type : "CX00015", "WY20514",etc...

En fait j'ai créé un exemple simple de departement + client + achat, mais ce n'est pas le métier que je gère. Expliquer ce dernier serait une perte de temps, ce qui nous intéresse étant la technique.

En bref, je ne pourrai pas vraiment me passer de l'unicode pour ces identifiants et c'est bien dommage...

Il est normal que votre requete n'utilise pas l'index dans ce cas.
Les colonnes composants votre index ont un ordre précis dans celui-ci.

Si la date est le 3° de l'index et que les 2 premier ne font pas partis des filtres de votre requête, alors il ne peut être utilisé.

J'ai fait le test suivant avec les index :
- la clé primaire numDepartement + numeroAchat = clé non-cluster
- j'ai créé un index DateAchat +numDepartement = clé cluster

Mes requêtes étant filtrées principalement par departement et date.
Résultat : mon plan d'exécution me montre que l'index cluster est bien utilisé et j'ai un gain de 17% sur ma requête.
Je remonte donc 330 000 lignes sur 3 millions en 3min8s à peu près.

Questions:
- d'après votre expérience sur SQL Server ce résultat est-il naturel?
Puis-je espérer des performances encore meilleures ou est-ce vraiment normal de passer 3 minutes pour ramener un tel volume de données?

- en dehors des indexes, sur quelles autres fonctionnalités SQL Server puis-je m'appuyer pour optimiser mes requêtes? Le partitionnement par année ça existe?

[mikedavem]

La clé cluster DateAchat +numDepartement ne vaut que si vous avez un prédicat du style

Code :

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

WHERE dateAchat BETWEEN ... AND numDeparteéent = ....

... mais je pense que vous l'avez compris.

Questions:
- d'après votre expérience sur SQL Server ce résultat est-il naturel?
Puis-je espérer des performances encore meilleures ou est-ce vraiment normal de passer 3 minutes pour ramener un tel volume de données?

Il faudrait en savoir plus pour vous dire si cela est normal :
- Configuration de votre serveur (processeurs, RAM ...)
- La DDL de votre ou vos tables concernées
- La requête qui vous remonte les données

Concernant le partitionnement oui cela existe sur SQL Server mais cela doit être la solution de dernier recours selon moi.

++

[SQLpro]

Hello,

Voilà je cherche les meilleurs moyens pour optimiser des requêtes sur une table avec 3 millions de lignes.

Pour l'instant cette table est équipée d'un clé primaire composite sur les colonnes:
- Numéro Département (nvarchar(5))
- Numéro Client (nvarchar(10))

Vous stockez des numéros sous forme de chaine de caractères ? Quelle drôle d'idée ? Et vous voulez en plus des performances ? On rève !
De plus mettre du NVARCHAR (N = NATIONAL) c'est à dire de l'unicode qui double le nombre d'octets pour tous les caractères latin c'est stupide?. En sus vous prenez du VAR qui fragmente d'office c'est particulièrement idiot...

Commencez par définir un type de données approprié. Si c'est du pur numérique alors du INT, du BIGINT.
Si c'est mi numérique, mi alpha, mais sans accents, alors du CHAR, mais jamais du NCHAR ou pire du VARCHAR !

Les requêtes que je fait sont tous filtrés par une colonne DATE_ACHAT de type date, et ça rame beaucoup pour ramener les données sur une année.
Ex: je ramène tous les achats sur l'année 2009, soit à peu près 350 000 lignes et il me met près de 5 minutes pour le ressortir.

Tout dépend si votre filtre est "sargeable" et vos index couvrants, mais ressortir 350 000 lignes avec toutes les colonnes sur une table de 3 millions de lignes, ne conduira jamais à un temps de réponse fulgurant !
Quel est votre traitement final ?

J'ai essayé de créer un index : Numéro Départ + Numéro Client + Date_Achat mais le plan d'exécution montre que ma requête n'utilise jamais cet index,

Plus un index est gros et moins il sert, sauf s'il est couvrant

SQL Server préfère utiliser la clé primaire à chaque fois.
Le seul ca où cet index est utilisé a été un test où je ne remonte que Numéro Départ + Numéro Client + Date_Achat .

Parce qu'il est couvrant !

Bref je trouve dommage qu'on ne puisse tirer profit des filtres que je mets sur la date d'achat dans le plan d'exécution.

A vous d'être sargeable !

A votre avis quels sont les meilleurs moyens à mettre en œuvre pour optimiser ce type de requête ?

Lisez les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/quoi-indexer/
http://sqlpro.developpez.com/optimisation/indexation/
http://sqlpro.developpez.com/optimisation/mediane/

Un partitionnement par année, ça existe sous SQL Server comme sur Oracle ça se fait comment ?

Oui et c'est d'une extrême simplicité :
1) créez une fonction de partition avec CREATE PARTITION FUNCTION
2) Créez un schema de partitionnement avec CREATE PARTITION SCHEME
3) modifiez la table pour que l'index clustered ou la table utilise le partitionnement à l'aide de ALTER TABLE ou ALTER INDEX .... ON maFonctionDePartition(MaColonneDePartitionnemen).

A +