Discussion :

[cmako]

Bonjour,

Je suis en train de ma battre avec une procédure qui ne se termine jamais.
Cette procédure a une requête qui pose problème uniquement lorsque je l'exécute via un package SSIS. En procédant par élimination j'ai trouvé quelle ligne pose problème.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT ...
INTO #tmp
FROM Table1 t1
	LEFT JOIN Table2 t2
	...
	LEFT JOIN 
	...
	INNER JOIN tn
	...
WHERE	t2.ID IS NULL
	AND	t1.DAT_DEB >= @date_ref -- cette ligne pose problème

Lorsque je commente cette ligne la procédure s'exécute normalement.

CE qui est aussi étonnant c'est que lorsque j'exécute la procédure dans management studio (EXEC proc_toto) cette ligne ne pose pas de problèmes.

Le problème se pose uniquement lorsque je l'exécute via un package SSIS.

En faisant des investigations j'ai remarqué que la requête pose plein de verrous visibles dans l'Activity Monitor. J'ai essayé de rajouter WITH (TABLOCK) sur Table1, j'ai vu moins de verrous mais ça ne règle pas le problème. J'ai également essayé d'ajouter OPTION (KEEP PLAN) - pareil.

Autre remarque importante: la valeur de @date_ref n'a aucun impact sur la requête, même si on rentre une valeur qui doit ramener 0 résultats, par contre si on remplace la variable par une valeur en dur - aucun problème.

Quelqu'un a une idée de ce que ça peut être?

[SQLpro]

1) mettez les préfixes de schéma à tous les objets (dbo par défaut)
2) à quoi vous sert cette table temporaire ? Pouvez vous vous en passer ? Donner nous le code complet de cette procédure...

A +

[cmako]

1) mettez les préfixes de schéma à tous les objets (dbo par défaut)

C'est fait mais ça ne change rien.

2) à quoi vous sert cette table temporaire ? Pouvez vous vous en passer ?

Oui, je peux m'en passer mais il me semble que ce n'est pas elle qui pose problème. Je peux essayer de m'en passer, ça sera plus lourd au niveau du code et plus long car je la réutilise 2 fois.
Une chose dont je ne peux pas m'en passer c'est la @date_ref.

Donner nous le code complet de cette procédure...

Ok

[SQLpro]

Récrivez votre requête en utilisant systématiquement la CTE et évitez :
1) les tables temporaires
2) les sous requêtes en table dérivées
Pour vous aider, lisez le papier que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/s...te-recursives/

Enfin regardez si tous les LEFT OUTER JOIN sont justifiés. Il y en a peut être qui sont transformable en INNER JOIN.

A +

[cmako]

Récrivez votre requête en utilisant systématiquement la CTE et évitez :
1) les tables temporaires
2) les sous requêtes en table dérivées

A +

Je continue les investigations.
En quoi les CTE (non récursifs) sont mieux que les sous-requêtes?

Enfin regardez si tous les LEFT OUTER JOIN sont justifiés. Il y en a peut être qui sont transformable en INNER JOIN

Oui malheureusement, la base de départ est très différente de la finale.

[SQLpro]

En quoi les CTE (non récursifs) sont mieux que les sous-requêtes?

Meilleure factorisation = meilleure optimisation par le moteur et par vous même (pose d'index adéquats).

A +

[cmako]

Meilleure factorisation = meilleure optimisation par le moteur et par vous même (pose d'index adéquats).
A +

J'ai essayé de remplacer les sous requêtes par un CTE, je ne vois aucune différence sur le plan d'exécution. De plus, j'ai peur que les autres collègues se plantent ne connaissant pas bien la syntaxe. En revanche, ne les connaissant pas auparavant, les CTE seraient bien utiles lorsqu'on veut factoriser une requête (réutilisation du code). Malheureusement, les CTE doivent être suivie d'une seule instruction SELECT, INSERT, UPDATE ou DELETE.

Sinon, j'ai résolu le problème. Dans la requête qui posait problème, j'ai ajouté OPTION(KEEP PLAN, RECOMPILE)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT ...
INTO #tmp
FROM Table1 t1
	LEFT JOIN Table2 t2
	...
	LEFT JOIN 
	...
	INNER JOIN tn
	...
WHERE	t2.ID IS NULL
	AND	t1.DAT_DEB >= @date_ref -- cette ligne pose problème
OPTION(KEEP PLAN, RECOMPILE)

Elle fonctionne également si j'écris OPTION(OPTIMIZE FOR (@date_ref = '1950-01-01')), 1950-01-01 est la valeur minimale possible pour @date_ref et qui ramène le maximum de lignes.

Alors maintenant, pourquoi ce problème se pose uniquement en exécutant le package SSIS, j'en ai aucune idée.