Discussion :

[StringBuilder]

Bonjour,

J'ai une procédure stockée qui exécute cette requête :

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
 
insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, fi_extsys, fi_extkey, rep, rep_extsys, rep_extkey, [date], SB_StaNo, SB_SerNo)
	select
			'DEL',
			0,
			0,
			'',
			'',
			0,
			'',
			'',
			@dte,
			dbo.CU_ID_To_StaNo(sb.ID),
			dbo.CU_ID_To_SerNo(sb.ID)
	from dbo.te_isi_sb sb
	where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
	and not exists (
		select 1 / 0
		from ##tmp tmp
		left outer join dbo.te_isi_id id on id.id = sb.BearbeiterId
		where sb.F4018 = concat(isnull(dbo.CU_ID_To_StaNo(tmp.id_fi), ''), '_', isnull(dbo.CU_ID_To_SerNo(tmp.id_fi), ''), '_', isnull(id.ExtKey, ''))
	);

La procédure tourne depuis plusieurs heures, mais on va dire que c'est normal.

En revanche, dans le même temps, j'exécute à intervalle régulier cette requête :

Code sql :

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

1
2
3
4
 
select allocated, mode, count(*)
from cu_customerallocation with (readuncommitted)
group by Allocated, mode;

Et là, je vois qu'une ligne est créée dans la table toutes les 3 ou 4 minutes…

Il n'y a aucun autre traitement concurrent sur la table.

Comment est-ce possible que le insert soit aussi lent ?
J'ai toujours pensé que c'était le SELECT qui était lent, mais une fois évalué, l'insert du jeu de résultat était quasi instantané, non ?
Comment ça se fait que là ça rame autant ?

[François DORIN]

J'ai toujours pensé que c'était le SELECT qui était lent, mais une fois évalué, l'insert du jeu de résultat était quasi instantané, non ?
Comment ça se fait que là ça rame autant ?

J'avoue que je ne me suis jamais posé la question, mais naïvement, j'ai effectivement envie de dire que le SELECT est évalué, puis ensuite le INSERT, car les bons SGBD sont avant tout ensemblistes et non séquentiels (bien entendu, le tout de manière transparente et atomique pour l'utilisateur).

Difficile pour l'instant de répondre à ta question, mais je vois quelques pistes : y a-t-il des triggers qui sont affectés à cette table ? Et comment sont définies tes fonctions dbo.CU_ID_To_StaNo et dbo.CU_ID_To_SerNo ?

As-tu également affiché le plan d'exécution de la requête, pour voir s'il n'y aurait pas des surprises ? Y a-t-il du RLS d'activé ?

[StringBuilder]

Bonjour François,

Non, aucun trigger sur les tables utilisées, et accessoirement, aucune autre activité sur la base (je suis sur une base de DEV et seul à bosser dessus).

Les deux fonctions sont très simples :

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
 
ALTER FUNCTION [dbo].[CU_ID_To_StaNo] 
(
	-- Parameter ID
	@ID bigint
)
RETURNS bigint
with schemabinding
AS
BEGIN
 
	-- Return the result of the function
	RETURN @ID / 4294967296;
 
END
 
ALTER FUNCTION [dbo].[CU_ID_To_SerNo] 
(
	-- Parameter ID
	@ID bigint
)
RETURNS bigint
with schemabinding
AS
BEGIN
 
	-- Return the result of the function
	RETURN @ID & cast(4294967295 as bigint);
 
END

J'ai modifié ma procédure pour avoir directement le résultat de la concaténation sous forme d'une colonne calculée persisted, ainsi qu'un index dessus (ça me fait bizzare de faire des données persisted et un index dans une table temporaire) :

Code sql :

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

1
2
3
 
create table #tmp (id_fi bigint, id_id bigint, id_extkey varchar(80), extkey as (concat(cast(id_fi / 4294967296 as bigint), '_', id_fi & cast(4294967295 as bigint), '_', id_extkey)) persisted, primary key (id_fi, id_id));
create unique index uix_tmp on #tmp (extkey);

Maintenant la requête est instantanée (et plus lisible )
J'en ai aussi profité pour nettoyer un peu le code en simplifiant un truc ou deux (notamment disparition des isnull() puisque les colonnes ne sont jamais nulles)

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
 
	insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, fi_extsys, fi_extkey, rep, rep_extsys, rep_extkey, [date], SB_StaNo, SB_SerNo)
	select
			'DEL',
			0,
			0,
			'',
			'',
			0,
			'',
			'',
			@dte,
			dbo.CU_ID_To_StaNo(sb.ID),
			dbo.CU_ID_To_SerNo(sb.ID)
	from dbo.te_isi_sb sb
	where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
	and not exists (
		select 1 / 0
		from #tmp tmp
		where sb.F4018 = tmp.extkey
	);

Pas trop compris pourquoi ça a autant d'impact sur les performances par contre...

[François DORIN]

Pas trop compris pourquoi ça a autant d'impact sur les performances par contre...

Les index, ont a presque l'impression que c'est magique parfois ! Clairement, ici, leur impact est plus que positif, et rien d'étonnant à cela. Sans, la sous-requête dans le NOT EXISTS doit scanner l'ensemble des enregistrements de ta table temporaire. Avec, la réponse est quasi immédiate car une simple recherche d'index permet de trouver la réponse.

Mais cela ne répond toujours pas à la question initiale du pourquoi les insertions se font ligne par ligne avec un intervalle de plusieurs minutes entre chaque... On peut avoir une idée du nombre d'enregistrements de la table temporaire ? Et la config de la machine de test (notamment la RAM allouée à SQL Server) ?

[StringBuilder]

Visiblement la table temporaire était très grosse (environ 1,7 million de lignes, ça commence à faire pour une table temporaire).

Quant à la config, ça commence à devenir étriqué :
4 cœurs à 2,19 GHz (j'ai réussi à faire benner les Goupil l'an dernier)
8 Go de RAM (4096 fois plus que la NASA n'en affait pour la mission Apollo 13 !)
Les différentes bases et leurs logs sont découpés en plusieurs fichiers répartis sur 6 disques logiques (le tout sur le même NAS par contre… on n'a plus les bandes magnétiques depuis la semaine dernière)

Le tout pour :
Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)
4 bases de données de 90 Go
Et 12 bases en tout (hors bases systèmes)

Là j'ai un traitement (basé sur le résultat de la procédure) qui tourne depuis 12 heures et qui a traité 40 000 lignes (sur 1,7 millions grmpf) et un CPU est à 100% d'occupation.
Reste plus qu'à demander à l'infra un nouveau serveur pour Noël, j'aurai peut-être un CPC 6128+ !

[Invité]

Est-ce que tes disques sont plein ? En forme ? As-tu vérifié ta vitesse d'écriture / lecture ?

[François DORIN]

Visiblement la table temporaire était très grosse (environ 1,7 million de lignes, ça commence à faire pour une table temporaire).

Donc le gain de performance lié à l'ajout d'un index s'explique très bien !

4 cœurs à 2,19 GHz (j'ai réussi à faire benner les Goupil l'an dernier)
8 Go de RAM (4096 fois plus que la NASA n'en affait pour la mission Apollo 13 !)
Les différentes bases et leurs logs sont découpés en plusieurs fichiers répartis sur 6 disques logiques (le tout sur le même NAS par contre… on n'a plus les bandes magnétiques depuis la semaine dernière)

Pour une machine de dev, ça passe
Après, les disques sont sur un NAS (en iSCSI je suppose). Donc il faut tenir compte du réseau et de son potentiel encombrement.

Et comme le dit 7gyY9w1ZY6ySRgPeaefZ, vérifie aussi les disques, ainsi que le taux d'utilisation du NAS (pas l'espace disque du NAS, mais bien l'occupation du NAS). Est-il dédié, ou fait-il autre chose qui pourrait expliquer des lenteurs d'accès ?.

[StringBuilder]

Est-ce que tes disques sont plein ? En forme ? As-tu vérifié ta vitesse d'écriture / lecture ?

Bah le souci vient de là (mais pas que).

On a deux NAS :
- Un "vrai" NAS à base de disques magnétiques qui ont plus de 10 ans.
- Un SAN à base de disques SSD flambant neufs (enfin... ils doivent bien avoir 3 ou 4 ans maintenant)

Et là dessus il y a des centaines de VM qui tournent.
Donc, même si quand je parle de PC Carrefour à l'infra ça les fait sauter au plafond, même avec une connectique de malade, je vois pas comment, même avec du SSD, on va pouvoir avoir des débits de fou lorsqu'il y a de la concurrence de plusieurs milliers de process sur la même baie de disques...

Il y a quelques années, on avait des soucis de temps de réponse disque et réseau, car l'hyperviseur n'utilisait qu'un seul CPU pour gérer les IO (disques et réseau) et qu'il était en constante saturation.

C'est soit-disant corrigé depuis, mais à chaque fois que je tente de faire des "bench" pour essayer de comprendre où ça coince, j'ai des résultats incompréhensibles...
Par moment je vais pouvoir copier un fichier d'une partition à l'autre à plus de 1 Go/s.
Et d'autres fois, le même fichier va se transférer à quelques Ko/s avec des pauses toutes les 10 secondes...

Alors forcément, SQL Server il subit la même chose.

Mais indépendamment de ça, ce qui me surprenais c'était que la partie "insert" soit si lente, alors que les lignes étaient visiblement déjà trouvées par le SELECT.

Après, j'ai vidé la table (par un DELETE et non un TRUNCATE)... et j'ai fini par comprendre... il a mis près d'une heure à supprimer les lignes... par lot de quelques dizaines et une pause à chaque paquet.

Je vais me chercher une corde.

[aieeeuuuuu]

bonjour,

Non, un SELECT peut "renvoyer" des données avant que la requête ne soient complément terminée dans certains cas. Cela dépend du plan d'exécution et de la présence ou non d'opérateurs bloquants.

ici, la seule restriction est un NOT EXISTS, probablement traduit par une boucle imbriquée, qui n'est pas un opérateur bloquant, il parait donc logique que les lignes soient insérées au fur et à mesure qu'elles sont traitées par l'opération de boucle imbriquée.

En revanche, il est probable aussi que la table temporaire ne tienne pas en mémoire (surtout si sa taille est sous évaluée, avec par exemple une estimation d'une seule ligne pour la table, ce qui est loin d'être le cas... que donne la requête initiale avec un option recompile ?).
En l'absence d'index, le NOT EXISTS provoque un scan de table dans une boucle imbriquée, et si la table temporaire ne tient pas en mémoire en totalité, c'est alors le disque qui va prendre le relais, et comme le système disque ne semble pas performant... c'est le drame

Enfin ce n'est qu'une théorie, et le plan d’exécution réel pourrait nous en dire un peu plus...

[SQLpro]

Bonjour François,

Non, aucun trigger sur les tables utilisées, et accessoirement, aucune autre activité sur la base (je suis sur une base de DEV et seul à bosser dessus).

Les deux fonctions sont très simples :

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
 
ALTER FUNCTION [dbo].[CU_ID_To_StaNo] 
(
	-- Parameter ID
	@ID bigint
)
RETURNS bigint
with schemabinding
AS
BEGIN
 
	-- Return the result of the function
	RETURN @ID / 4294967296;
 
END
 
ALTER FUNCTION [dbo].[CU_ID_To_SerNo] 
(
	-- Parameter ID
	@ID bigint
)
RETURNS bigint
with schemabinding
AS
BEGIN
 
	-- Return the result of the function
	RETURN @ID & cast(4294967295 as bigint);
 
END

...

Pas trop compris pourquoi ça a autant d'impact sur les performances par contre...

En fait dès que tu met une UDF, le parcours des données devient itératif (scan de table). Si tu les éradiques, il est capable de faire un parcours optimisé en tenant compte des index et en utilisant le parallélisme.

Pour info, dans la version 2019, ce problème sera partiellement éliminé, par tranformation de l'UDF en expression en ligne dans la requête... Cela s’appelle le "Scalar UDF Inlining"...

A lire :
https://blogs.msdn.microsoft.com/sql...-udf-inlining/


A +

[aieeeuuuuu]

est-ce que tu es sûr de toi ?

ok pour le parallélisme, mais dans la mesure où les fonctions scalaire ne sont utilisées que dans le SELECT, il ne me semble pas que cela empêche l'utilisation d'indexes...

[SQLpro]

Au passage SCHEMABINDING n'a aucun intérêt puisque la fonction n'encapsule par d'appel à d'autres objets...

A +

[aieeeuuuuu]

si, il y a un intérêt : lier la fonction au schéma est une condition pour qu'elle soit considérée comme déterministe, ce qui peut influer pas mal sur les performances dans certains cas....

[SQLpro]

si, il y a un intérêt : lier la fonction au schéma est une condition pour qu'elle soit considérée comme déterministe, ce qui peut influer pas mal sur les performances dans certains cas....

Cela n'a d'intérêt que pour les fonctions écrites en SQL CLR... SQL Server sait par défaut pour une UDF si elle est déterministe ou non...

Petite démo...

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
CREATE FUNCTION F_DATE ()
RETURNS DATE
AS
BEGIN
   RETURN CAST(GETDATE() AS DATE)
END
GO
 
CREATE FUNCTION F_DATE_AN2000 ()
RETURNS DATE
AS
BEGIN
   RETURN CAST('2000-01-01' AS DATE)
END
GO
 
CREATE FUNCTION F_DATE_AN3000 ()
RETURNS DATE
WITH SCHEMABINDING
AS
BEGIN
   RETURN CAST('3000-01-01' AS DATE)
END
GO
 
SELECT OBJECTPROPERTYEX(object_id, 'isDeterministic'), name 
FROM   sys.objects WHERE name LIKE 'F?_DATE%' ESCAPE '?'

A +

[aieeeuuuuu]

Je n'ai pas compris le sens de la démo... les trois fonctions sont non deterministes par nature. Les lier au schéma ne change donc rien en effet.

en revanche, si on reprend la première fonction de StringBuilder, et que l'on en crée une lié au schéma, l'autre non :

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
 
USE tempdb;
GO
 
CREATE FUNCTION [dbo].[CU_ID_To_StaNo] 
(
	-- Parameter ID
	@ID bigint
)
RETURNS bigint
AS
BEGIN
 
	-- Return the result of the function
	RETURN @ID / 4294967296;
 
END
 
GO
 
CREATE FUNCTION [dbo].[CU_ID_To_StaNo_SCHEMABOUND] 
(
	-- Parameter ID
	@ID bigint
)
RETURNS bigint
with schemabinding
AS
BEGIN
 
	-- Return the result of the function
	RETURN @ID / 4294967296;
 
END

Code :

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

1
2
3
4
 
SELECT R.ROUTINE_NAME, R.IS_DETERMINISTIC
FROM INFORMATION_SCHEMA.ROUTINES AS R
WHERE R.ROUTINE_NAME LIKE 'CU_ID_To_StaNo%' ESCAPE'/'

-->

ROUTINE_NAME                                                                                                                     IS_DETERMINISTIC
-------------------------------------------------------------------------------------------------------------------------------- ----------------
CU_ID_To_StaNo                                                                                                                   NO
CU_ID_To_StaNo_SCHEMABOUND                                                                                                       YES

si on crée ensuite deux tables temporaires identiques, utilisant l'une puis l'autre des fonctions :

Code :

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

1
2
3
4
 
 
CREATE TABLE #T_SCHEMABOUND(val BIGINT, result AS  [dbo].[CU_ID_To_StaNo_SCHEMABOUND](val) )
CREATE TABLE #T(val BIGINT, result AS  [dbo].[CU_ID_To_StaNo](val) )

Créons maintenant un index sur la colonne calculée :
Table avec la fonction liée au schéma

Code :

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

CREATE INDEX IX_T_SCHEMABOUND_Result ON #T_SCHEMABOUND(result);

-->

Commande(s) réussie(s).

Sur la table avec la fonction non liée au schéma :

Code :

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

CREATE INDEX IX_T_Result ON #T(result);

-->

Msg*2729, Niveau*16, État*1, Ligne*15
Impossible d'utiliser la colonne 'result' de table '#T' dans un index, des statistiques ou en tant que clé de partition car elle n'est pas déterministe.

D'ailleurs, au passage, @StringBuilder : il n'est pas nécessaire ici que la colonne calculée soit persistante pour pouvoir créer un index, car le type est précis (c'est en revanche nécessaire avec des types imprécis comme FLOAT)

[StringBuilder]

Merci pour toutes ces informations

J'ai obtenu de passer de 8 à 16 Go sur le serveur.
Même si c'est pas folichon, ça a drastiquement amélioré les performances : au lieu de mettre plus de 48 heures à tourner, l'ensemble du traitement ne dure plus que 4 heures...

En revanche je constate toujours ces pauses à interval régulier. Je pense qu'on a clairement un souci d'IO quelque part, mais augmenter la mémoire a permis de réduire ces dernières... Vu que c'est un traitement exceptionnel (en temps normal il ne traite quelques dizaines de milliers de lignes et non des millions) on va dire que c'est satisfaisant.

[Michel.Priori]

Bonsoir,

Il me semble acquit que le problème de performance vient de la saturation mémoire.

Je n'ai pas compris, il me semble avoir lu que la requête est instantanée après indexation ; pourquoi l'ajout de RAM ? pourquoi 4 heures ?

Bon, disons que ce n'est pas le sujet. En absence du plan d’exécution on est réduit à faire des supputations.
Est-ce que le SELECT est sensible au nombre de lignes issues de la table dbo.te_isi_sb ?
Est-ce qu'on peut imaginer une approche par paquets de 100, 1000 ou 10000 lignes ?

[StringBuilder]

Il me semble acquit que le problème de performance vient de la saturation mémoire.

En fait, le problème me semble venir de plusieurs facteurs, qui se marchent sur les pieds :
- CPU clairement trop faibles. Il y a 4 cœurs sur la VM. Je pourrais en demander plus, mais à ça ne sert à rien : il y a peu de gros traitements concurrents, donc autant lors de certains traitements, je peux avoir 1 ou 2 cœurs à 100% d'occupation pendant des dizaines de minutes, autant les autres cœurs se tournent les pouces pendant ce temps. Là, le seul moyen d'améliorer ça serait de changer de machine hôte, mais il paraît qu'il faut amortir les investissements d'y il y 10 ans avant d'en refaire de nouveaux…
- IO mal maîtrisées. On a le choix entre un NAS très anciens avec des disques magnétiques en fin de vie (un disque à changer par jour, avec plusieurs heures de reconstruction du RAID à chaque fois… heureusement à chaud) ou un SAN flambant neuf (enfin… il doit bien avoir 3 ou 4 ans maintenant) à base de RAID 10 sur des SSD. Bref, le choix entre la peste et le choléra, puisque dans l'idéal il faudrait un bon NAS… A ça on ajoute que le système de virtualisation dédie un seule cœur aux IO… et que les CPU sont à la ramasse, et donc on se retrouve avec des IO ralenties.
- Par conséquent, les gens (client comme développeurs) s'énervent, relancent les traitements qui mettent trop longtemps, et on arrive à une surconsommation des ressources qui empire lorsqu'il y a un pic.
- Visiblement, le fait d'ajouter de la mémoire permet de réduire grandement le nombre d'IO, puisqu'on a moins besoin de travailler dans tempdb ni charger et décharger les données entre chaque requête. Donc moins d'IO, moins de charge sur le CPU qui gère les IO, et on retrouve des performances "normales" sur les baies de disque.

Je n'ai pas compris, il me semble avoir lu que la requête est instantanée après indexation ; pourquoi l'ajout de RAM ? pourquoi 4 heures ?
Bon, disons que ce n'est pas le sujet. En absence du plan d’exécution on est réduit à faire des supputations.

Le souci, c'est que ma procédure stockée est un peu longue, et n'est qu'une étape dans un traitement plus long. La création d'un index a donc permis de grandement améliorer les performances d'une partie du traitement, mais je suis ensuite tombé sur d'autres points problématiques…

Est-ce que le SELECT est sensible au nombre de lignes issues de la table dbo.te_isi_sb ?

Oui, absolument, dans la mesure où une partie du traitement consiste à identifier les lignes qui sont en trop ou en manque dans cette table par rapport à une liste théorique. A partir du moment où je fois faire des not exists d'une liste de plusieurs millions de lignes sur une table de plusieurs millions de lignes… bah ça n'arrange pas que la table soit grosse

Est-ce qu'on peut imaginer une approche par paquets de 100, 1000 ou 10000 lignes ?

Pour résumer, on a une table qui défini des règles du type : "tel commercial s'occupe des clients de telle catégorie sur telle tranche de code postaux". J'ai plusieurs milliers de lignes là dedans, avec un total de 6 règles différentes, utilisant diverses données dans plusieurs tables différentes, donc certaines peuvent se chevaucher.
A partir de là, je génère dans une table temporaire toutes les relations commercial <-> client théoriques, sans doublons. Du coup, là je ne peux pas trop découper en paquets, sinon je vais générer des doublons.
Ensuite, je compare cette liste théorique au contenu de la véritable table de relations, et vu que ce serait trop simple de directement bosser en SQL, je dois passer par un programme d'import (qui recalcule entre autre des colonnes binaires cryptés que je ne sais pas décoder ni générer… mais qui sont vitales pour l'application). Du coup, je stocke dans une table persistante cette fois le résultat de ma comparaison. Des lignes avec une colonne "mode" initialisée à "NEW" pour celles qui doivent être ajoutées, et "DEL" pour celles qui au contraire doivent être supprimées.
A nouveau, à moins de lancer X fois le programme d'import ce qui, vu son fonctionnement, ne me semble pas du tout permettre de gagner en performances, je suis obligé de tout traiter d'un coup.
Enfin, je lance l'import (une fois en mode création et une fois en mode suppression) pour mettre à jour ma table te_isi_sb. Et là c'est le drame, car le programme d'import gère les lignes 1 à 1, avec une transaction à chaque ligne… mais tout un tas de traitements complémentaires qui durent un temps pas possible… bref, en suppression, ça supprime un peu plus de 1000 lignes par minute, et en création, ça va encore un peu plus lentement…

A ça, on ajoute que suite à un changement de nature d'une colonne, il faut regénérer l'ensemble des 1,7 millions de lignes de la table te_isi_sb et on arrive à un traitement qui tourne depuis 3 jours, et je ne suis pas certain que demain matin il ait fini…

Pour la gloire, voici le code complet de la PS...
J'avoue que je ne sais plus trop par où la prendre pour améliorer ses performances... même si maintenant le fait qu'elle tourne 15 ou 20 minutes n'est plus vraiment un problème comparé au temps pharaonique de l'import qui s'en suit...

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:		Un collèque
-- Create date: 2015-07-31
-- Description:	Build customer allocation (SB) from matrix C024
--				SB is updated through the use of a temporary table and an import
-- History:
--			2015-09-11 Un collèque : Create allocation for Customers only
--			2015-10-29 Un collèque : Species criteria is now linked to the species of the productions (C022) of the customer
--			2015-11-27 Un collèque : Do not allocate and delete allocation of inactive users
--			2016-05-04 Moi : Optimisation
--			2016-05-19 Moi : Réécriture complète
--			2016-06-17 Moi : Ajout de SB_StaNo et SB_SerNo pour la suppression
--			2018-06-19 Moi : Ajout d'une règle pour pouvoir recopier les SB d'un rep vers un autre
--			2018-10-24 Moi : Ajout d'une règle pour pouvoir affecter une plage de code postaux
--			2019-01-25 Moi : Ajout de critères d'exclusions d'une règle à l'autre pour éviter qu'une règle plus large ne prenne le pas sur un paramétrage plus fin)
--			2019-06-06 Moi : Ajout d'un test sur SB.FreiL1 (Disabled) en plus de LosKZ
--			2019-09-16 Moi : Modification du test pour suppression des SB avec mauvaise clé externe + petites optimisations
-- =============================================
ALTER PROCEDURE [dbo].[CU_Customer_Allocation]
AS
BEGIN
	SET NOCOUNT ON;
 
	declare @dte datetime2;
 
	-- Création d'une table temporaire qui servira à générer l'allocation complète
	print 'Création de la table temporaire';
	create table #tmp (id_fi bigint, id_id bigint, extkey as (concat(cast(id_fi / 4294967296 as bigint), '_', id_fi & cast(4294967295 as bigint), '_', id_id)) persisted, primary key (id_fi, id_id));
	create unique index uix_tmp on #tmp (extkey);
 
	set @dte = SYSDATETIME();
 
	-- Suppression des allocations archivées
	print 'Suppression des allocations déjà traitées';
	DELETE CU_CustomerAllocation WHERE Allocated = 1;
 
	-- Archivage de la précédente allocation
	print 'Archivage des allocations en cours';
	UPDATE CU_CustomerAllocation SET Allocated = 1 WHERE Allocated = 0;
 
	print 'Génération de la liste des allocations théoriques';
	insert into #tmp (id_fi, id_id)
 
	-- Règle 1
	-- On affecte à un utilisateur les clients d'un tenant qui font une espèce donnée dans une plage de codes INSEE donnée
	SELECT fi.id, id.id
	FROM dbo.te_isi_C024 C024
	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
	INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
	WHERE C024.del = 0
	and C024.f7000 > 0
	and C024.f7004 > 0
	and C024.f7006 > 0
	and C024.f7007 = 0
	and C024.f7008 = 0
	and isnull(C024.F7011, '') <> ''
	and isnull(C024.F7012, '') <> ''
	and C024.F7015 = 0
	and isnull(C024.F7019, '') = ''
	and isnull(C024.F7020, '') = ''
	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
	and exists (
		select *
		from te_isi_C022 SP
		where SP.F7003 = c024.f7006 AND SP.ID_FI = FI_GLO.ID AND SP.DEL = 0
	)
	union
	-- Règle 2
	-- On affecte à un utilisateur les clients d'un tenant sans une espèce dans une plage de codes INSEE donnée
	SELECT fi.id, id.id
	FROM dbo.te_isi_C024 C024
	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
	INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
	WHERE C024.del = 0
	and C024.f7000 > 0
	and C024.f7004 > 0
	and C024.f7006 = 0
	and C024.f7007 = 0
	and C024.f7008 = 0
	and isnull(C024.F7011, '') <> ''
	and isnull(C024.F7012, '') <> ''
	and C024.F7015 = 0
	and isnull(C024.F7019, '') = ''
	and isnull(C024.F7020, '') = ''
	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
	union
	-- Règle 3
	-- On affecte à un utilisateur les clients d'un tenant qui ont un réseau donné sans sous-réseau dans une plage de codes INSEE donnée
	SELECT fi.id, id.id
	FROM dbo.te_isi_C024 C024
	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and FI.F7020 = C024.F7007 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
	WHERE C024.del = 0
	and C024.f7000 > 0
	and C024.f7004 > 0
	and C024.f7006 = 0
	and C024.f7007 > 0
	and C024.f7008 = 0
	and isnull(C024.F7011, '') <> ''
	and isnull(C024.F7012, '') <> ''
	and C024.F7015 = 0
	and isnull(C024.F7019, '') = ''
	and isnull(C024.F7020, '') = ''
	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
	union
	-- Règle 4
	-- On affecte à un utilisateur les clients d'un tenant qui ont un réseau et un sous-réseau donné dans une plage de codes INSEE donnée
	SELECT fi.id, id.id
	FROM dbo.te_isi_C024 C024
	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and FI.F7020 = C024.F7007 and FI.F7021 = C024.F7008 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
	WHERE C024.del = 0
	and C024.f7000 > 0
	and C024.f7004 > 0
	and C024.f7006 = 0
	and C024.f7007 > 0
	and C024.f7008 > 0
	and isnull(C024.F7011, '') <> ''
	and isnull(C024.F7012, '') <> ''
	and C024.F7015 = 0
	and isnull(C024.F7019, '') = ''
	and isnull(C024.F7020, '') = ''
	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
	union
	-- Règle 5
	-- On affecte à un utilisateur tous les SB d'un autre utilisateur
	SELECT SB.ID_FI, C024.F7000
	FROM dbo.TE_ISI_C024 C024
	INNER JOIN dbo.TE_ISI_SB SB on SB.BearbeiterId = C024.F7015 and SB.LosKZ = 0 and SB.FreiL1 = 0
	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
	WHERE C024.DEL = 0
	and C024.F7000 > 0
	--and C024.f7004 = 0
	and C024.f7006 = 0
	and C024.f7007 = 0
	and C024.f7008 = 0
	and isnull(C024.F7011, '') = ''
	and isnull(C024.F7012, '') = ''
	and C024.F7015 > 0
	and isnull(C024.F7019, '') = ''
	and isnull(C024.F7020, '') = ''
	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
	union
	-- Règle 6
	-- On affecte à un utilisateur les clients d'un tenant sans une espèce dans une plage de codes postaux donnée
	SELECT fi.id, id.id
	FROM dbo.te_isi_C024 C024
	INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
	inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.Plz between c024.f7019 and c024.f7020
	INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
	WHERE C024.del = 0
	and C024.f7000 > 0
	and C024.f7004 > 0
	and C024.f7006 = 0
	and C024.f7007 = 0
	and C024.f7008 = 0
	and isnull(C024.F7011, '') = ''
	and isnull(C024.F7012, '') = ''
	and C024.F7015 = 0
	and isnull(C024.F7019, '') <> ''
	and isnull(C024.F7020, '') <> ''
	and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014;
 
	-- On enregristre la liste des SB à désallouer
	print 'Identification des allocations existantes à supprimer';
	insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, rep, [date], SB_StaNo, SB_SerNo)
	select
			'DEL',
			null,
			null,
			null,
			@dte,
			dbo.CU_ID_To_StaNo(sb.ID),
			dbo.CU_ID_To_SerNo(sb.ID)
	from dbo.te_isi_sb sb
	where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
	and not exists (
		select 1 / 0
		from #tmp tmp
		where sb.F4018 = tmp.extkey
	);
 
	-- On exclu les allocations déjà existantes
	print 'Exclusion des allocations déjà correctes';
	delete #tmp
	where exists
	(
		select *
		from dbo.te_isi_sb sb
		where sb.BearbeiterId = #tmp.id_id
		and sb.ID_FI = #tmp.id_fi
		and sb.LosKZ = 0
		and sb.FreiL1 = 0
		and sb.F4018 = #tmp.extkey
	);
 
	-- On enregistre les allocations à créer
	print 'Ajout des allocations à créer';
	insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, rep, [date], SB_StaNo, SB_SerNo)
	select 'NEW', 
			dbo.CU_ID_To_StaNo(FI.ID) CompanyStaNo,
			dbo.CU_ID_To_SerNo(FI.ID) CompanySerNo,
			id.id,
			@dte,
			dbo.CU_ID_To_StaNo(SB.ID) SB_StaNo,
			dbo.CU_ID_To_SerNo(SB.ID) SB_SerNo
	from #tmp tmp
	inner join dbo.te_isi_fi fi on fi.id = tmp.ID_fi
	inner join dbo.te_isi_id id on id.id = tmp.id_id
	left outer join dbo.te_isi_sb sb on sb.LosKZ = 0 and sb.F7007 = 1 and sb.FreiL1 = 1 and sb.F4018 = tmp.extkey;
 
	-- On détruit la table temporaire
	print 'Destruction de la table temporaire';
	drop table #tmp;
END;

[aieeeuuuuu]

J'avoue que je ne sais plus trop par où la prendre pour améliorer ses performances...

En premier lieu, je mettrai la création de l'index sur la table temporaire après le peuplement de celle-ci, voire créer la clef primaire après également.

ensuite, quelques pistes à tester éventuellement.
Concernant la requête avec les UNION : voir s'il n'est pas possible de remplacer par une seule requête avec des CASE en remplacement. pas sûr du résultat toutefois, et cela risque de nuire à la lisibilité/maintenance en "mélangeant" les différentes règles de gestion...

Concernant les différentes opération sur la table dbo.CU_CustomerAllocation : remplacer par un MERGE pour effectuer en une fois le DELETE et l'UPDATE du début, mais également peut-être l'INSERT final, si la table temporaire est peuplée avant. Là aussi, la transformation risque d'être compliquée, et le résultat risque de nuire à la lisibilité du code. Par sûr non plus que cela améliore vraiment les perf, ce n'est qu'une piste à tester.

En revanche, vérifier aussi que la fonction CU_Date_To_CRMDate est bien liée au schéma. Au delà de l’aspect déterministe déjà évoqué plus haut, il y a un autre point également important, car la fonction est utilisée dans le cadre d'un INSERT. Si elle n'est pas liée au schéma, le moteur ignore si la fonction accède ou non aux données de la table visée par l'INSERT. Il mettra donc en place une protection contre l'effet Halloween, ce qui se traduira par un spool de table relativement couteux, surtout dans ton cas puisque la RAM semble un peu juste...

[StringBuilder]

Merci d'avoir pris le temps de relire la procédure

En premier lieu, je mettrai la création de l'index sur la table temporaire après le peuplement de celle-ci, voire créer la clef primaire après également.

C'est fait en effet.
A la base, j'aime pas trop désactiver les index/clés mais c'est vrai que sur une table temporaire, je ne risque pas grand chose.

ensuite, quelques pistes à tester éventuellement.
Concernant la requête avec les UNION : voir s'il n'est pas possible de remplacer par une seule requête avec des CASE en remplacement. pas sûr du résultat toutefois, et cela risque de nuire à la lisibilité/maintenance en "mélangeant" les différentes règles de gestion...

Si tu regardes dans l'historique de la procédure, tu verras "2016-05-19 Moi : Réécriture complète".
C'est à ce moment que j'ai mis en place les UNION.
En effet, avant il y avait toute une tripotée de CASE, mais aussi et surtout un grand nombre de OR. Du coup aucun index n'était utilisé, et le select seul mettait à l'époque plus de 12 heures à tourner (alors qu'il y avait à peine le tiers des données actuelles, et la moitié des règles).
Les UNION ont donc permis de m'affranchir des OR, et, j'ose espérer, permettre à SQL Server de paralléliser les différents SELECT.

Concernant les différentes opération sur la table dbo.CU_CustomerAllocation : remplacer par un MERGE pour effectuer en une fois le DELETE et l'UPDATE du début, mais également peut-être l'INSERT final, si la table temporaire est peuplée avant. Là aussi, la transformation risque d'être compliquée, et le résultat risque de nuire à la lisibilité du code. Par sûr non plus que cela améliore vraiment les perf, ce n'est qu'une piste à tester.

Je pense surtout que je vais virer ce flag "allocated" qui ne sert au final à rien du tout... et remplacer tout ça par un truncate de la table en début de traitement.

En revanche, vérifier aussi que la fonction CU_Date_To_CRMDate est bien liée au schéma. Au delà de l’aspect déterministe déjà évoqué plus haut, il y a un autre point également important, car la fonction est utilisée dans le cadre d'un INSERT. Si elle n'est pas liée au schéma, le moteur ignore si la fonction accède ou non aux données de la table visée par l'INSERT. Il mettra donc en place une protection contre l'effet Halloween, ce qui se traduira par un spool de table relativement couteux, surtout dans ton cas puisque la RAM semble un peu juste...

Elle est déjà liée au schéma : (attention, ne pas vomir)

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
 
ALTER 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