Discussion :

[diallochercheur]

Je suis actuellement sur un projet BI, et j'ai besoin de votre expertise. J'ai trois table sur une même instance sql avec les volumétries suivantes :
-Table 1 : Plus de 10 millions de ligne
-Table 2 : Plus de 60 millions de ligne
-Table 3 : Plus de 320 millions de ligne
Trois pachages déployés sur un autre serveur insèrent des données chaque 30mn dans ses tables.
J'attaque ces tables depuis Power BI report builder via des procédures stockées. Je suis confronté à un problème de performance. Comme solution, j'ai opté pour le partionning en filegroup et après création des index cluster sur ide_date. Cette solution marche apparemment pour les deux premières tables pour lesquelles, j'ai un temps de réponse relativement bon. Mais les rapports basé sur la table 3 sont toujours lent lorsque je fais des analyses sur plusieurs mois. Pour cette table 3, j'ai crée une partition pour chaque jours soit une moyenne de 3 millions de lignes par partition et un total de 204 partitions.
je viens solliciter votre aide à travers vos conseils et bonne attitude à appliquer. Ci joint un exemple de code de procédure stocké que j’exécute depuis le rapport en fonction des paramètres saisi

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
40
41
42
ALTER PROCEDURE [dbo].[USP_DIFFERENTIAL_PRESSURE]
 
	@VARIABLE VARCHAR(MAX)
	,@TYPE_PERIODE INT
	,@DATE_DEBUT DATE, @DATE_FIN DATE	
	,@HEURE_DEBUT VARCHAR(8), @HEURE_FIN VARCHAR(8)	
AS
BEGIN
 
DECLARE 
@VAR_VARIABLE VARCHAR(MAX) = @VARIABLE
,@VAR_TYPE_PERIODE INT = @TYPE_PERIODE
,@VAR_DATE_DEBUT INT = cast(convert(varchar(8),cast(@DATE_DEBUT as date),112) as int)
,@VAR_DATE_FIN INT = cast(convert(varchar(8),cast(@DATE_FIN as date),112) as int)
,@VAR_HEURE_DEBUT VARCHAR(8) = @HEURE_DEBUT, @VAR_HEURE_FIN VARCHAR(8) = @HEURE_FIN
 
select 
s.Code_Site, s.Libelle_Site
,cl.Code_Client, cl.Libelle_Client
,e.Code, e.Libelle_Equipement
,u.Unite, f.Ide_Date
,cast(cast(f.Ide_Date as varchar(8)) as date) Date_Jour
,f.Heure_Complete
,f.Heure  
,f.Heure+':'+f."Minute" Heure_Minute
,f."Minute"
,f.Seconde
,@VAR_TYPE_PERIODE Type_Periode
,v.Variables
,f.Differential_Press 
from FAIT_COMPT_GAZ_LIVRE f
join DIM_VARIABLE v on f.Ide_Variable = v.Ide_Variable
join DIM_SITE s on f.Ide_Terminal = s.Ide_Site
join DIM_CLIENT cl on f.Ide_Clients = cl.Ide_Client
join DIM_EQUIPEMENT e on f.Ide_Equipements = e.Ide_Equipement
join DIM_UNITE u on f.Ide_Unite = u.Ide_Unite
where 
f.Ide_Date between @VAR_DATE_DEBUT and @VAR_DATE_FIN
and f.Heure_Complete between @VAR_HEURE_DEBUT and @VAR_HEURE_FIN
and CHARINDEX(',' + CAST(f.Ide_Variable AS VARCHAR(MAX)) + ',', ',' + REPLACE(@VAR_VARIABLE, ' ', '') + ',' ) > 0
order by f.Ide_Date, f.Heure, f."Minute", f.Seconde
END

[al1_24]

Je suppose que le premier paramètre est une liste d'identifiants séparés par une virgule
Il faudrait commencer par transformer cette liste en table (temporaire) sur laquelle faire une jointure...

[soazig]

Bonjour,
Pour al1_24, mettre le contenu de @VARIABLE en table temporaire pourrait aider , mais à condition de normaliser aussi DIM_VARIABLE.
Le problème tel que je le vois c'est que mettons que dans dim_variable il y ait A,B,C,D, et que dans @variable il y ait B,C on veut récupérer la valeur, mais s'il y a C,B on ne veut pas l'avoir, il faut donc aussi un ordre.
[edit] à la relecture j'ai l'impression que c'est le contraire
dans dim_variable il y B,C et dans @variable A,B,C,D
[/edit]
Si Dim_variable est petite, on pourrait décomposer le problème en deux.
Mettre dans une table temporaire les Ide_Variable correspondants au critère.
puis faire la requête en joignant sur la table temporaire.
Mais j'ai l'impression comme al1_24 que le problème réside surtout sur dim_variable.
Cordialement
Soazig

[Michel.Priori]

Bonsoir,

Comme solution, j'ai opté pour le partionning en filegroup et après création des index cluster sur ide_date. Cette solution marche apparemment pour les deux premières tables pour lesquelles, j'ai un temps de réponse relativement bon. Mais les rapports basé sur la table 3 sont toujours lent lorsque je fais des analyses sur plusieurs mois.

Est-ce que le plan d’exécution montre l'utilisation du partitionnement ?
Si non, il faut se poser les bonnes questions