Discussion :

[Batou69]

Bonjour,

J'utilise SQL Server 2005 Ent ed 64 bits pour notre base de données d'archives. Le serveur est en SP3.

Nous avons dans cette base deux tables définies comme ci après :

Code :

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

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
 
USE [TPGD_Arc]
CREATE TABLE [dbo].[CBL](
	[TRIEUR] [tinyint] NOT NULL,
	[FICHIER_N7] [int] NOT NULL,
	[RANG] [int] NOT NULL,
	[LONGUEUR] [smallint] NULL,
	[UTILITE] [smallint] NOT NULL,
	[CODE_POSTAL_POS] [smallint] NULL,
	[CODE_POSTAL_LEN] [smallint] NULL,
	[POIDS_POS] [smallint] NULL,
	[POIDS_LEN] [smallint] NULL,
	[POIDS_COEF] [smallint] NULL,
	[TABLE_TRI] [char](2) NULL,
	[ALGO] [smallint] NULL,
	[LIEN_POS] [smallint] NULL,
	[LIEN_LEN] [smallint] NULL,
	[CODE_CLIENT_POS] [smallint] NULL,
	[CODE_CLIENT_LEN] [smallint] NULL,
	[NUMERO_COLIS_POS] [smallint] NULL,
	[NUMERO_COLIS_LEN] [smallint] NULL,
	[PARTIE_FIXE_POS] [smallint] NULL,
	[PARTIE_FIXE_LEN] [smallint] NULL,
 CONSTRAINT [PK_CBL] PRIMARY KEY CLUSTERED 
(
	[TRIEUR] ASC,
	[FICHIER_N7] ASC,
	[RANG] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Pas d'index supplémentaires sur cette table. La seconde :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE [dbo].[FICHIER_N7](
	[TRIEUR] [tinyint] NOT NULL,
	[FICHIER_N7] [int] NOT NULL,
	[NOM] [varchar](50) NOT NULL,
	[DATE_INTEGRATION] [datetime] NOT NULL,
 CONSTRAINT [PK_FICHIER_N7] PRIMARY KEY CLUSTERED 
(
	[TRIEUR] ASC,
	[FICHIER_N7] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]

Cette table possède un index en plus de la clef :

Code :

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

1
2
3
4
5
6
7
 
CREATE NONCLUSTERED INDEX [IX_FICHIER_N7_DATE_INTEGRATION] ON [dbo].[FICHIER_N7] 
(
	[TRIEUR] ASC,
	[FICHIER_N7] ASC,
	[DATE_INTEGRATION] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]

Mon problème est que les indexs ne sont pas utilisés correctement lorsque j'exécute la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
DECLARE @DUREE AS INT
SET @DUREE = 13
 
SELECT
	CBL.FICHIER_N7
FROM
	dbo.CBL CBL	
JOIN
	dbo.FICHIER_N7 FI7
ON
	CBL.TRIEUR = FI7.TRIEUR
	AND CBL.FICHIER_N7 = FI7.FICHIER_N7
	AND FI7.DATE_INTEGRATION < DATEADD( MONTH, -@DUREE, GETDATE() )

Il me fait un index scan sur les deux tables.
Si j'effectue un select sur une seule table à la fois avec des valeurs fixes, alors il fait un seek. Le pire c'est que si je remplace la variable @duree dans le code ci dessus par un valeur "en dur" alors il fait un index seek sur la table CBL.

Bref je comprend pas bien comment il utilise les indexs avec une date.

J'ai le même problème dans une table de 35M de lignes et un between sur une colonne indexée avec un index couvrant.


Merci pour votre aide !!

[SQLpro]

Tout dépend de la dispersion des données et du volume des données retournées. Sachant qu'il est plus complexe de parcourir un index (seek) que de lire une table (scan) ou un index, si le volume attendu en sortie est supérieur à un certain seuil (entre 10 et 30%) alors le seek ne vaut pas le "coût" et l'optimiseur passe au scan.

Pour vérifier cette hypothese, vous pouvez analyser les statistiques d'exécution de la requête brute (SET STATISTICS IO ON / SET STATISTICS TIME ON) ou en lui forçant l'index :

Code :

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

1
2
JOIN
	dbo.FICHIER_N7 FI7 WITH(INDEX (IX_FICHIER_N7_DATE_INTEGRATION))

Sachez enfin qui est rare que l'optimiseur est tort et que SQL Server se trompe !

Au passage avez vous forcé la paramétrisation des requêtes pour cette base ?

Code :

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

ALTER DATABASE ??? SET PARAMETRIZATION FORCED

A +

[Batou69]

Tout d'abord merci pour ta réponse rapide !

Le fait de lui forcer l'index ou pas ne change rien. Il l'utilise dans les deux cas mais simplement il le scanne complètement.
Mais j'ai bien compris que cela dépend surtout du nombre de lignes impactées. Dans mon cas il n'y a pas de lignes la plupart du temps ( c'est une requête de delete qui purge les lignes antérieures à X mois). Donc il scanne.

Ta réponse explique aussi pourquoi lorsque j'effectue une somme sur 50% des lignes d'une table de 34 millions de lignes, alors j'aurais beau mettre tous les indexes que je veux, il fait systématiquement un scan.
(et donc c'est un peu long mais sans plus : environ 15 secondes )

Edit : Je ne sais pas (encore) ce que signifie la "parametrisation" des requêtes. Je regarde cela tout de suite.

[Batou69]

Ok alors pour la parametrisation, j'ai compris que cela indique au moteur que les requêtes sont toutes paramétrées. Cela lui permet de les mettre dans le cache et donc de ne pas avoir a les recompiler a chaque fois.

Nous n'utilisons JAMAIS de requêtes directes. Uniquement des procédures stockées. Alors dans ce cas, cela me semble utile de forcer ce paramètre car il semble améliorer les performance dans notre cas.

Mais il doit bien y avoir un revers de la médaille. Pourquoi n'est il pas actif par défaut alors ??

PS : cette base est abonnée à un serveur de production qui push les insert/update mais pas les delete selon unmode transactionnel. Je ne sais pas si cela a une importance.

[SQLpro]

Mais il doit bien y avoir un revers de la médaille. Pourquoi n'est il pas actif par défaut alors ??

Parce que cela nécessite un peu plus de travail si aucune des requêtes passées ne sont pré paramétrées....

Ta réponse explique aussi pourquoi lorsque j'effectue une somme sur 50% des lignes d'une table de 34 millions de lignes, alors j'aurais beau mettre tous les indexes que je veux, il fait systématiquement un scan.
(et donc c'est un peu long mais sans plus : environ 15 secondes )

Il n'y a de toutes façons pas d'autre moyen pour calculer une somme une moyenne ou un count. Si c'était un max ou min l'index serait utilisé en SEEK.
Si la plage était plus réduite (en dessous de 19% alors il ferait peut être un seek).

Mais pour un tel cas de figure sur une table de ce volume il y a longtemps que j'aurais mis en place une vue indexée !

A +

[Batou69]

Mais pour un tel cas de figure sur une table de ce volume il y a longtemps que j'aurais mis en place une vue indexée !

Certes cela est plus efficaces dans bien des cas.
Mais la table dont je parle est une table d'historique glissant sur un an. Il y a par jour environ 300 000 inserts (et donc 300 000 deletes pour les lignes agées de plus d'un an)
La requete que je dois optimiser fait un agregat du nombre de lignes sur les six derniers mois selon trois groupement différents.

Si je veux faire une vue, alors il m'en faut non pas une mais trois ce qui pourrait prendre une place conséquente non ? ( je n'ai pas idée de la volumétrie nécessaire). D'autre part, pour faire le groupement sur les six dernier mois, il me faudra faire une clause where contenant le getdate(). Je ne suis pas un expert mais il me semble que l'actualisation de la vue sera alors très couteuse.
Enfin lorsque la requête qui me pose problème est exécutée dix fois par jour c'est déjà le bout du monde. Cela n'empeche pas qu'elle doit être rapide au moment où l'on en besoin....

En tout dernier recours je pourrais construire une table de consolidation par jour que je remplirait tous les jours avec un batch, mais cela perd un peu en précision par rapport au getdate()

Que pouvez vous me conseiller dans ce cas ?