Searchable Arguments ou clause WHERE optimisée

Version imprimable

Voir 40 message(s) de cette discussion en une page

20/08/2010, 11h23
zinzineti

Citation:

Envoyé par hmira

Pour résoudre le problème, de ce cas précis "Démo : Conseil N° 4", lié au scan sur l’index IX_ID, je propose la solution suivante :

1 - Rajouter dans la table dbo.T_TEST un champ calculé CompareIdVal persistant

2 – Créer un index sur ce nouveau champs calculé

Merci, je n'avais pas vu cette solutiion :ccool:

A+
20/08/2010, 11h45
zinzineti

Citation:

Envoyé par elsuket

Attention, IN n'est pas SARGable dans le cas où l'on se réfère à une liste de valeurs 'en dur'.
Cela revient à écrire maColonne= uneValeur OR maColonne = uneAutreValeur OR maColonne = encoreUneAutreValeur.
Or, les statistiques de colonnes sont collectées uniquement suivant les valeurs singleton que celles-ci contient, pas sur les possibles sous-ensembles arbitraires qui peuvent exister dans celle-ci (imaginez le boulot :D).
C'est d'ailleurs ce qui fait globalement qu'un prédicat est SARGable.

Bonjour elsuket,
Merci pour tes explications :ccool:
En effet j'ai fait le test avec les opérateur "IN" et "OR" et j'ai un résultat contraire.
j'obtiens dans les conditions de mon test que "IN" et "OR" sont SARGs !?
Comment peut-on l'expliquer le résultat observé

Voici les résultats des tests (en bas de page du billet)

Merci de m'éclairer ;)
21/08/2010, 11h53
SQLpro

Citation:

Envoyé par zinzineti

C'est justement ces genres d'exceptions qui m'intéressent.
D'où ma question initiale qui est de savoir s'il existe une liste exhaustive des cas où le moteur SQL Server bypass les règles de Non SARGeabilité.

Un cas classique de non sargeabilité est données par une clause OR sur même colonne ou bien IN que le moteur transforme souvent en UNION ALL, s'il n'y a pas trop de "branches". Enfin cela dépend du moteur (je fais en ce moment de la plongée en asie et n'ai pas les moyens de te montrer la chose, mais tu peut faire des tests).

A +

On ne peut pas, en terme de SARGeabilité, définir une règle générale fixe pour l’opérateur IN. Ce dernier peut aussi bien être SARG que Non-SARG, en fait, tout dépend du résultat de la stratégie (algorithme d’accès aux données) de l’optimiseur dans sa recherche du plan optimal.

Dan l’exemple fourni par zinzineti
Code:

1 2 3 SELECT val FROM T_TEST WHERE val IN ('456','789','980')
L’opérateur IN a été SARG. En effet, on le voit bien dans la partie « Seek Predicate» l’optimiseur a effectué 3 opérations Index Seek (IX_VAL), un Seek pour chaque élément de la liste ('456', '789', et '980')

Dans cet exemple l’optimiseur a jugé (c.à.d. résultat de l’algorithme interne de l’optimiseur dans sa recherche du plan le plus optimal) qu’il était plus intéressant et plus optimal d’effectuer 3 "Index Seek" (IX_VAL) que d’effectuer par exemple 1 seul Index-Scan pour trouver tous les enregistrements répondant au critère " val IN ('456','789','980') "

Dans un autres exemple (ou autre situation), toujours avec l’opérateur IN , l’optimiseur peut très bien décider du contraire c.à.d., juger plus intéressant de faire un "Index scan" au lieu de faire N "Index Seek" (où N représente le nombre d’éléments de la liste IN (…) ) et auquel cas l’opérateur serait considéré Non-SARGAble.

Ci-dessous 2 exemples de requêtes établis sur la table T_TEST (voir script de création et de peuplement de la table T_TEST dans le blog de zinzineti ici)
La table T_TEST contient 1 million d’enregistrements et possède 2 indexes IX_ID et IX_VAL établis respectivement sur les colonnes id et val.

Exemple 1 :
Code:

1 2 3 4 5 6 7 SELECT * FROM T_TEST T1 WHERE T1.val IN -- <--- Index Scan (IX_ID) T1 (SELECT T2.val FROM T_test T2 WHERE T2.val > '100' -- <--- Index Seek (IX_VAL) T2 )
Dans l’exemple 1, ci-dessus, l’optimiseur, a effectué pour l’opérateur IN, un "Index San" sur l’index IX_ID

Exemple 2 :
Code:

1 2 3 4 5 6 7 SELECT * FROM T_TEST T1 WHERE T1.val IN -- <---Index Seek (IX_VAL) T1 (SELECT T2.val FROM T_test T2 WHERE T2.val <= '100' -- <--- Index Seek (IX_VAL) T2 )
Alors que dans l’exemple 2, ci-dessus, l’optimiseur, a efectué pour l’opérateur IN un "Index Seek" sur l’index IX_VAL

A+

23/08/2010, 13h24
elsuket

Il faut donc considérer la distribution des données comme vous le suggérez, plus le nombre de paramètres ... un peu lourd quand même :D

@++ ;)
25/08/2010, 20h48
hmira
Il ne s’agit pas du nombre de paramètres ! Il s’agit du coût et du nombre d’enregistrements estimés répondants au critère de recherche.
Si l’optimiseur estime que les enregistrements répondant au critère IN représentent plus de 15% (certains disent même plus de 10%), l’optimiseur choisira de faire un Index Scan (de préférence sur un index ordonné en cluster) sur la table, plutôt que de faire un Index Seek sur un index non ordonné en cluster.
Si au contraire, l’optimiseur estime que les enregistrements répondant au critère IN représentent moins de 15% (ou moins de 10 %) l’optimiseur choisira de faire un Index Seek

Reprenons notre exemple de la table T_TEST avec ses 1 millions d’enregistrements. Si l’optimiseur, estime par exemple, que la sous requête :
Code:

1 2 3 4 5 IN (SELECT T2.val FROM T_test T2 WHERE T2.val > '100' -- même résonnement pour T2.val <= 100 )
retournerait 200 000 enregistrements (soit 20% du nombre total des enregistrements de la table T_TEST, alors l’optimiseur choisira vraisemblablement de faire un Index Scan.
Si au contraire, l’optimiseur estime que la même sous requête ne retournerait que 50 000 enregistrements (soit 5% de nombre total des enregistrements de la table T_TEST), alors l’optimiseur choisira, dans ce cas, vraisemblablement de faire un Index Seek.

A+

PS :
- Dans le cas d’un Index Scan, le coût est souvent proportionnel au nombre total d’enregistrements de la table.
- Alors que dans le cas d’un Index Seek, le cout est souvent, proportionnel au nombre d’enregistrement répondant aux critères de recherche (critères de sélection définis dans la clause WHERE).
26/08/2010, 10h28
SQLpro

Citation:

Envoyé par hmira

Il ne s’agit pas du nombre de paramètres ! Il s’agit du coût et du nombre d’enregistrements estimés répondants au critère de recherche.
Si l’optimiseur estime que les enregistrements répondant au critère IN représentent plus de 15% (certains disent même plus de 10%), l’optimiseur choisira de faire un Index Scan (de préférence sur un index ordonné en cluster) sur la table, plutôt que de faire un Index Seek sur un index non ordonné en cluster.
Si au contraire, l’optimiseur estime que les enregistrements répondant au critère IN représentent moins de 15% (ou moins de 10 %) l’optimiseur choisira de faire un Index Seek

En fait les limites sont entre 10 et 30 %

A +
27/08/2010, 13h39
zinzineti
Citation:

Envoyé par SQLpro

En fait les limites sont entre 10 et 30 %

A +

Comment peut-on vérifier ces métriques ?

J'ai essayé le scénario suivant mais à aucun moment j'ai un Index Scan ?!

Voici le scénario :

--> 1 . création d'un index clustered sur la colonne ID

Code:

CREATE CLUSTERED INDEX IX_ID ON dbo.T_TEST(id)

--> 2. test sur 10% du nb de lignes de la table
Code:

1 2 3 SELECT T1.id FROM T_TEST T1 WHERE id IN (SELECT T2.id FROM T_TEST T2 WHERE T2.id <= 100000)
--> 3. test sur 15% du nb de lignes de la table
Code:

1 2 3 SELECT T1.id FROM T_TEST T1 WHERE id IN (SELECT T2.id FROM T_TEST T2 WHERE T2.id <= 150000)
-> 4. test sur 30% du nb de lignes de la table
Code:

1 2 3 SELECT T1.id FROM T_TEST T1 WHERE id IN (SELECT T2.id FROM T_TEST T2 WHERE T2.id <= 300000)
-> 5. test sur 50% du nb de lignes de la table
Code:

1 2 3 SELECT T1.id FROM T_TEST T1 WHERE id IN (SELECT T2.id FROM T_TEST T2 WHERE T2.id <= 500000)
-> 6. test sur 100% du nb de lignes de la table
Code:

1 2 3 SELECT T1.id FROM T_TEST T1 WHERE id IN (SELECT T2.id FROM T_TEST T2 WHERE T2.id <= 1000000)
Pour tous ces tests j'ai toujours des Index Seek !

Comment peut-on vérifier les chiffres que vous donner ?

avez-vous des exemples concrets permettant de se faire une idée des métriques (10 % , 30%, ..) ?

Merci de m'éclairer.

A+
27/08/2010, 14h52
SQLpro

Normal, votre jeu est faussé dès le départ par le fait que votre index est couvrant. Pour cela il faut un index non couvrant.

En sus, cet indice est estimé à l'opération, pas globalement pour le plan de requête.

A +
27/08/2010, 15h06
zinzineti

Citation:

Envoyé par SQLpro

Normal, votre jeu est faussé dès le départ par le fait que votre index est couvrant. Pour cela il faut un index non couvrant.
A +

Avec un Index Non couvrant j'ai dans tous les cas des "Index Scan"

Citation:

En sus, cet indice est estimé à l'opération, pas globalement pour le plan de requête.

Est ce que cela veut dire qu'il n'y a aucun moyen de tester les 10%, 30% ?

A+
27/08/2010, 20h01
SQLpro

Il faut un volume de données conséquent (si possible plus que la RAM du serveur) avec différentes colonnes, une dispersion proche de la réalité (gaussienne par exemple), et différentes données.

A +
28/08/2010, 01h55
hmira
Zinzineti,
Ton scénario ne peut fonctionner, ou plutôt il ne peut générer que des index seek et c’est normal (tu l’as bien remarqué) en effet, ta clause (where id in ..) porte sur le champ id, clé primaire de la table T_TEST, la dite clé primaire est ordonnée en cluster.
L’optimiseur n’a aucune raison de faire un Index-Scan sur l’index de la clé primaire alors la clause where porte sur la clé primaire. Il fera forcément un index seek sur l’index de la clé primaire où alors à quoi sert les indexes et leurs jolis B-Arbre équilibrés dont le but principal est d’optimiser l’accès aux données !

Il aurait fallut que la clause where porte par exemple sur le champ val et mettre dans la clause Select des champs qui ne sont pas couvert par l’index IX_val (index établi sur le champ val)
Ainsi, l’optimiseur aura à évaluer et à comparer les couts entre :
* Faire un index seek sur IX_val, mais pour chaque clé aller chercher dans la table les valeurs des champs non couvert par l’index IX_val
* ou faire un index scan sur IX_id (certe plus couteux que l’index seek sur IX_val) mais où, il n’aura pas à aller chercher, pour chaque enregistrement répondant aux critères , les valeurs des champs (champs de la clause Select non couverts par l’index) ailleurs ; puisque l’index ordonnée en cluster est la table elle-même.

J’ai refais des tests pour vérifier au travers des exemples concrets, les métriques ou limites 10%, 30% évoqués précédemment.
Code:

1 2 3 4 5 SELECT * FROM T_TEST T1 WHERE T1.val IN (SELECT TOP (200000) val -- soit 20% des 1 millions d'enregitrements FROM T_TEST T2 )
-- Résultat : Index Scan IX_ID (T1)
-----------------------------------------
-- ... etc.. J’obtiens toujours des Index Scan IX_ID (T1) jusqu'au nombre TOP 1417 compris.

-- .. Au dessous du nombre magique 1416 (compris), j'obtiens des opérations Index Seek (IX_VAL) (T1) :
Code:

1 2 3 4 SELECT * FROM T_TEST T1 WHERE T1.val IN (SELECT TOP (1416) val -- soit environ 0.14% des 1 millions d'enregitrements FROM T_TEST T2)
-- Résultat : Index Seek IX_val (T1)
-----------------------------------
Je pense que cette valeur 0.14% très faible ne contredit pas les limites (10% à 30%) puisque dans le cas de l'opérateur IN (..) intervient le nombre d'opérations Seek effectuées (indiquées dans le détail du plan d'exécution estimé).

Pour le nombre Top 1416 l'optimiseur effectue 1416 opérations Index Seek IX_ID (T1). C’est un peu comme si on lançait la requête "select * from T_TEST T1 WHERE T1.val = @val", 1416 fois !
Alors que dans le cas de l'index scan (IX_id) le nombre d'opération est égale 1

Donc, effectivement, à partir d'un certain seuil (dans notre exemple 1416) les couts (Index Seek, index Scan) s'inversent.

A+
30/08/2010, 10h56
zinzineti

Citation:

Envoyé par SQLpro

Il faut un volume de données conséquent (si possible plus que la RAM du serveur) avec différentes colonnes, une dispersion proche de la réalité (gaussienne par exemple), et différentes données.
A +

Est-il possible de fabriquer ce jeu de donner proche de la réalité ?

Je pense à un générateur de données qui pourrait prendre en paramètre la valeur

de la RAM de la machine sur laquelle on désire faire le test.

A+
30/08/2010, 11h09
zinzineti

Citation:

Envoyé par hmira

-- Résultat : Index Seek IX_val (T1)
-----------------------------------
Je pense que cette valeur 0.14% très faible ne contredit pas les limites (10% à 30%) puisque dans le cas de l'opérateur IN (..) intervient le nombre d'opérations Seek effectuées (indiquées dans le détail du plan d'exécution estimé).
Pour le nombre Top 1416 l'optimiseur effectue 1416 opérations Index Seek IX_ID (T1). C’est un peu comme si on lançait la requête "select * from T_TEST T1 WHERE T1.val = @val", 1416 fois !
Alors que dans le cas de l'index scan (IX_id) le nombre d'opération est égale 1
Donc, effectivement, à partir d'un certain seuil (dans notre exemple 1416) les couts (Index Seek, index Scan) s'inversent.
A+

C'est édifiant toutes ces explications :ccool:

En somme les métriques (10% - 30%) dans le cas de l'opérateur IN est compliqué à tester ?!

A+
01/09/2010, 16h44
zinzineti

Citation:

Envoyé par SQLpro

Il faut un volume de données conséquent (si possible plus que la RAM du serveur) avec différentes colonnes, une dispersion proche de la réalité (gaussienne par exemple), et différentes données.
A +

J'ai téléchargé un générateur de données ICI

et je voudrais savoir comment vérifier si les données générées remplissent la condition suivante :

dispersion proche de la réalité (gaussienne par exemple)

Merci d'avance

Citation:

Envoyé par hmira

Je pense que cette valeur 0.14% très faible ne contredit pas les limites (10% à 30%) puisque dans le cas de l'opérateur IN (..) intervient le nombre d'opérations Seek effectuées (indiquées dans le détail du plan d'exécution estimé).

Toujours pour avoir une idée sur les métriques j'ai repris la même table
Code:

1 2 3 4 5 6 7 8 9 SET NOCOUNT ON IF OBJECT_ID('dbo.T_TEST') IS NOT NULL DROP TABLE dbo.T_TEST CREATE TABLE dbo.T_TEST ( id int identity(1,1), val varchar(10), creation_date datetime )
En la chargeant avec les 1 millions de lignes :
Code:

1 2 3 4 5 6 7 DECLARE @counter int; SET @counter = 1; WHILE @counter <= 1000000 BEGIN INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(left(convert(bigint,RAND()*10000000),6))),getdate()); SET @counter = @counter + 1 END;
Ensuite je place un index cluster sur la colonne val

Code:

CREATE CLUSTERED INDEX IX_VAL ON dbo.T_TEST(val)

La requête de test :

Code:

SELECT * FROM dbo.T_TEST WHERE val = '0'

Pour tester l'effet de l'index sur 5% de lignes renvoyées, je fais :

Code:

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=50000

et je teste la requête :

Code:

SELECT * FROM dbo.T_TEST WHERE val = '0'

Pour tester l'effet de l'index sur 10% de lignes renvoyées, je fais :

Code:

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=100000

et je teste la requête :

Code:

SELECT * FROM dbo.T_TEST WHERE val = '0'

Et ainsi de suite ...

Voici les résultats :

Pour un index cluster ==> INDEX SEEK dans tous les cas

Par contre en reprenant les tests mais cette fois-ci en créant un index NONCLUSTERED sur la même colonne val on a :

Entre 0% - 9% ==> INDEX SEEK

A partir de 10% et plus ==> Table Scan

Et toujours à partir de 10% dans le plan d'exécution affiche le message suivant :
Code:

1 2 3 4 5 6 /*The Query Processor estimates that implementing the following index could improve the query cost by 99.9181%.*/ USE [MaBase] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[T_TEST] ([val]) GO
Pourquoi ce message alors que l'index suggéré existe bien !!!

05/09/2010, 16h21
SQLpro

Citation:

Envoyé par zinzineti

Pourquoi ce message alors que l'index suggéré existe bien !!!

1) non, ce n'est pas le même index ! CLUSTERED versus NONCLUSTERED
2) Il existe bien une TRES grande différence entre un index CLUSTERED qui est la table avec toutes ses colonnes et un index NONCLUSTERED qui est une copie des seules données des clefs (colonnes visées par l'indexation) de l'index.

Pour comprendre ce qu'est un index CLUSTERED et donc son contraire, lisez ce que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p5...-ce-que-c-est/

A +
06/09/2010, 11h04
zinzineti

Citation:

Envoyé par SQLpro

1) non, ce n'est pas le même index ! CLUSTERED versus NONCLUSTERED
2) Il existe bien une TRES grande différence entre un index CLUSTERED qui est la table avec toutes ses colonnes et un index NONCLUSTERED qui est une copie des seules données des clefs (colonnes visées par l'indexation) de l'index.

Oui bien sûr il y a une TRES grande différence entre un index CLUSTERED et un index NONCLUSTERED.

Le test dans le cas d'un index CLUSTERED renvoit toujours un index SEEK quelque soit le niveau de sélectivité.

Par contre pour index NONCLUSTERED, on a:

Entre 0% - 9% ==> INDEX SEEK

A partir de 10% et plus ==> Table Scan

et le message suivant apparaît à partir de 10% :

Citation:

/*The Query Processor estimates that implementing the following index could improve the query cost by 99.9181%.*/
USE [MaBase]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[T_TEST] ([val])
GO

Alors que cet index existe déjà ! d'où mon étonnement !?

Merci de m'éclairer
06/09/2010, 11h30
SQLpro

ENCORE UNE FOIS NON !

Vous créez un index CLUSTERED sur val. Relisez vous :

Code:

CREATE CLUSTERED INDEX IX_VAL ON dbo.T_TEST(val)

Refaite le test en créant cet index de la manière suivante :

Code:

CREATE INDEX IX_VAL ON dbo.T_TEST(val)

A +

Vu le comportement bizarre que je constate, je me dis qu'il y a quelque chose qui m'échappe. J'ai donc supprimer ma table de test pour repartir de zéro. Voici la procédure de test :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 --Création de la table SET NOCOUNT ON IF OBJECT_ID('dbo.T_TEST') IS NOT NULL DROP TABLE dbo.T_TEST CREATE TABLE dbo.T_TEST ( id int identity(1,1), val varchar(10), creation_date datetime ) --Chargement d'1 million de lignes dans la table DECLARE @counter int; SET @counter = 1; WHILE @counter <= 1000000 BEGIN INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(LEFT(convert(bigint,RAND()*10000000),6))),getdate()); SET @counter = @counter + 1 END; --Création d'index sur la colonne val CREATE INDEX IX_VAL ON dbo.T_TEST(val)
La requête de tester :

Code:

SELECT * FROM dbo.T_TEST WHERE val = '0'

-- Niveau de sélectivité : 7% du nombre total de lignes de la table

Code:

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=70000

Cette fois-ci :
de 0% -7% ==> INDEX SEEK

-- Niveau de sélectivité : 8% du nombre total de lignes de la table

Code:

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=80000

de 8% -100% ==> Table Scan

et l'optimiseur me renvoie ce message ci-dessous :

Citation:

/*
Missing Index Details
The Query Processor estimates that implementing the following index could improve the query cost by 99.9152%.
*/
/*
USE [MaBase]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[T_TEST] ([val])
GO
*/

Ai-je raté quelque chose ? Mais l'index NONCLUSTERED IX_VAL existe ?!? :oops:

Ce comportement me paraît un peu bizarre ...

J'ai besoin de votre patience et de votre lumière ;)

Merci d'avance

Voir 40 message(s) de cette discussion en une page