Problème de performances après modification d'une requête

**StringBuilder** · 13/02/2019, 11h50

Bonjour,

Je suis en train d'écrire une requête qui me pose un gros souci de performances, que je ne m'explique pas.

Le but de la requête est de calculer, en fonction des commandes, les dates de dernière commande, intervalle entre deux commande et date de prochaine commande par client et produit (en se basant sur l'année du potentiel et la précédente), en fonction de lignes de potentiel déjà existantes

Voici la structure des tables impliquées.
Je m'excuse par avance pour le nommage (illisible, entre Allemand et numérotation auto) et le choix des types loin d'être intelligents (date dans des int, etc.).
Je n'y peux rien, c'est la base de données d'un logiciel tiers, et je n'ai pas la main dessus.

Table des potentiels :
TE_ISI_C035 (208 970 lignes)
- ID bigint => clé primaire
- ID_FI bigint => id du client
- ID_AR bigint => id de l'article de regroupement
- F7007 int => année du potentiel
- F7025 int => date de dernière commande (YYYYMMDD)
- F7026 int => interval moyen entre deux commandes (jours)
- F7027 int => date prévisionnelle de prochaine commande (YYYYMMDD)
- DEL bit => indicateur de suppression

Table des articles :
TE_ISI_AR (196 550 lignes)
- ID bigint => clé primaire
- ArtikelNr nvarchar(45) => code produit, unique
- ID_AR_250 => id de l'article de regroupement (lien sur TE_ISI_AR.ID)
- LosKZ bit => indicateur de suppression

Table des commandes :
TE_ISI_AU (1 524 241 lignes)
- ID bigint => clé primaire
- AufDat int => date de commande (YYYYMMDD)
- ID_FI => id du client
- LosKZ bit => indicateur de suppression

Table des lignes de commande :
TE_ISI_UP (3 151 449 lignes)
- ID bigint => clé primaire
- ArtikelNr nvarchar(45) => code produit (lien vers TE_ISI_AR.ArtikelNr)
- ID_AU bigint => lien vers l'id de la commande
- LosKZ bit => indicateur de suppression

Voici une première requête que j'avais écrit, n'ayant pas fait attention du départ que les lignes de C035 étaient par produit, je n'étais donc pas descendu à la ligne de commande :

Code sql :

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
 
with base (id, mindate, maxdate, nborder) 
as 
(
	select 
		C035.ID, 
		min(AU.AufDat), 
		max(AU.AufDat), 
		count(AU.ID) 
	from TE_ISI_C035 C035 
	left outer join TE_ISI_AU AU on AU.ID_FI = C035.ID_FI and AU.LosKZ = 0 and (AU.AufDat / 10000) in (C035.F7007 - 1, C035.F7007) 
	where C035.DEL = 0 
	group by C035.ID
), 
res (id, maxdate, interval, nextdate) 
as 
(
	select 
		b.id, 
		isnull(b.maxdate, 0), 
		case when nborder <= 1 then 0 else DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder end, 
		case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder, dbo.CU_CRMDate_To_Date(b.maxdate))) end 
	from base b
) 
select 
	dbo.CU_ID_To_StaNo(r.id), 
	dbo.CU_ID_To_SerNo(r.id), 
	r.maxdate, 
	r.interval, 
	r.nextdate 
from res r 
inner join TE_ISI_C035 C035 on C035.ID = r.id and C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate

Ce requête tourne en 3 secondes et donne entière satisfaction.

Seulement, j'ai besoin de descendre à l'article.
Du coup je recherche les articles correspondants aux articles de regroupement indiqués sur les potentiels inner join TE_ISI_AR AR on AR.ID_AR_250 = C035.ID_AR and AR.LosKZ = 0.
Puis je recherche les informations des commandes utilisant les produits en question left outer join (TE_ISI_AU AU inner join TE_ISI_UP UP on UP.ID_AU = AU.ID and UP.LosKZ = 0) on AU.ID_FI = C035.ID_FI and UP.Artikelnr = AR.Artikelnr.

Ce qui donne :

Code sql :

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
 
with base (id, mindate, maxdate, nborder) 
as 
(
	select 
		C035.ID, 
		min(AU.AufDat), 
		max(AU.AufDat), 
		count(AU.ID) 
	from TE_ISI_C035 C035 
	inner join TE_ISI_AR AR on AR.ID_AR_250 = C035.ID_AR and AR.LosKZ = 0
	left outer join 
	(
		TE_ISI_AU AU 
		inner join TE_ISI_UP UP on UP.ID_AU = AU.ID and UP.LosKZ = 0
	) on AU.ID_FI = C035.ID_FI and AU.LosKZ = 0 and (AU.AufDat / 10000) in (C035.F7007 - 1, C035.F7007) and UP.Artikelnr = AR.Artikelnr 
	where C035.DEL = 0 
	group by C035.ID
), 
res (id, maxdate, interval, nextdate) 
as 
(
	select 
		b.id, 
		isnull(b.maxdate, 0), 
		case when nborder <= 1 then 0 else DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder end, 
		case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder, dbo.CU_CRMDate_To_Date(b.maxdate))) end 
	from base b
) 
select 
	dbo.CU_ID_To_StaNo(r.id), 
	dbo.CU_ID_To_SerNo(r.id), 
	r.maxdate, 
	r.interval, 
	r.nextdate 
from res r 
inner join TE_ISI_C035 C035 on C035.ID = r.id and C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate;

Et là c'est le drame : la requête n'en fini plus...

J'ai tenté de créer/modifier des index suivants (les index sont créés depuis l'application, donc à nouveau... j'ai pas la main sur leurs options, juste la liste et l'ordre des colonnes...) :

Code sql :

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
CREATE UNIQUE NONCLUSTERED INDEX [TE_ISI_AR_AF302] ON [dbo].[TE_ISI_AR]
(
	[Artikelnr] ASC,
	[LosKZ] ASC,
	[ID_AR_250] ASC,
	[ID] 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) ON [PRIMARY]
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [TE_ISI_AU_AF310] ON [dbo].[TE_ISI_AU]
(
	[AufDat] ASC,
	[LosKZ] ASC,
	[Status] ASC,  -- inutile pour ma requête
	[ID_FI] ASC,
	[ID] 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) ON [PRIMARY]
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [TE_ISI_UP_AF301] ON [dbo].[TE_ISI_UP]
(
	[LosKZ] ASC,
	[ID_AU] ASC,
	[Artikelnr] ASC,
	[MNO] ASC,  -- inutile pour ma requête
	[ID] ASC
)
INCLUDE ( 	[ID_FI],
	[Artikelbez],  -- inutile pour ma requête
	[Menge],  -- inutile pour ma requête
	[F7001],  -- inutile pour ma requête
	[F7024]  -- inutile pour ma requête
) 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) ON [PRIMARY]
GO
 
-- SQL Server ne veut pas l'utiliser, il prévère le suivant, que je ne peux pas modifier/supprimer
CREATE UNIQUE NONCLUSTERED INDEX [TE_ISI_C035_AF8] ON [dbo].[TE_ISI_C035]
(
	[ID] ASC,
	[F7025] ASC,
	[F7026] ASC,
	[F7027] ASC
)
INCLUDE ( 	[DEL]) 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) ON [PRIMARY]
GO
 
-- Celui-là m'emmerde bien, je ne peux pas le modifier, et SQL Server préfère l'utiliser...
ALTER TABLE [dbo].[TE_ISI_C035] ADD PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
-- Celui-là est bien utilisé par contre
CREATE UNIQUE NONCLUSTERED INDEX [TE_ISI_C035_AF7] ON [dbo].[TE_ISI_C035]
(
	[F7007] ASC,
	[ID_FI] ASC,
	[ID_AR] ASC,
	[DEL] ASC,
	[ID] 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) ON [PRIMARY]
GO

Je n'arrive pas à comprendre pourquoi d'un coup ça devient si long...
J'ai fait un produit cartésien sans l'en rendre compte ?

**StringBuilder** · 13/02/2019, 13h10

Un truc que je remarque, c'est que sur le serveur, c'est calme plat sur les IO.
Seul un cœur (sur 4) travaille.

D'ailleurs c'est très étonnant car j'ai lancé deux requête en simultané (avec une variante) et pourtant un seul cœur est utilisé...
Le serveur est configuré "automatiquement" pour utiliser tous les cœurs disponibles.
Il s'agit d'une édition développeur.

**StringBuilder** · 13/02/2019, 18h06

Bon, y'a de quoi devenir chèvre...

La requête suivante plante au bout de 3 heures :

Code sql :

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
 
with base (id, mindate, maxdate, nborder) 
as 
(
	select 
		C035.ID, 
		min(AU.AufDat), 
		max(AU.AufDat), 
		count(distinct AU.ID) 
	from TE_ISI_C035 C035 
	inner join TE_ISI_AR AR on AR.ID_AR_250 = C035.ID_AR and AR.LosKZ = 0
	left outer join TE_ISI_UP UP on UP.Artikelnr = AR.Artikelnr and UP.LosKZ = 0
	left outer join TE_ISI_AU AU on AU.ID_FI = C035.ID_FI and AU.ID = UP.ID_AU and AU.LosKZ = 0 and AU.AufDat between (C035.F7007 - 1) * 10000 and (C035.F7007 + 1) * 10000 - 1
	where C035.DEL = 0 
	group by C035.ID
), 
res (id, maxdate, interval, nextdate) 
as 
(
	select 
		b.id, 
		isnull(b.maxdate, 0), 
		case when nborder <= 1 then 0 else DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder end, 
		case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder, dbo.CU_CRMDate_To_Date(b.maxdate))) end 
	from base b
) 
select 
	dbo.CU_ID_To_StaNo(r.id), 
	dbo.CU_ID_To_SerNo(r.id), 
	r.maxdate, 
	r.interval, 
	r.nextdate 
from res r 
inner join TE_ISI_C035 C035 on C035.ID = r.id and C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate;

Erreur :

Msg 1101, Level 17, State 10, Line 1
Impossible d'allouer une nouvelle page pour la base de données 'TEMPDB' en raison d'un espace disque insuffisant dans le groupe de fichiers 'DEFAULT'. Créez l'espace nécessaire en supprimant des objets dans le groupe de fichiers, en ajoutant des fichiers supplémentaires au groupe de fichiers ou en définissant Autogrowth sur ON pour les fichiers existants dans le groupe de fichiers.

TempDB est constitué de 4 fichiers de 12 Go et quelques pour un total de 51 Go.
La base de données elle même ne fait que 40 Go !

C'est étrange dans la mesure où je n'ai jamais vu dans le gestionnaire Windows la moindre activité sur le disque mise à part le backup des logs qui tourne toutes les 10 minutes...

J'ai lancé la variante suivante en même temps :

Code sql :

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
 
with base (id, mindate, maxdate, nborder) 
as 
(
	select 
		C035.ID, 
		min(AU.AufDat), 
		max(AU.AufDat), 
		count(distinct AU.ID) 
	from TE_ISI_C035 C035 
	inner join TE_ISI_AR AR on AR.ID_AR_250 = C035.ID_AR and AR.LosKZ = 0
	left outer join 
	(
		TE_ISI_AU AU 
		inner join TE_ISI_UP UP on UP.ID_AU = AU.ID and UP.LosKZ = 0
	) on AU.ID_FI = C035.ID_FI and AU.LosKZ = 0 and AU.AufDat between (C035.F7007 - 1) * 10000 and (C035.F7007 + 1) * 10000 - 1 and UP.Artikelnr = AR.Artikelnr 
	where C035.DEL = 0 
	group by C035.ID
), 
res (id, maxdate, interval, nextdate) 
as 
(
	select 
		b.id, 
		isnull(b.maxdate, 0), 
		case when nborder <= 1 then 0 else DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder end, 
		case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder, dbo.CU_CRMDate_To_Date(b.maxdate))) end 
	from base b
) 
select 
	dbo.CU_ID_To_StaNo(r.id), 
	dbo.CU_ID_To_SerNo(r.id), 
	r.maxdate, 
	r.interval, 
	r.nextdate 
from res r 
inner join TE_ISI_C035 C035 on C035.ID = r.id and C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate;

Seule la CTE "base" diffère. De peu...

Elle a tourné 1 heure pour ramener 13 000 lignes.

Comprends pas pourquoi c'est si long

**SQLpro** · 13/02/2019, 18h25

1) non sargable : (AU.AufDat / 10000) in (C035.F7007 - 1, C035.F7007)
rend le sargable en utilisant des colonnes calculées persistantes

2) itérations sur dbo.CU_CRMDate_To_Date
rend le ensembliste !

Quel est le code de cette fonction ?

3) non sargable :
C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate
pas de solution....

A +

**StringBuilder** · 13/02/2019, 20h31

Envoyé par SQLpro

1) non sargable : (AU.AufDat / 10000) in (C035.F7007 - 1, C035.F7007)
rend le sargable en utilisant des colonnes calculées persistantes

Malheureusement, je ne peux pas utiliser de colonnes calculées : je ne peux pas modifier les tables.

Je pourrais éventuellement faire une vue indexée...
En attendant, j'avais effectivement déjà modifié ce test en :
and AU.AufDat between (C035.F7007 - 1) * 10000 and (C035.F7007 + 1) * 10000 - 1Ca permet au moins d'utiliser l'index sur au.aufdat

Envoyé par SQLpro

2) itérations sur dbo.CU_CRMDate_To_Date
rend le ensembliste !

Quel est le code de cette fonction ?

Code sql :

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
 
CREATE FUNCTION [dbo].[CU_CRMDate_To_Date]
(
	-- CRM date value
	@CRMDate bigint
)
RETURNS DATETIME2
with schemabinding
AS
BEGIN
	declare @res datetime2;
 
	if (@CRMDate = 0)
	begin
		set @res = null;
	end
	else if (@CRMDate < 99999999) -- YYYYMMDD
	begin
		-- Return the result of the function
		set @res = CONVERT(DATETIME2, CAST(@CRMDate AS CHAR(8)), 112);
	end
	else -- YYYYMMDDHHMISSFFF
	begin
		set @res = datetimefromparts(@CRMDate / 10000000000000, (@CRMDate / 100000000000) % 100, (@CRMDate / 1000000000) % 100, (@CRMDate / 10000000) % 100, (@CRMDate / 100000) % 100, (@CRMDate / 1000) % 100, @CRMDate % 1000)
	end;
 
	return @res;
END

Et dans l'autre sens :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE FUNCTION [dbo].[CU_Date_To_CRMDate]
(
	-- Date value
	@Date DATETIME
)
RETURNS bigint
with schemabinding
AS
BEGIN
 
	-- Return the result of the function
	RETURN CONVERT(CHAR, @Date, 112);
 
END

Dans notre cas, la date est au format YYYYMMDD et non YYYYMMDDHHMISSFFF

Envoyé par SQLpro

3) non sargable :
C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate
pas de solution....

A +

ce qui explique pourquoi il ne veut pas de mon index... en même temps, vu la volumétrie, c'est juste pas logique que ça prenne du temps sur ce test.

Anyway...
Finalement, la requête qui a duré 1 heure tout à l'heure, je l'ai relancée depuis un programme... et elle a duré 3 minutes.

Soit c'est SSMS qui déconne (ça m'est déjà arrivé) soit c'est ces saletés de serveurs de 5 lettres qui refont de leurs...
3 seconde à 3 minutes, c'est pitoyable, mais vu que c'est un traitement destiné à tourner 1 fois pas jour... on va dire que ça suffit largement...

**aieeeuuuuu** · 14/02/2019, 10h55

bonjour,

Envoyé par StringBuilder

Seul un cœur (sur 4) travaille.

C'est normal, votre requête utilise une fonction scalaire, ce qui empêche le parallélisme.

Réécrivez les fonctions en fonctions tables en ligne.

**StringBuilder** · 14/02/2019, 11h03

Ca je veux bien, mais ce que je comprends pas, c'est que quand je lance une seconde requête, elle devrait en toute logigue, si possible, tourner dans un thread/process différent, et du coup sur un cœur différent.

**aieeeuuuuu** · 14/02/2019, 11h12

et avec un index sur TE_ISI_AR (ID_AR_250) WHERE LosKZ = 0 ?

**SQLpro** · 14/02/2019, 12h57

Remplacez cela : [dbo].[CU_Date_To_CRMDate]
par CAST(... AS DATE) et vous verrez ça va 1000 fois plus vite !

De même pour l'autre, rajoutez une table de dates avec la correspondance int et date.

A +

**StringBuilder** · 14/02/2019, 15h18

Hmmm, c'est pas follichon...

La requête "originale" : (non mise en forme, c'est juste pour l'avoir sous la main)

Code sql :

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

1
2
 
with base (id, mindate, maxdate, nborder) as (select C035.ID, min(AU.AufDat), max(AU.AufDat), count(distinct AU.ID) from TE_ISI_C035 C035 inner join TE_ISI_AR AR on AR.ID_AR_250 = C035.ID_AR and AR.LosKZ = 0left outer join (TE_ISI_AU AU inner join TE_ISI_UP UP on UP.ID_AU = AU.ID and UP.LosKZ = 0) on AU.ID_FI = C035.ID_FI and AU.LosKZ = 0 and AU.AufDat between (C035.F7007 - 1) * 10000 and (C035.F7007 + 1) * 10000 - 1 and UP.Artikelnr = AR.Artikelnr where C035.DEL = 0 group by C035.ID), res (id, maxdate, interval, nextdate) as (select b.id, isnull(b.maxdate, 0), case when nborder <= 1 then 0 else DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder end, case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder, dbo.CU_CRMDate_To_Date(b.maxdate))) end from base b) select dbo.CU_ID_To_StaNo(r.id), dbo.CU_ID_To_SerNo(r.id), r.maxdate, r.interval, r.nextdate from res r inner join TE_ISI_C035 C035 on C035.ID = r.id and C035.F7025 <> r.maxdate and C035.F7026 <> r.interval and C035.F7027 <> r.nextdate

La CTE qui travaille sur les dates :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
res (id, maxdate, interval, nextdate) 
as 
(
  select 
    b.id, 
    isnull(b.maxdate, 0), 
    case when nborder <= 1 then 0 else DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder end, 
    case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, dbo.CU_CRMDate_To_Date(b.mindate), dbo.CU_CRMDate_To_Date(b.maxdate)) / b.nborder, dbo.CU_CRMDate_To_Date(b.maxdate))) end 
  from base b
)

=> La requête dure 00:06:59

La CTE réécrite sans fonction scalaire :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
res (id, maxdate, interval, nextdate) 
as 
(
  select 
    b.id, 
    isnull(b.maxdate, 0), 
    case when nborder <= 1 then 0 else DATEDIFF(d, cast(cast(b.mindate as char(8)) as date), cast(cast(b.maxdate as char(8)) as date)) / b.nborder end, 
    case when nborder <= 1 then 0 else cast(convert(char(8), DATEADD(d, DATEDIFF(d, cast(cast(b.mindate as char(8)) as date), cast(cast(b.maxdate as char(8)) as date)) / b.nborder, cast(cast(b.maxdate as char(8)) as date)), 112) as int) end 
  from base b
)

=> La requête dure 00:06:56

L'optimisation ne saute pas aux yeux...

Et comme d'hab, vu que j'aime bien tester tout et n'importe quoi, l'impensable se produit (enfin...)
Une version à moitié modifié, mais pas complètement :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
res (id, maxdate, interval, nextdate) 
as 
(
  select 
    b.id, 
    isnull(b.maxdate, 0), 
    case when nborder <= 1 then 0 else DATEDIFF(d, cast(cast(b.mindate as char(8)) as date), cast(cast(b.maxdate as char(8)) as date)) / b.nborder end, 
    case when nborder <= 1 then 0 else dbo.CU_Date_To_CRMDate(DATEADD(d, DATEDIFF(d, cast(cast(b.mindate as char(8)) as date), cast(cast(b.maxdate as char(8)) as date)) / b.nborder, cast(cast(b.maxdate as char(8)) as date))) end 
  from base b
)

=> 00:06:44

Bon, ça change pas des masses, mais c'est pourtant plus rapide alors qu'il y a pourtant une optimisation en moins...

Pour ce qui est de la table des dates, vu que c'est un traitement de nuit qui ne tourne qu'une seule fois par jour, je vais m'en passer...
Ceci dit, la version initiale utilisait bien ces fonctions, et elle ne durait que 3 secondes.
Le gros souci de performances que j'ai, c'est lors de l'ajout des tables TE_ISI_AR et TE_ISI_UP à la requête.

Problème de performances après modification d'une requête [2016]

Développement SQL Server

Discussions similaires

Partager

Partager