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 ?

[Batou69]

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

Bon ben je m'auto répond : une vue ne prend pas de place, c'est l'index sur une vue qui en prend.

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.

Contrairement à ce que je pensait, une vue n'est pas actualisée en temps réel. C'est l'interrogation de la vue qui est longue.

MAIS : c'est bien ce que je craignais, je ne peux pas mettre de sous requete dans la définition de la vue. J'ai le message d'erreur :
Impossible de créer index dans la vue xxx car elle référence la table dérivée xx. Erreur 10109.

Donc j'ai aussi tenté de supprimer la table dérivée, mais maintenant j'ai une erreur sur le getdate qui n'est pas déterministe. Il ne peut pas créer d'index.

Je sais plus quoi faire pour accélérer cette requête !!

Voici le code initial de création de la vue :

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
31
32
33
34
35
36
37
38
39
 
CREATE VIEW CODAGE_OPERATEUR_6M
WITH SCHEMABINDING
AS
	SELECT
		COUNT_BIG(*) NB
		, CON.ID_UTILISATEUR
		, CON.DATE_DEBUT
		, DATEDIFF( MINUTE
					, CASE 
						WHEN CON.DATE_DEBUT < DATEADD( MONTH, -6, GETDATE() ) THEN DATEADD( MONTH, -6, GETDATE() )
						ELSE CON.DATE_DEBUT
					END
					, COALESCE( CON.DATE_FIN, GETDATE() )
				) TEMPS_CON
		, SUM(1) NB_COLIS_CODES
		, SUM( CASE 
					WHEN RESULTAT_SANCTION = 5 THEN 1
					WHEN RESULTAT_SANCTION = 6 THEN 1
					ELSE 0
				END
		) NB_COLIS_REJET
	FROM 
		dbo.COLIS COL
		, dbo.CONNEXION CON
	WHERE 
		COL.DATE_SANCTION > DATEADD( MONTH, -6, GETDATE() )
		AND COL.ID_UTILISATEUR_AC = CON.ID_UTILISATEUR
		AND DATE_SANCTION BETWEEN CON.DATE_DEBUT AND CON.DATE_FIN
	GROUP BY
		CON.ID_UTILISATEUR
		, CON.DATE_DEBUT
		, DATEDIFF( MINUTE
					, CASE 
						WHEN CON.DATE_DEBUT < DATEADD( MONTH, -6, GETDATE() ) THEN DATEADD( MONTH, -6, GETDATE() )
						ELSE CON.DATE_DEBUT
					END
					, COALESCE( CON.DATE_FIN, GETDATE() )
				 )

Y a t'il quelqu'un pour me venir en aide svp ??
Merci

[SQLpro]

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.

Une, deux, trois... vues ! Aucune importance. Le volume d'une base de données ne reflète pas directement ses performances....
De plus le volume d'un index d'agrégation est extrêmement faible. Vous dites 3000 insertion jours. Dans une vue d'agrégation journalière ceci se transformera en 1 seule ligne.... !

Enfin, pas question évidemment de passer la dateheure courante, ceci devant être fait dans la requête final sur la vue....

Si votre agrégat est glissant par mois révolus, il suffit de créer une vue d'agrégation avec un groupage par an/mois et donc de l'indexer....

Après vous pourrez faire n'importe quelle interrogation du genre :

Code :

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

1
2
3
SELECT *
FROM    MaVueIndexée
WHERE  MMaDate Between ??? AND ???

Si votre glissement est au jour le jour, vous pouvez déjà faire une vue indexée avec un groupage jour.

Si vous voulez le must du must, définissez 2 vues avec les agrégats suivants :

• vue1 groupage Jour
• vue2 groupage Mois

Puis encapsulez l'appel de calcul global glissant au jour près en piochant dans les différentes vues.
Exemple 1 an glissant du mardi 21 octobre 2008 au mardi 20 octobre 2009 inclus :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM   V1
WHERE DATE BETWEEN 21/10/2008 AND 31/10:2008 --> fin du mois 2008
UNION ALL
SELECT *
FROM   V2
WHERE DATE BEWTEEN 1/11/2008 AND 30/09/2009 --> 11 mois
SELECT *
FROM   V1
WHERE DATE BEWTEEN 1/10/2009 AND 20/10/2009 --> début mois 2009

A +

[SQLpro]

Par exemple :

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
CREATE VIEW dbo.CODAGE_OPERATEUR_6M
WITH SCHEMABINDING
AS
	SELECT
		COUNT_BIG(*) NB
		, CON.ID_UTILISATEUR
		, CON.DATE_DEBUT, 
          COALESCE(CON.DATE_FIN, '99991231'),
          DATE_SANCTION
		, COUNT_BIG(NB_COLIS_CODES) AS NB_COLIS_CODES
		, SUM( CASE 
					WHEN RESULTAT_SANCTION = 5 THEN 1
					WHEN RESULTAT_SANCTION = 6 THEN 1
					ELSE 0
				END
		) NB_COLIS_REJET
	FROM 
		dbo.COLIS COL
        INNER JOIN dbo.CONNEXION CON
              ON COL.ID_UTILISATEUR_AC = CON.ID_UTILISATEUR
 
	GROUP BY
				, CON.ID_UTILISATEUR
		, CON.DATE_DEBUT, 
          CON.DATE_FIN,
          DATE_SANCTION

A +

[Batou69]

Super !

J'ai donc crée une vue indexée avec une agrégation non plus sur les six derniers mois mais sur toute l'année (contenu complet de la table). C'est pas forcement plus lent, cela renvoie 'trop' de lignes mais au moins je peux indexer la vue.

En revanche j'ai une dernière question : la requête pour générer la vue met environ 20 secondes a s'exécuter dans l'analyseur.
L'index est il mis a jours à chaque insertion/modification de la table sous jacente ?

Si oui alors comment fait il pour mettre a jour l'index dynamiquement sachant que la table sous jacente change plus vite que le temps de recalcul ?

Sinon alors j'ai un décalage entre ma vue et les données. Dans ce cas comment le mesurer ?

Bon ok cela plus d'une question, mais le fonctionnement interne m'intéresse.
J'ai demandé une formation certifiée à mon chef pour l'an prochain

[SQLpro]

En revanche j'ai une dernière question : la requête pour générer la vue met environ 20 secondes a s'exécuter dans l'analyseur.
L'index est il mis a jours à chaque insertion/modification de la table sous jacente ?

Si oui alors comment fait il pour mettre a jour l'index dynamiquement sachant que la table sous jacente change plus vite que le temps de recalcul ?

Sinon alors j'ai un décalage entre ma vue et les données. Dans ce cas comment le mesurer ?

Bon ok cela plus d'une question, mais le fonctionnement interne m'intéresse.
J'ai demandé une formation certifiée à mon chef pour l'an prochain

1) oui l'index est synchrone, donc mis à jour à tout insert/update/delete

2) cela prendre très peu de temps car il agit par différence et non par recalcul (attention votre expression de requête doit être EXACTE).

3) étant synchrone, vous n'aurez jamais de décalage...

Pourquoi ne pas venir vous former chez Orsys ou nous donnons des cours en toute indépendance marketing de MS ? Cela vous apprendra réellement des choses et vous pourrez e,nsuite acheter la bouquin pour bachoter l'exam !

Rudi et moi même donnons la plupart des cours sur MS SQL Server, y compris le plus intéressant de tous, celui d'optimisation qui n'a pas d'équivalence en richesse avec les cours officiels MS !

A +

[Batou69]

2) cela prendre très peu de temps car il agit par différence et non par recalcul (attention votre expression de requête doit être EXACTE).

Heuu... question bête mais, qu'entendez vous par expression de requête doit être exacte ?

Pourquoi ne pas venir vous former chez Orsys ou nous donnons des cours en toute indépendance marketing de MS ? Cela vous apprendra réellement des choses et vous pourrez e,nsuite acheter la bouquin pour bachoter l'exam !

Rudi et moi même donnons la plupart des cours sur MS SQL Server, y compris le plus intéressant de tous, celui d'optimisation qui n'a pas d'équivalence en richesse avec les cours officiels MS !

A +

Pas de problème, j'ai vu que vous avez aussi des sessions sur Lyon. C'est le SQS qui me plait bien. J'ai transmis la demande. Malheureusement, la suite n'est pas de mon ressort, cela est, comme souvent, laissé à son appréciation.

++

[SQLpro]

Heuu... question bête mais, qu'entendez vous par expression de requête doit être exacte ?

Dues aux mesures physiques les calculs en réels (type float ou real) peuvent être incohérent vu qu'il travaille par différence. Donc il faut utiliser du DECIMAL à la place.
Pour tester si votre expression est précise, vous pouvez utiliser :

Code :

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

SELECT COLUMNPROPERTY(OBJECT_ID('matable/vue'), 'macolonne', 'IsPrecise ')

La session SQS est rarement donné sur Lyon, car pas assez de candidats. Elle peut donc être reportée sur celle de Paris !

A =

[Batou69]

Très bien, j'ai bien noté.

Je vous remercie pour votre aide !!