Optimisation de requête récursive avec CTE (With as)

Version imprimable

22/07/2020, 16h02
honeymamy

Optimisation de requête récursive avec CTE (With as)

Bonjour à tous,

Je rencontre actuellement une erreur suite exécution requête assez complexe sur SSMS, la voici :

Ressources internes du processeur de requêtes insuffisantes. Impossible de créer un plan de requête. Cette erreur se produit rarement et uniquement avec des requêtes extrêmement complexes ou qui référencent un grand nombre de tables ou de partitions. Simplifiez la requête. Si vous pensez que vous avez reçu ce message par erreur, contactez le support technique pour plus d'informations.

Je précise que ma requête calcul un solde cumulé par mois selon certaines conditions de compte comptable
Donc j'ai mis en CTE par mois les calculs, pour Février par exemple : se base des données de Janvier.. ainsi de suite
Je reste bloquée pour le mois de Juin

Je ne sais pas côté fonction si ça pourrait marcher pour alléger ce genre de requête mais je ne sais pas comment le faire en fait et je demande votre précieuse aide svp
La requête est sur PJ

Par avance, merci de l’intérêt que vous porterez à ce sujet
22/07/2020, 16h16
escartefigue

bonjour

Les CTE sont chargées en mémoire et là il y en a beaucoup, c'est sans doute l'origine du problème

Essayez de stocker au moins une partie des éléments dans des tables temporaires pour avoir moins de CTE ouvertes simultanément.
22/07/2020, 16h48
aieeeuuuuu

Bonjour,

Il n'y a pas de requête récursive à proprement parler (pas de CTE faisant appel à elle même) ou je ne vois pas où (difficile de vous aider... vous postez une requete de presque 400 lignes pas indentée...).

En revanche, on dirait que chaque CTE s'appuie sur la précédente, donc même si ce n'est as visible, ça donne au final une bonne vingtaine de niveau d'imbrication des tables...
Donc éventuellement, faire une vraie requête récursive, mais vous pourriez faire directement une somme cumulée croissantes avec SUM() OVER(ORDER BY...) plutôt que de le faire à la main

surtout, vous devriez calculer tous les mois dans la même colonne (une ligne par mois avec une colonne supplémentaire indiquant le mois). Vous pouvez si besoin pivoter à la fin, mais il serait même préférable de le faire dans le code applicatif.

mais en l'état, la requête est trop illisible pour bien comprendre ce que vous essayez de faire et donner des pistes plus concrètes.
22/07/2020, 20h05
honeymamy

Re,

Merci pour vos réponses,

Je vais partir avec la première alternative qui me semble facile à intégrer, cad séparer sur des tables temporaires, merci @escartefigue

Puis, j'ai trouvé un article sur le site de Microsoft expliquant les cumuls auto comme @aieeeuuuuu a suggéré. C'est une boite noire pour le moment mais j'essaierai, en apportant qlq nettoyages & améliorations également, comme vous le suggérez, c'est vrai que j'ai posté à la hâte et c'est devenu illisible pour vous.
https://docs.microsoft.com/fr-fr/sql...l-server-ver15

Je vous dirai si cela fonctionne asap
23/07/2020, 14h19
aieeeuuuuu

Bonjour,

Même si la solution d'Escartefigue éviterait certainement votre message d'erreur, je pense qu'il serait néanmoins préférable de réécrire complétement la requête de façon totalement différente. en plus de poser problème au moteur en raison du niveau d'imbrication, elle ne donnera probablement pas de mauvaises performances. Vous découpez trop les traitements unitairement...

Vous pouvez exposer le besoin et le règles de gestion, la structure des tables en jeu et idéalement un jeu d'essai avec résultat attendu, on pourra alors vous aider à réécrire la requête.

Quel est l'intérêt d'un GROUP BY alors que le SELECT n'a pas de calcul d'agrégat ?

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 REQ_TEMP_FEVRIER AS (SELECT REQ1.COMPTE, REQ1.SOCIETE, REQ1.DIVISION, REQ1.ANNEE, REQ1.SOMME_GL, 2 AS MOIS FROM REQ1 WHERE MOIS = 2 GROUP BY COMPTE, SOCIETE, DIVISION, Annee, SOMME_GL)
Plusieurs réponses possibles :
1) je suis masochiste
2) j'aime bien pourrir les performances du serveurs
3) je fais n'importe quoi

Vos dates sont-elles des dates ou des nombres ???

Code:

WHERE ID_DATE_COMPTABLE BETWEEN 20200101 AND 20201231

Effectivement vos deux blocs répétitifs de requêtes devraient pouvoir être fondu en deux requêtes au lieu de 2 x 12.

A +

4 pièce(s) jointe(s)

Pièce jointe 575726Bonsoir,

Bien que la solution avec utilisation des tables temporaires a fonctionné et a résolu en partie mon souci, je rejoins le message plus haut et plus que persuadée que ce n’est pas la bonne pratique et oui il y a plusieurs imbrications la dessus :(
Je reformule le tout avec le plus de clarification possible, parce que oui y a du n'importe quoi dans le fichier je trouve, donc si vous pouvez m’orienter cette fois, j'essaie d'ê plus clair :)

J’ai une table qui enregistre les soldes des comptes financiers. Mon objectif c’est de pouvoir calculer les cumuls mensuels selon des règles bien précises, dont décrites dans les pièces jointes en images :)

Pièce jointe 575703
Pièce jointe 575704

La requête principale avec sous requête mensuelle (ici c'est le traitement des COMPTES créances, mais le principe reste le même pour les comptes n'appartenant pas à ce groupe)

Code:

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
 
WITH REQ1  as 
(
       SELECT 		
       fait.[NUM_COMPTE] AS COMPTE,	
       fait.[SOCIETE] as SOCIETE  ,           
       fait.[DIVISION] as DIVISION,
       sum(SOLDE_GL) as SOMME_GL,
       NumeroDuMoisDeLAnnee as MOIS,	 
       temps.Annee as Annee
       FROM [SOLDE_COMPTE_BILAN] fait
       left join [COMPTE_BILAN] compte on fait.NUM_COMPTE = compte.NUM_COMPTE AND compte.DIVISION = fait.DIVISION
       left join TEMPS temps on fait.ID_DATE_COMPTABLE = temps.ID_DIM_TEMPS --ID_DATE_COMPTABLE est un entier
 
       where compte.GROUPE_2 in(421, 422, 425, 427, 428, 431, 432, 438,443, 444, 445, 447, 448, 451, 455, 457, 512, 518)       
       group by    fait.[NUM_COMPTE] ,fait.[SOCIETE] ,Annee ,fait.[DIVISION],NumeroDuMoisDeLAnnee
) ,
 
REQ_TEMP_FEVRIER as (
       SELECT REQ1.COMPTE,REQ1.SOCIETE, REQ1.DIVISION, REQ1.ANNEE, REQ1.SOMME_GL,2 as MOIS
       FROM REQ1 where MOIS=2 group by COMPTE, SOCIETE, DIVISION, Annee, SOMME_GL 
),
 
REQ_TEMP_MARS as (
       SELECT REQ1.COMPTE,REQ1.SOCIETE, REQ1.DIVISION, REQ1.ANNEE, REQ1.SOMME_GL,3 as MOIS
       FROM REQ1 where MOIS=3 group by COMPTE, SOCIETE, DIVISION, Annee, SOMME_GL 
),

J'ai multiplié jusque Décembre, puis après le calcul selon les règles :

Code:

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
REQ_JANVIER as (
       SELECT 
              COMPTE,SOCIETE, DIVISION, ANNEE, SOMME_GL,1 as MOIS,
              sum(case when Mois=1 and SOMME_GL > 0 then isnull(SOMME_GL,0) else 0 end) as SOMME_GL_ACTIF_01,
              sum(case when Mois=1 and SOMME_GL < 0 then (-1)*isnull(SOMME_GL,0) else 0 end) as SOMME_GL_PASSIF_01 
       FROM REQ1 where Mois=1 group by COMPTE, SOCIETE, DIVISION, Annee, SOMME_GL
),
 
REQ_FEVRIER as(
       SELECT 
              pple.COMPTE,pple.SOCIETE,pple.DIVISION,pple.ANNEE,2 as MOIS, sum(fev.SOMME_GL) as SOMME_GL,
              sum(isnull(janv.SOMME_GL_ACTIF_01,0)) as  SOMME_GL_ACTIF_01,
              sum(isnull(janv.SOMME_GL_PASSIF_01,0)) as SOMME_GL_PASSIF_01,
              sum(case when 
                     (isnull(janv.SOMME_GL_ACTIF_01,0) - isnull(janv.SOMME_GL_PASSIF_01,0) + isnull(fev.SOMME_GL,0))> 0 
                 then 
                     (isnull(janv.SOMME_GL_ACTIF_01,0) - isnull(janv.SOMME_GL_PASSIF_01,0) + isnull(fev.SOMME_GL,0)) 
                 else 0 end )  as SOMME_GL_ACTIF_02,
              sum(case when 
                     ( (-1) * isnull(janv.SOMME_GL_PASSIF_01,0) + isnull(janv.SOMME_GL_ACTIF_01,0) +isnull(fev.SOMME_GL,0) ) <0  
                  then isnull(janv.SOMME_GL_PASSIF_01,0) - isnull(janv.SOMME_GL_ACTIF_01,0) - isnull(fev.SOMME_GL,0) 
                  else 0 end  ) as SOMME_GL_PASSIF_02
       FROM   REQ1 pple left join REQ_JANVIER janv on pple.COMPTE=janv.COMPTE 
       left join REQ_TEMP_FEVRIER fev ON janv.COMPTE=Fev.COMPTE 
       WHERE pple.MOIS=1
       group by pple.COMPTE,pple.SOCIETE,pple.DIVISION,pple.ANNEE),

Il se peut qu'en Février, il existe une transaction d'un nouveau compte donc, en plus j'ai ça

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
REQ_FEV_BIS as(
       select 
              temp_fev.COMPTE,temp_fev.SOCIETE,temp_fev.DIVISION,temp_fev.ANNEE,2 AS MOIS, 
              0 as  SOMME_GL_ACTIF_01,
              0 as SOMME_GL_PASSIF_01,
              case when 
                     (sum(isnull(SOMME_GL_ACTIF_01,0)) - sum(isnull(SOMME_GL_PASSIF_01,0)) + sum(isnull(temp_fev.SOMME_GL,0)))> 0 
              then 
                     sum(isnull(SOMME_GL_ACTIF_01,0)) - sum(isnull(SOMME_GL_PASSIF_01,0)) + sum(isnull(temp_fev.SOMME_GL,0)) else 0 end  
              as SOMME_GL_ACTIF_02,
              case when 
                     ((-1) * sum(isnull(SOMME_GL_PASSIF_01,0)) + sum(isnull(SOMME_GL_ACTIF_01,0)) +sum(isnull(temp_fev.SOMME_GL,0) ) ) <0  
              then 
                     sum(isnull(SOMME_GL_PASSIF_01,0)) - sum(isnull(SOMME_GL_ACTIF_01,0)) - sum(isnull(temp_fev.SOMME_GL,0))   else 0 end   
              as SOMME_GL_PASSIF_02
       from REQ_FEVRIER fev right join  REQ_TEMP_FEVRIER temp_fev on fev.COMPTE = temp_fev.COMPTE 
       WHERE fev.COMPTE is null
       group by temp_fev.COMPTE,temp_fev.DIVISION,temp_fev.SOCIETE,temp_fev.ANNEE
 
),

Donc pour fevrier c'est REQ_FEVRIER union REQ_FEVRIER_BIS

Suite pour MARS :

Code:

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
REQ_MARS as(
-- 1 : selectionner les requete dont compte existe dejà en FEVRIER
       select 
              fev.COMPTE, fev.DIVISION,fev.SOCIETE,fev.ANNEE,3 as MOIS,
              sum(mars.SOMME_GL) as SOMME_GL,
              sum(isnull(fev.SOMME_GL_ACTIF_01,0)) as  SOMME_GL_ACTIF_01,
              sum(isnull(fev.SOMME_GL_PASSIF_01,0)) as SOMME_GL_PASSIF_01,
              sum(isnull(fev.SOMME_GL_ACTIF_02,0)) as  SOMME_GL_ACTIF_02,
              sum(isnull(fev.SOMME_GL_PASSIF_02,0)) as SOMME_GL_PASSIF_02,
              case when 
                      (sum(isnull(fev.SOMME_GL_ACTIF_02,0)) - sum(isnull(fev.SOMME_GL_PASSIF_02,0)) + sum(isnull(mars.SOMME_GL,0))) > 0   
             then sum(isnull(fev.SOMME_GL_ACTIF_02,0)) - sum(isnull(fev.SOMME_GL_PASSIF_02,0)) + sum(isnull(mars.SOMME_GL,0)) 
             else 0 end  as SOMME_GL_ACTIF_03,
              case when 
                     ((-1) * sum(isnull(fev.SOMME_GL_PASSIF_02,0)) + sum(isnull(fev.SOMME_GL_ACTIF_02,0)) +sum(isnull(mars.SOMME_GL,0))) <0  
             then sum(isnull(fev.SOMME_GL_PASSIF_02,0)) - sum(isnull(fev.SOMME_GL_ACTIF_02,0)) - sum(isnull(mars.SOMME_GL,0))    
             else 0 end   as SOMME_GL_PASSIF_03
 
       from REQ_FEVRIER_FINAL fev  left join REQ_TEMP_MARS mars on fev.COMPTE = mars.COMPTE 
 
       group by  fev.COMPTE, fev.DIVISION,fev.SOCIETE,fev.ANNEE
       ),
 
REQ_MARS_BIS as(
-- 2 : selectionner les données, uniquement pour les nouveaux comptes du mois de MARS
select 
       temp_mars.COMPTE,temp_mars.DIVISION,temp_mars.SOCIETE,temp_mars.ANNEE,3 AS MOIS,
       sum(temp_mars.SOMME_GL) as SOMME_GL,
       0 as  SOMME_GL_ACTIF_01,
       0 as SOMME_GL_PASSIF_01,
       0 as  SOMME_GL_ACTIF_02,
       0 as SOMME_GL_PASSIF_02,
       case when 
               (sum(isnull(SOMME_GL_ACTIF_02,0)) - sum(isnull(SOMME_GL_PASSIF_02,0)) + sum(isnull(temp_mars.SOMME_GL,0)))> 0 
      then sum(isnull(SOMME_GL_ACTIF_02,0)) - sum(isnull(SOMME_GL_PASSIF_02,0)) + sum(isnull(temp_mars.SOMME_GL,0)) 
      else 0 end  as SOMME_GL_ACTIF_03,
 
      case when
              ( (-1) * sum(isnull(SOMME_GL_PASSIF_02,0)) + sum(isnull(SOMME_GL_ACTIF_02,0)) +sum(isnull(temp_mars.SOMME_GL,0) )) <0
     then sum(isnull(SOMME_GL_PASSIF_02,0)) - sum(isnull(SOMME_GL_ACTIF_02,0)) - sum(isnull(temp_mars.SOMME_GL,0))
     else 0 end   as SOMME_GL_PASSIF_03
from REQ_MARS mars right join  REQ_TEMP_MARS temp_mars on mars.COMPTE = temp_mars.COMPTE 
WHERE mars.COMPTE is null
group by temp_mars.COMPTE,temp_mars.DIVISION,temp_mars.SOCIETE,temp_mars.ANNEE
 
)

Quelques jeu de données en PJ format rar, puis le resultat attendu pour les 421 à 428, chaque colonne correspond à un mois.
Encore une fois, je sais qu'il y a une autre manière plus propre et plus performant de le faire mais je ne sais pas comment c'est pourquoi je réitère votre soutien, merci d'av

Pièce jointe 575728

Pièce jointe 575727

23/07/2020, 21h12
escartefigue

Déjà cette règle est une source de complexité et de dégradation des performances :
groupe créance = compte dont les trois premiers caractères commencent par [...]

Elle implique d'utiliser des fonctions de chaines de caractères qui rendent l'usage des index impossible.
Quels sont les autres groupes possibles ? Plus ils sont nombreux, plus la création d'un attribut spécifique et d'un index associé deviennent pertinents
23/07/2020, 21h58
honeymamy

@escartefigue >> Ah si, le champ GROUPE_2 qui est dans REQ1 est de type entier

Sorry, j'ai parlé de règles métiers mais techniquement c'est trois premiers chiffres

L'autre alternative c'est de prendre les comptes mais là encore ce sera trop multipliés car un groupe pourra contenir une dizaine de compte à 7chiffres

On pourra faire quoi si on utilise un index avec ce champ GROUPE_2
27/07/2020, 14h32
escartefigue

Tout dépend
- du nombre total de lignes dans la table contenant la colonne "GROUPE_2"
- du nombre de valeurs distinctes prises par cette colonne "GROUPE_2"
- de la répartition statistique de ces valeurs

Exemples défavorables :
- il n'y a que très peu de types de comptes ou alors les comptes de type "groupe créance" représentent une très forte proportion de l'effectif ==> aucun index n'est pertinent pour la recherche

Exemple favorable :
il y a un grand nombre de types de "groupes de compte", la répartition par type est relativement homogène ==> un index est pertinent car filtrant