Discussion :

[aieeeuuuuu]

Bonjour,

Pour trouver la date du premier jour du mois en cours, j'utilise classiquement la méthode suivante

Code :

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

1
2
3
4
5
DATEADD(
		MONTH
	,	DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
	,	0
	)

Utilisée dans une clause WHERE, je constate cependant que le moteur semble utiliser la date de référence (0 dans mon exemple, soit le 1er janvier 1900) pour l'estimation des cardinalités, et non pas le résultat du calcul (soit 1er aout 2017)
Cela peut donc mener à des plans d’exécution inefficaces.

exemple :
création et peuplement d'une table de test

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE gt (
		id	INT			NOT NULL	PRIMARY KEY IDENTITY
	,	dh	DATETIME2(3)	NOT NULL
);
 
INSERT INTO gt 
	SELECT TOP(1000) DATEADD(DAY, -ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), CURRENT_TIMESTAMP)
	FROM sys.objects A, sys.objects B
;

La table est donc peuplée avec une ligne par jour sur les 1000 derniers jours.

Pour la requête ci-dessous qui remonte 28 lignes, le moteur estime à 1000 le nombre de lignes, soit la table tout entière.

Code :

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

1
2
3
4
5
6
7
8
SELECT	id, dh
FROM	gt
WHERE	dh >=	DATEADD(
					MONTH
				,	DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
				,	0
				)
;

Alors que si l'on change uniquement la date de référence, par celle du 1er aout 2017, le moteur estime à 29 le nombre de lignes, ce qui est correct

Code :

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

1
2
3
4
5
6
7
8
SELECT id, dh
FROM	gt
WHERE dh >=		DATEADD(
					MONTH
				,	DATEDIFF(MONTH, '20170801', CURRENT_TIMESTAMP)
				,	'20170801'
				)
;

Je constate cela sur un SQL Server 2012

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64) 
	Sep 23 2016 16:56:29 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Sur un SQL Server 2014, le probléme disparait, sauf si on passe en mode de compatibilité 2012 ou 2008

Code :

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

alter database current set compatibility_level = 110

Avez-vous déjà constaté ce problème ?
Comment le contournez-vous ?

[SQLpro]

1) dès que l'on a application de fonctions sur les données, l'estimation de cardinalité devient en théorie impossible.
2) Dans certains cas SQL Server récrit les fonctions intégrées pour pouvoir quand même estimer les cardinalités, mais cela est moins précis qu'un critère direct.
3) l'estimateur de cardinalité à changé à partir de la version 2014

Vous pouvez obtenir de meilleurs performance en ajoutant une colonne calculée persistante (éventuellement indexée)

A +

[aieeeuuuuu]

oui, sauf que là justement, je ne fait pas de calcul sur les données, mais uniquement sur CURRENT_TIMESTAMP pour obtenir la date de début de mois.

Plus globalement, ma question peut se résumer à :

Comment obtenir efficacement les données du mois en cours ( ou du mois précédent par exemple) dans une table ayant plusieurs années de données.

La seule alternative que j'ai trouvée pour le moment, et de calculer la date dans une variable, et d'utiliser la variable dans la requête.
Dans ce cas, le moteur part sur des statistique globales (30% des lignes estimées dans le cas de la requête que j'ai postée avec un >= )
Si on ajoute un OPTION (RECOMPILE), alors les estimations deviennent correctes...

Comme ce n'est pas une solution adaptée à toutes les situations, loin s'en faut, je me demandais quelles étaient vos pratiques pour ce genre de requête (recherche pour une plage de dates relative à la date en cours)

[StringBuilder]

Pourquoi ne pas stocker ce "premier jour du mois" dans une variable et l'utiliser dans la requête ?

[aieeeuuuuu]

Oui, c'est l'option que j'ai retenue pour le moment, comme je le dis dans mon post précédent.

D'ailleurs, plutôt qu'un OPTION RECOMPILE, on peut utiliser OPTIMIZE FOR afin d'éviter les recompilations, et de garder des estimations correctes (si les plages de dates sont similaires entre plusieurs exécutions de la requête, et que les données ont une répartition homogène dans le temps.)

En fait, je me suis surtout étonné sur le coup de n'avoir jamais remarqué ce problème.
Je me demandais quelles étaient vos habitudes pour ce genre de requête (filtrage sur une date de façon dynamique par rapport à la date courante). ça reste une "classique" (total des ventes du mois précédent,...)

[SQLpro]

En fait tu as une problématique potentielle d'estimation incorrecte liée aux dates :
si je rajoute les nouvelles factures et que ces dernières représentent moins que le seuil de déclenchement du recalcul des stats, l'optimiseur ne "verras" pas ces lignes et son estimation est faussée.
C'est pourquoi il est important de mettre à jour certaines stats systématiquement en mode FULLSCAN après des insertions massives.

A +

[rudib]

Je trouve qu'

Code :

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

OPTION RECOMPILE

est une bonne solution pour ce genre de problème. Le temps de compilation ne me gène pas par rapport au gain obtenu par une estimation correcte de la cardinalité. Ton problème disparaît en 2014 parce que le nouvel estimateur de cardinalité (2014) est censé mieux gérer ce problème (problème de clé ascendante : https://www.sqlskills.com/blogs/joe/...er-calculator/). Peut-être l'usage de statistiques filtrée peut aussi améliorer les choses.

Tu peux voir comment sont utilisées les statistiques avec ces options de requête :

Code :

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

1
2
3
4
5
6
7
8
 
OPTION
(
    RECOMPILE,
    QUERYTRACEON 3604,-- redirige la sortie
    QUERYTRACEON 9204 -- indique les statistiques utilisées
)
GO

[aieeeuuuuu]

l'optimiseur ne "verras" pas ces lignes et son estimation est faussée.
C'est pourquoi il est important de mettre à jour certaines stats systématiquement en mode FULLSCAN après des insertions massives.

non, le problème que je veux aborder n'est pas là. Il pourrait éventuellement se poser sur des requêtes du type "total des commandes depuis le début de l'année en cours" qui forcément n'est pas le même en janvier qu'en décembre (ou alors, ça sent le dépôt de bilan )

il s'agit plutôt de la façon dont l'optimiseur "traite" les bornes indiquées dans la clause WHERE. Il ne calcule pas ses bornes avant l’exécution de la requête (alors qu'elle ne font appel à aucune donnée de la base, uniquement la date du serveur). Au lieu de ça, il s'appuie sur la seule date qu'il trouve dans l'expression (soit le 1er janvier 1900 dans mon exemple)

Même avec des stats à jour, le problème se pose.

[elsuket]

Bonjour,

En mode de compatibilité 100 ou 110, ce problème est assez récurrent.
Il vient du fait que, comme on utilise zéro à la place du 1er janvier 1900, le moteur réalise un transtypage implicite : cette méthode produit généralement un défaut d'estimation de cardinalité.

On le retrouve dans le plan de la requête que tu as proposé :



C'est un cas particulier qui ne permet malheureusement pas au moteur de réaliser un estimation décente
D'une certaine manière, ceci rejoint les propos de SQLPro dans le sens où, dès que l'on applique une fonction, l'optimisation est impossible.

En revanche, chose assez rigolote : en mode de compatibilité 130 et avec l'indicateur de requête QUERYTRACEON 9481, censé forcer la compilation avec l'ancien optimiseur, on n'a pas ce problème.
Par contre, en compatibilité 100 ou 110, et avec l'indicateur de requête QUERYTRACEON 2312, c'est à dire qu'on demande la compilation avec le nouvel optimiseur, on a toujours le problème.

Autre petit essai : si on change l'expression qui permet de récupérer le date de début du mois, on obtient un estimation du nombre de lignes, après transtypage implicite, à ... 1

Code :

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

1
2
3
SELECT	id, dh
FROM	gt
WHERE	dh >= DATEADD(day, -DATEPART(day, CAST(SYSDATETIME() AS date)) + 1, CAST(SYSDATETIME() AS date))

Tout cela tant est si bien que j'ai mis en place un audit des requêtes dont le plan comporte des CONVERT_IMPLICIT

@+

[aieeeuuuuu]

Il vient du fait que, comme on utilise zéro à la place du 1er janvier 1900, le moteur réalise un transtypage implicite

Le transtypage implicite n'y est malheureseuement pour rien. Si on change la déifnition de la table pour mettre la date en DATETIME au lieu de DATETIME2, il n'y a plus ce transtypage, mais le problème reste identique

Autre petit essai : si on change l'expression qui permet de récupérer le date de début du mois, on obtient un estimation du nombre de lignes, après transtypage implicite, à ... 1

Oui, justement, cela vient du fait que tu utilises la date du jour - et non plus le 1er janvier 1900 - comme date de référence. C'est au final ce que je reproche au moteur (dans cette version) : ne pas faire le calcul en amont, alors que tout est statique dans l'expression du calcul du début du mois.