[2008] Optimisation par Index filtré sur existant

[Sergejack]

Bonjour.

Hypothèse :
Avec la possibilité de filtré les index, il devient facile d'optimiser les insertions.
En créant une colonne "INDEXED" de type BIT et en filtrant les index sur "INDEXED = 1", on peut ainsi faire des insert oµ simplement en mettant INDEXED à 0, on évite le recalcule de l'index.

Imaginez le pseudo code suivant :

Code :

1
2
3
4
5
FAIRE 1000x
INSERT TBL (DATA, Indexed) VALUE('Test', 0)
FIN_FAIRE 1000x
 
UPDATE TBL SET INDEXED = 1 WHERE INDEXED = 0

Cela irait plus vite (en fonction du nombre d'indexes existants) que si les index n'étaient pas filtrées et recalculée à chaque INSERT.


Maintenant, ma question :

Est-ce que je me trompe ?

[SQLpro]

Pas forcément.... En effet avec Arian Papillon dans le cadre des journées SQL Days Chez MS les 11 et 12 décembre, nous allons démontrer l'inverse !

A +

[Sergejack]

Est-ce que vous pourriez déjà m'en dire d'avantage, svp ?

[SQLpro]

Et dévoilez tout nos petits secrets maintenant... !

Pas question. Mais relancez moi après la conférence si vous y avez pas assisté !

En tout cas, laissez faire les index et mettez les toujours à bon escient. Prévoyez toujours un fill factor d'au moins 90% voire 80...

Enfin, lisez ceci : http://sqlpro.developpez.com/cours/s...ivation-index/


A +

[Kropernic]

Un lien pour des infos sur la conférence ?

Google ne semble pas être mon ami sur ce coup-là... (je trouve des résultat pour novembre )

Ah, j'ai p-e trouvé par ici.

Mais c'est en fait le 10 et le 11 alors (pour ça que je ne trouvais pas *siffle*)

[Sergejack]

Jusqu'à présent, tous les tests que j'ai faits me donnent des résultats qui traduisent bel et bien un gain de performance dans les insertions.

[Rei Ichido]

Un gain de performance dans les insertions, peut-être.
Mais ensuite il faut :
1) Considérer l'update.
2) Modifier toutes les requêtes pour rajouter la condition "indexed = 1", sans quoi l'index ne sera jamais utilisé ...

Ce qui revient à peu près (mais en plus compliqué, et porteur à mon sens de problèmes si on se retrouve avec des lignes qui resteraient à indexed = 0) à avoir une table temporaire dans laquelle on fait toutes les insertions avant de faire l'insert final en une fois.

[SQLpro]

Les développeurs oublient toujours qu'il ne sont pas seuls.... Il ne pense pas à ce qui se passe au niveau de la concurrence !

A +

[Sergejack]

Envoyé par SQLpro

Les développeurs oublient toujours qu'il ne sont pas seuls.... Il ne pense pas à ce qui se passe au niveau de la concurrence !

A +

Oui, oui, les praticiens ont énormément de lacunes que n'ont évidemment pas les théoriciens.
Cependant, de quelle concurrence parlez-vous ?

[Sergejack]

Envoyé par Rei Ichido

Un gain de performance dans les insertions, peut-être.
Mais ensuite il faut :
1) Considérer l'update.
2) Modifier toutes les requêtes pour rajouter la condition "indexed = 1", sans quoi l'index ne sera jamais utilisé ...

Ce qui revient à peu près (mais en plus compliqué, et porteur à mon sens de problèmes si on se retrouve avec des lignes qui resteraient à indexed = 0) à avoir une table temporaire dans laquelle on fait toutes les insertions avant de faire l'insert final en une fois.

Vous avez vu juste.
Ce que vous dîtes est en fait la raison qui me font allez à tâtons sur ce terrain.

Néanmoins, faire l'update d'une colonne prends moins de temps de la recopie (et suppression) de l'ensemble des données.
Donc l'insertion reste plus rapide.

Donc, c'est en effet, surtout la modification d'un schéma existant et le bouleversement total que cela implique sur les liaison et utilisation d'indexes qui me freinent.


Enfin, je vais quand même rester attentif à ce qu'SQL Pro aura dit sur le sujet après ses conférences. En espérant que ces dernières ne se basent pas trop sur des cas biaisés et ne travestissent pas tous les contre arguments (cette fois).

[SQLpro]

Envoyé par Sergejack

...de quelle concurrence parlez-vous ?

Des miliers d'utilisateurs qui en parallèle à votre requête veulent eux aussi avoir accès à la même table pour d'autres raisons... lectures, écritures.

En fait vous regardez votre arbre, alors que c'est la forêt qu'il faut contempler dans son ensemble !

A +

[Sergejack]

Envoyé par SQLpro

Des miliers d'utilisateurs qui en parallèle à votre requête veulent eux aussi avoir accès à la même table pour d'autres raisons... lectures, écritures.

En fait vous regardez votre arbre, alors que c'est la forêt qu'il faut contempler dans son ensemble !

A +

Vous avez super bien cerné ma méthode !
Pour tout vous dire, ce n'est même pas l'arbre que je regard, mais l'ombre de l’écureuil.



C'est sarcastique évidemment.
Mais pour reprendre votre métaphore, sachez qu'il m'arrive de réfléchir à la nature de l'arbre pour planifier mes forêts.

[Kropernic]

Au risque de me faire taper sur les doigts, je pense qu'il faut aussi prendre en compte le contexte pour lequel on développe.

Développez quand on s'attend plusieurs milliers d'accès concurrents, ce n'est pas pareil que si on s'attend à quelques uns non ?

J'imagine que certaines pratiques sont avantageuses dans un cas et pas dans l'autre.

[Sergejack]

SQL Pro, pouvez-vous maintenant divulguer vos secrets de couple ?

[Sergejack]

Envoyé par SQLpro

Et dévoilez tout nos petits secrets maintenant... !

Pas question. Mais relancez moi après la conférence si vous y avez pas assisté !

En tout cas, laissez faire les index et mettez les toujours à bon escient. Prévoyez toujours un fill factor d'au moins 90% voire 80...

Enfin, lisez ceci : http://sqlpro.developpez.com/cours/s...ivation-index/


A +

Et donc... qu'avez-vous finalement à nous apprendre sur le sujet ?

[Kropernic]

Trop déçu de na pas avoir pu y aller

[Sergejack]

Bon, on a eu droit à une joli coup de pub, un bel effet d'annonce.
Maintenant, pour apporter un véritable début de réponse :

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
27
28
29
30
31
32
33
34
35
36
 
CREATE TABLE tTstFIdx (
	ID INT NOT NULL IDENTITY
	, DATA DATETIME NULL
	, XDATA NVARCHAR(50) NULL
)
GO
 
CREATE NONCLUSTERED INDEX IDX_tTstFIdx_DATE ON tTstFIdx(DATA) WHERE DATA <> '2010-01-01'
GO
 
SET NOCOUNT ON
SET STATISTICS IO ON
 
INSERT tTstFIdx (DATA, XDATA)
SELECT
	DATEADD(dd, n, '2010-01-01')
	, X.v
FROM tTally AS N
CROSS JOIN (
	VALUES('A'), ('B')
) AS X(v)
WHERE N.n BETWEEN 1 AND 1000
 
 
INSERT tTstFIdx (DATA, XDATA)
SELECT
	DATEADD(dd, 0, '2010-01-01')
	, X.v
FROM tTally AS N
CROSS JOIN (
	VALUES('A'), ('B')
) AS X(v)
WHERE N.n BETWEEN 1 AND 1000
 
SET STATISTICS IO OFF

Table 'tTstFIdx'. Scan count 0, logical reads 6303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tTally'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tTstFIdx'. Scan count 0, logical reads 2006, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tTally'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Après plusieurs essaie du même acabits, le résultat attendu reste toujours le résultat obtenu :

Si vous faites une insertion dans la table avec des valeurs excluent (filtrées) de certains indexes, ces derniers ne seront pas mis à jour (c'est logique).
Cela fait donc un beau gain de performance SURTOUT si vous faites plein de petite insertions plutôt que quelques grosses.

Cette approche est donc idéale quand vous travaillez avec une couche programmatique qui ne vous permet/facilite pas le regroupement d'opérations d'insertion.

[Rei Ichido]

Oui mais non.

Pour que votre index soit utilisé, il faut maintenant que la colonne indexed soit mise à jour à 1 ; vous allez donc certainement modifier un certain nombre de requêtes (mais peut-être pas toutes) pour inclure ce filtre. Rien que là, on voit que votre application risque de gros soucis :
- parce que certaines requêtes n'auront pas ce filtre supplémentaire, et donc n'utiliseront pas l'index
- parce que certaines requêtes auront ce filtre, et du coup ne ramèneront pas de lignes là où elles le devraient (index "pas à jour"), générant des bugs parce que considérant qu'il n'y a pas d'objet associé. Par exemple, vous avez une Table "Fil de discussion" et une Table "Posts". Le fil de discussion est créé, et indexé. Un post est créé, mais pas indexé. Ah zut, votre requête qui fait le lien ne vous ramène plus le post ... fort risque de plantage quand vous allez vouloir supprimer le fil de discussion (considéré comme vide) ou faire une insertion d'un "premier post".

À la limite le vrai gain de performance serait de supprimer l'index ! Parce que là, de toute façon votre façon de procéder n'a de sens que si vous gérez de temps en temps votre index (avec la mise à jour de indexed vers 1).
Vous pourriez tout autant et de façon nettement plus claire, ne pas rajouter cette colonne, désactiver l'index dans le cas général, et le reconstruire avant vos gros traitements.

[SQLpro]

En fait le problème est simple : sans index adéquat, une insertion nécessite un verrou préalable de table (scan complet) pour pouvoir placer la ligne au bon endroit... Donc la table est bloquée intégralement un certain temps, ce qui contribue à diminuer drastiquement la concurrence !
Avec un index adéquat, il n'y a pas de verrou de table posé, mais directement un verrou de ligne, voir de plage de clef....

Le problème est que les développeurs oublient toujours deux éléments fondamentaux des SGBDR dans leur mode de pensée :
1) la concurrence (ils se croient seul au monde à lancer le même bout de code...)
2) les aspects ensemblistes des requêtes (parallélisme et pas d'ordre natif...)

A +

[Sergejack]

Envoyé par SQLpro

En fait le problème est simple : sans index adéquat, une insertion nécessite un verrou préalable de table (scan complet) pour pouvoir placer la ligne au bon endroit...

Vous êtes sérieux, là... ?
Qui a dit qu'il n'y avait pas de PK (non filtrée XD) ?

Bah... dans le genre foireux, prendre une table sans PK pour faire la critique des indexes filtrés, c'est encore du triple A.