Contrainte CHECK et index

Bonjour,

J'avais cru lire qu'sql server utilisaient les contraintes check pour améliorer l'exécution des requêtes, et plus particulièrement dans le cas suivant :

Code:

---

CREATE TABLE peu_importe (Id int primary key identity, Start datetime2(3), End datetime2(3), ....)

---

une contrainte

Code:

---

CHECK End > Start

---

et un seul index non clustered

Code:

---

CREATE INDEX IX_a ON peu_importe (Start)

---

il me semblait avoir lu que quand on faisait un where End > @date alors l'index sur start pouvait être utilisé pour préfiltrer et ne pas avoir toute la table à balayer ...
je n'arrive pas à reproduire (sur 1 millions de lignes, en regardant les plans d'exécutions) ni à retrouver d'infos là dessus

quelqu'un peut me confirmer que ma contrainte est utile pour un where ou si c'est juste pour de l'intégrité ?

Merci.

Les contraintes CHECK pouvant être bien plus complexes que ça, le nombre de données filtrées par cette logique étant parfaitement aléatoire, et la recherche du meilleur index dépendant de tellement d'autres facteurs… que ça me surprendrait qu'un tel comportement puisse être effectivement implémenté et apporte quelque chose de mesurable "dans le cas général".

hier je suis tombé sur quelqu'un qui avait une contrainte check colonne ='A' ou colonne = 'B' et en faisant un select where colonne = 'C' il disait que c'était instantané

que ca apporte quelque chose de mesurable si
si tu as 1 millions de lignes avec un check datefin > datedebut et que tu fais un select where sur datefin > x avec un index sur datedebut, et que seulement 10 lignes ont datebut > x donc pas plus avec datefin > x
ca ne ferait plus que 10 lignes à lire au lieu des 1 millions (+ les quelques données à lire pour arriver à repointer sur les lignes)
donc moi ca me semble mesurable ... et implémentable ...

Le premier cas n'utilise pas d'index : l'optimiseur a juste à valider que le critère de recherche est valide ou non. Ca ne change rien aux index lus : ça va juste permettre d'invalider potentiellement tout ou partie de la requête, et donc ne pas l'exécuter.

Le second cas, quant à lui, comme je l'ai dit :

- On n'a aucune garantie que la contrainte va réellement apporter un critère de filtre pertinent : youpi si ça ramène 10 lignes sur 1 million. Mais l'intérêt sera-t-il le même si ça rapporte 999 990 lignes ?
- Si la contrainte est du type "madate between debut and fin or (type = 1 and madate < debut)", voir même plus complexe, ça me semble compliqué pour l'optimiseur de se baser sur un index ou un autre en fonction de ces éléments… qui n'ont pas de lien direct avec la donnée utilisée dans le filtre


Fort de ça, je reste sur ma position : ça me surprendrait que Microsoft se soit amusé à développer une telle "optimisation" au vue de son degré aléatoire et de sa complexité de mise en œuvre...

Voici un exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

create table test_check_index ( id int identity primary key, type tinyint not null, debut datetime2 not null, fin datetime2 not null, check (type in (1, 2, 3)), check (debut < fin) ); go   create index ix_date on test_check_index(fin); go   declare @i int; declare @date datetime2;   set @i = 0;   while @i < 10000000 begin select @i = @i + 1, @date = DATEADD(day, rand() * -5000, GETDATE()); insert into test_check_index (type, debut, fin) values (cast(RAND() * 3 as tinyint) + 1, @date, DATEADD(day, RAND() * 6000, @date)); end; go

---

Quelques requêtes :

Code:

---

1 2 3 4 5 6

select COUNT(*) from test_check_index where type = 5; -- Devrait tenir compte de la contrainte CHECK "type in (1, 2, 3)" select COUNT(*) from test_check_index where debut > fin; -- Devrait tenir compte de la contrainte CHECK "debut < fin" select COUNT(*) from test_check_index where type = 1; select COUNT(*) from test_check_index where debut < dateadd(day, -5, GETDATE()); -- Pourrait tenir compte de la contrainte CHECK "debut < fin" select COUNT(*) from test_check_index where fin > GETDATE();

---

Les plans associés :
Pièce jointe 391838

Aucune intelligence sur aucune des requêtes : les contraintes n'ont aucun impact.

Cependant, chose très étrange : tant que j'avais peu de lignes dans ma table, moins de 1 000 000 (j'ai lancé les tests pendant l'alimentation de la table) la première tenait compte de la contrainte check !
Mais à aucun moment les autres.

Pourtant, à 10 000 000 de lignes, SQL Server aurait probablement intérêt à activer ce genre d'optimisations si existantes…

J'essaie de reproduire le coup de la contrainte sur la colonne type sans aucun succès depuis tout à l'heure...

J'ai réussi à reproduire le comportement de la vérification de la contrainte check plutôt que la recherche de valeurs :
Pièce jointe 391842

J'ai été obligé de réécrire la requête en changeant le critère, ainsi qu'un redémarrage du service SQL Server !

Pas compris pourquoi au bout d'un moment le moteur décide que c'est mieux de lire les données… Peut-être en raison de la mise en cache de la table entière, qui reste somme toute très petite ? Présence de statistiques (non sollicitées) sur la colonne ?

En tout cas, là où ça devient intéressant, et je l'ai reproduit du premier coup, c'est lorsqu'on fait ce genre de requête :

Code:

---

1 2	select COUNT(*) from test_check_index where fin < dateadd(day, -5, GETDATE()) and type = 4; -- Pourrait tenir compte de la contrainte CHECK "debut < fin"

---

Pour le coup, une recherche sur l'index portant sur "fin" était tentant, mais une vérification de la contrainte était bien plus performant : SQL Server n'est pas tombé dans le panneau.

Cela dépend de deux choses :
1) sp_configure 'optimize for d hoc workload' à 0 ou 1
2) de la présence d'un index sur la colonne contrainte

A +