Discussion :

[zinzineti]

Hello !
Permettez-moi de revenir sur ce sujet qui est déjà largement creusé.
Il s'agit des S-ARGs ou Clause WHERE optimisée.
Voici les articles que j'ai lu sur le sujet :
SQLPro

Elsuket

Mes questions :

1.) Existe-t-il un document officiel (de Microsoft par exemple) sur les conditions de SEARCHABLITÉ d'une clause WHERE ?
En d'autre terme est-il possible d'avoir une liste exhaustive des expressions (ou situations) de NON SEARCHABLITÉ ?

2.) Est ce que ces expressions (ou situations) de de SEARCHABLITÉ varient selon les moteurs SQL Server (2000, 2005 et 2008) ?

Merci de m'éclairer sur ce sujet qui ne date pas d'hier

[zinzineti]

Hello
Je reformule ma question initiale concernant l'utilisation des index par le moteur SQL Server.
Je viens de publier un papier qui met en évidence comment on peut contraindre le moteur SQL Server (au travers d'une vue indexée) d'utiliser les index même si la requête à exécuter est Non SARGeable VOIR DEMO
D'où ma question :
Dans quels autres cas, le moteur SQL utilise bien les index quand bien même la requête écrite ne répond pas aux critères de SARGeabilté ?

Merci de m'éclairer

[dbaffaleuf]

Tu peux donner un exemple ?

David B.

[zinzineti]

Tu peux donner un exemple ?
David B.

Hello David

J'ai fait une petite démo sur mon blog ICI

[dbaffaleuf]

Je l'ai lue mais je ne vois pas où tu veux en venir. Dans la dernière étape l'optimiseur choisit de passer par la vue indexée parce que les données sont déjà matérialisées, et parce que le coût d'accès par la VI est plus faible que de refaire la jointure entre SalesOrderHeader et SalesOrderDetail. Donc rien d'anormal.

David B.

[hmira]

1.) Existe-t-il un document officiel (de Microsoft par exemple) sur les conditions de SEARCHABLITÉ d'une clause WHERE ?
En d'autre terme est-il possible d'avoir une liste exhaustive des expressions (ou situations) de NON SEARCHABLITÉ ?

Le seul document, que j’ai trouvé, émanant de MicroSoft et qui traite du sujet SARG et Non-SARG est le suivant :
http://msdn.microsoft.com/en-us/library/ms172984.aspx

A+

[zinzineti]

Je l'ai lue mais je ne vois pas où tu veux en venir. Dans la dernière étape l'optimiseur choisit de passer par la vue indexée parce que les données sont déjà matérialisées, et parce que le coût d'accès par la VI est plus faible que de refaire la jointure entre SalesOrderHeader et SalesOrderDetail. Donc rien d'anormal.
David B.

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é.

Le premier réflexe quand on voit une requête du genre

Code :

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

1
2
3
4
 
SELECT ....
FROM ...
WHERE colonne  LIKE '%00'

est tenté de dire (les mains dans les poches bien sûr )

Requête Non SARGeable => le moteur n'utilisera pas les index => Problème de performances ....

Par ailleurs quand on évoque les règles de SARGeabilité, qu'en est-il de la clause HAVING par exemple ? existe-il des exceptions pour les JOIN ?

C'est pour trouver des réponses à toutes ces questions que j'ai demandé s'il existe un document complet qui traitement de façon plus fine le sujet.

J'avoue que j'ai passé des heures à fouiller dans les Books Online, mais rien

Merci de m'éclairer

A+

[zinzineti]

Le seul document, que j’ai trouvé, émanant de MicroSoft et qui traite du sujet SARG et Non-SARG est le suivant :
http://msdn.microsoft.com/en-us/library/ms172984.aspx
A+

Merci pour le lien. J'avais déjà vu ce document.

Tiens par exemple là ils écrivent comme titre du document

Query Performance Tuning (SQL Server Compact)

Est ce que tout ce qui est écrit dans le document concerne UNIQUEMENT SQL Server Compact ???!!!

Si oui et SQL 2K5 ? et SQL 2K8 ? j'allais oublier SQL 2K, mais bon ...


A +

[kagemaru]

Salut

Si tu réfères à ce doc :
http://technet.microsoft.com/en-us/l.../cc917715.aspx, il est indiqué qu'en version Dev ou Ent de SQL Server, il y a l'automatic query-to-indexed-view matching qui permet à l'optimiseur d'utiliser l'index de la vue dans une requête utilisant ses tables sous-jacentes.

Si tu es en version Ent ou Dev et que tu as créés un index sur la vue

Code :

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

1
2
3
4
5
6
 
CREATE UNIQUE CLUSTERED INDEX IDX_V1  
 
  ON Sales.vOrders (OrderDate, ProductID); 
 
GO

et que tu as lancé la requête sur les tables sous-jacentes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
 
OrderDate, 
ProductID 
 
FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o  
ON od.SalesOrderID=o.SalesOrderID 
 
WHERE ProductID Like '%00' 
AND OrderDate = CONVERT(datetime,'05/01/2002',101) 
 
GROUP BY OrderDate, ProductID

L'optimiseur utilise donc l'index de la vue (la colonne OrderDate certainement mais il aurait fallu passer la souris sur l'opérateur afin de voir les propriétés).

Mais ton exemple est bizarre car tu as un index composite sur OrderDate et ProductID mis tu utilises la colonne OrderDate. Forcément l'optimiseur va la choisir. Si tu n'avais utilisé que la colonne ProductID, l'index n'aurait pas été utilisé. Ce n'est pas parce que tu as 1 clause de recherche "non sargable" que cela compromet l'utilisation d'un index si tu utilises une autre clause "sargable" pour ce même index.

De plus, un LIKE avec un wildcard au début peut être utilisé comme SARG, puisque SQL Server maintient des statistiques sur les 40 premiers et derniers caractères de la colonne. Si tu avais eu un index seulement sur ProductID, il y avait quand même une chance que le

Code :

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

ProductID Like '%00'

permette l'utilisation de l'index. Si mes souvenirs sont bons, cela date de SQL 2005.

cf. http://msdn.microsoft.com/en-us/libr...QL.100%29.aspx

Par ailleurs il aurait été utile de poster le DDL des tables et les lignes pour pouvoir rejouer ton cas. Il me semble que les requêtes que tu utilises viennent de ce doc.

[zinzineti]

Merci pour ta réponse et pour les URLs.
Je prépare rapidement un autre scénario en prenant en compte tes remarques.

A+

[zinzineti]

Si tu es en version Ent ou Dev et que tu as créés un index sur la vue

Code :

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

1
2
3
4
5
6
 
CREATE UNIQUE CLUSTERED INDEX IDX_V1  
 
  ON Sales.vOrders (OrderDate, ProductID); 
 
GO

et que tu as lancé la requête sur les tables sous-jacentes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
 
OrderDate, 
ProductID 
 
FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o  
ON od.SalesOrderID=o.SalesOrderID 
 
WHERE ProductID Like '%00' 
AND OrderDate = CONVERT(datetime,'05/01/2002',101) 
 
GROUP BY OrderDate, ProductID

L'optimiseur utilise donc l'index de la vue (la colonne OrderDate certainement mais il aurait fallu passer la souris sur l'opérateur afin de voir les propriétés).

Je viens de le faire et effectivement c'est index de la colonne OrderDate qui est utilisé par le moteur. tu peux le voir sur mon blog

Mais ton exemple est bizarre car tu as un index composite sur OrderDate et ProductID mais tu utilises la colonne OrderDate. Forcément l'optimiseur va la choisir. Si tu n'avais utilisé que la colonne ProductID, l'index n'aurait pas été utilisé. Ce n'est pas parce que tu as 1 clause de recherche "non sargable" que cela compromet l'utilisation d'un index si tu utilises une autre clause "sargable" pour ce même index.

C'est vrai. j'ai fait à nouveau le test avec uniquement la clause LIKE '%00'
Et l'index de la vue n'est pas utilisé. ce qui me rassure maintenant

De plus, un LIKE avec un wildcard au début peut être utilisé comme SARG, puisque SQL Server maintient des statistiques sur les 40 premiers et derniers caractères de la colonne. Si tu avais eu un index seulement sur ProductID, il y avait quand même une chance que le

Code :

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

ProductID Like '%00'

permette l'utilisation de l'index. Si mes souvenirs sont bons, cela date de SQL 2005.

Je n'ai pas pu tester ce cas. As-tu un exemple ?

Par ailleurs il aurait été utile de poster le DDL des tables et les lignes pour pouvoir rejouer ton cas. Il me semble que les requêtes que tu utilises viennent de ce doc.

J'ai utilisé les tables de la base exemple AdventureWorks.

En tout merci pour ta contribution.

[mikedavem]

Bonjour,

De plus, un LIKE avec un wildcard au début peut être utilisé comme SARG, puisque SQL Server maintient des statistiques sur les 40 premiers et derniers caractères de la colonne. Si tu avais eu un index seulement sur ProductID, il y avait quand même une chance que le

Effectivement, à partir SQL Server 2005, les résumés de chaîne incluses dans les statistiques permettent de connaître précisement la cardinalité d'une sous chaîne à l'intérieur d'une colonne de type caractère (valable pour les type de données CHAR, VARCHAR, NVARCHAR, TEXT et NTEXT) et ceci même si cette sous chaîne ne commence pas au début de la chaîne de caractère.

Dans le cas de zinzinetti, aucun index ne pourra être utilisé car il n'existe pas de résumé de chaîne pour la colonne ProductID (de mémoire). Il suffit de vérifier à l'aide de la commande DBCC SHOW_STATISTICS dans l'entête si le paramètre String Index est à YES.

Un bon exemple serait celui-ci (Toujours en prenant comme exemple la base AdventureWorks)

Code :

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

1
2
3
4
5
6
7
SELECT LastName, FirstName, MiddleName, PersonType, Title
FROM Person.Person
WHERE LastName LIKE '%crombie'
 
SELECT LastName, FirstName, MiddleName, PersonType, Title
FROM Person.Person
WHERE LastName LIKE '%c%'

Il suffit de comparer les 2 plans pour voir qu'à l'aide des statistiques de résumé de chaîne pour l'index IX_Person_LastName_FirstName_MiddleName l'optimiseur peut choisir en fonction de la cardinalité de la valeur de sous chaîne du prédicat soit de faire un scan de niveau feuille de l'index + RID lookup sur l'index cluster dans le 1er cas et de faire un scan complet de l'index cluster dans le 2ème cas.

++

[zinzineti]

Un bon exemple serait celui-ci (Toujours en prenant comme exemple la base AdventureWorks)

Code :

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

1
2
3
4
5
6
SELECT LastName, FirstName, MiddleName, PersonType, Title
FROM Person.Person
WHERE LastName LIKE '%crombie'
SELECT LastName, FirstName, MiddleName, PersonType, Title
FROM Person.Person
WHERE LastName LIKE '%c%'

Il suffit de comparer les 2 plans pour voir qu'à l'aide des statistiques de résumé de chaîne pour l'index IX_Person_LastName_FirstName_MiddleName l'optimiseur peut choisir en fonction de la cardinalité de la valeur de sous chaîne du prédicat soit de faire un scan de niveau feuille de l'index + RID lookup sur l'index cluster dans le 1er cas et de faire un scan complet de l'index cluster dans le 2ème cas.
++

Dans les 2 exemples, il y a scan index preuve que l'index est utilisé. Mais qu'est ce que ça change au niveau performance de la requête ? à mon humble avis pas grand chose. scan index ou Table scan c'est à peu de chose près pareil en terme de performance de la requête.
Ce que je retiens c'est que le comportement de l'optimisateur SQL dépend non seulement de la version SQL Server (SQL2K, SQL2K5,SQL2K8) mais aussi de l'édition (Compact, Entreprise,...).
Pour chaque édition SQL Server, est ce qu'il existe une documentation officielle sur les SARGs et Non-SARGs ?

Merci

[mikedavem]

Dans les 2 exemples, il y a scan index preuve que l'index est utilisé. Mais qu'est ce que ça change au niveau performance de la requête ? à mon humble avis pas grand chose. scan index ou Table scan c'est à peu de chose près pareil en terme de performance de la requête.

Oui il y a effectivement un index scan mais qu'au niveau feuille de l'index .. C'est là tout l'avantage des résumés d'index. Sans eux un index scan sur l'ensemble des données de l'index serait effectué. Tu peux voir que le scan ramène très peu de données donc plus de performance en terme d'IO ...

++

[zinzineti]

Un petit point rapide sur le sujet.
Un argument de recherche (SARG) dans le WHERE est sous la forme :
• Colonne opérateur <constante ou variable>
• <constante ou variable> opérateur Colonne

1er cas : opérateur
=====
Les opérateurs SARG comprennent =, >, <, >=, <=, IN, BETWEEN et parfois LIKE (dans les cas des concordances des préfixes, tels que LIKE 'John%').

un index est inutile sur des expressions qui n'utilisent pas des opérateurs SARG.
Les opérateurs non-SARG comprennent NOT, <>, NOT EXISTS, NOT IN, NOT LIKE .

2ème cas : colonne
=====

Est Non-SARG une clause du genre

Colonne1 opérateur Colonne2

Il est parfois impossible pour le moteur de base de données de profiter d'un index si dans la clause WHERE, une fonction est définie sur la colonne.
Néanmoins il existe des fonctions qui appliquée sur la colonne est sans impacts sur l'utilisation des index.

3ème cas : Switch des SARG
=====

Un argument SARG peut contenir plusieurs conditions réunies par un opérateur AND.

• SARG AND SARG est une clause SARG

• SARG OR SARG peut être une clause SARG ou Non-SARG cela dépend de l’index .

• Non-SARG AND Non-SARG est une clause Non-SARG

• SARG AND Non-SARG peut être une clause SARG

• Non-SARG OR Non-SARG peut être une clause Non-SARG


J'ai besoin de votre aide pour identifier établir la liste exhaustive
des fonctions qui appliquée sur la colonne ONT d'IMPACTS sur l'UTILISATION des INDEX

Pour le moment j'en suis à 4 fonctions

ABS
DATEDIFF
MONTH
SUBSTRING
YEAR
......
......
......


Pouvez-vous me dire parmi les fonctions qui sontICI, celles qui ONT d'IMPACTS sur l'utilisation des index ?
La liste complète des fonctions SQL est ici

Merci d'avance

[zinzineti]

Ma liste s'allonge

Fonctions de chaine de caractères
=========================
CAST
CONVERT
LOWER
UPPER
REPLACE
REVERSE
SUBSTRING
.......
.......

Fonctions de calcul
=========================
%
+
-
*
/
EXP
LOG
LOG10
POWER
SQRT
ABS
.........
.........
.........


Fonctions horodatages
=========================
DATEDIFF
MONTH
YEAR
.........
.........
.........

[zinzineti]

Je reviens sur les fonctions qui isolent les colonnes et ne permettent pas à l'optimiseur d'utiliser les index.
je suis arriver à la conclusion suivante : toutes les fonctions SQL appliquées sur la colonne dans la clause WHERE ont d'impact sur l'utilisation des index sauf les fonctions :

• ISNULL
• IS NULL
• IS NOT NULL

Voir des exemples ici

A+

[hmira]

J’ai lu avec beaucoup d’intérêt ton article complet: « Pour une bonne utilisation des indexes dans la clause WHERE » du 18/08/2010
Je l’ai trouvé très intéressant, bien résumé, illustrant au travers des exemples concrets les différentes situations et scénarios etc. . Mais, tu l’as aussi remarqué, il reste encore beaucoup de chose à dire le sujet.

Pour ma part, j’ai examiné le cas "Démo : Conseil N° 4" :

Code :

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

1
2
3
4
SELECT id  
FROM T_TEST 
WHERE id = val
==> Index Scan (IX_ID)

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 défini comme suit :

Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dbo.T_TEST 
( 
  id int identity(1,1), 
  val varchar(10), 
  creation_date datetime, 
  CompareIdVal  as case 
                     when cast(id as varchar(8000)) = val then 0 
                     else 1 
                   end PERSISTED 
);
GO

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

Code :

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

1
2
3
4
5
6
CREATE NONCLUSTERED INDEX IX_CompareIdVal ON dbo.T_TEST
(  
  CompareIdVal ASC
) 
ON [PRIMARY]
GO

Ci-dessous les résultas que j’ai obtenus sur les 1 millions d’enregistrements de la table T_TEST

Code :

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

1
2
3
4
5
6
7
SELECT id  
FROM T_TEST
WHERE id = val 
 
Résultat : 
Index Scan (IX_ID)
CPU : 421 read : 4627 write : 0  Duration : 1016

Code :

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

1
2
3
4
5
6
7
SELECT id  
FROM T_TEST
WHERE CompareIdVal = 0 
 
Résulat : 
Index Seek (IX_CompareIdVal) 
CPU : 0 read : 25 write : 0 Duration : 120

En comparant les 2 requêtes, on voit qu’il n’y a pas photo !
25 pages contre 4 627 ! et 120 millisecondes contre 1 016

A+

[elsuket]

Bonjour,

Les opérateurs SARG comprennent =, >, <, >=, <=, IN, BETWEEN et parfois LIKE (dans les cas des concordances des préfixes, tels que LIKE 'John%').

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 ).
C'est d'ailleurs ce qui fait globalement qu'un prédicat est SARGable.

sauf les fonctions :

* ISNULL
* IS NULL
* IS NOT NULL

Ce n'est pas tout à fait vrai.
Si j'écris WEHRE ISNULL(maColonne, 0), alors ce filtre n'est pas SARGable, toujours pour la même raison : les statistiques sont collectées sur les valeurs qui existent dans la colonne, par sur celles qui pourraient y être.
IS NULL est SARGable puisque l'expression est déterministe : on sait si la colonne a une valeur ou n'en a pas.
IS NOT NULL ne l'est pas puisque, toujours pour la même raison, les statistiques sont collectées sur ce qui est dans la colonne, par sur ce qui n'y est pas.

Néanmoins avec SQL Server 2008, on peut contourner ce type de "problème" à l'aide des index filtrés

[dbaffaleuf]

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.

C'est vrai jusqu'à 64 conditions, au delà ça devient un semi-join.

http://www.sqlskills.com/BLOGS/CONOR...teresting.aspx
http://www.sqlskills.com/BLOGS/CONOR...lan-cache.aspx