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

[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 +