Optimiser procédure stockée

Version imprimable

Bonjour,

J'ai une procédure stockée qui prend 3 varchar comme parametres, mais ils peuvent être égaux à "" donc je fais ma clause where en fonction de mes varchar. Si le varchar est vide je ne fais pas de like.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
ALTER PROCEDURE [dbo].[Consult_SearchAvecCodeCltDescSGR] (@CodeCltContient varchar(50),@DescContient varchar(50),@SGRMagContient varchar(50),@Env varchar(50))
AS 
IF ( @CodeCltContient != '' AND @DescContient = '' AND @SGRMagContient = '') 
SELECT DISTINCT KNKNDK,KNKNMN,LAPRTN,LALADI 
FROM KNDF
INNER JOIN KLAF ON KLAF.LAKNDI = KNDF.KNKNDI
WHERE KNKNDK LIKE '%' + @CodeCltContient + '%' AND KNDF.ENV_CD = @Env ORDER BY KNKNDK
 
ELSE IF ( @CodeCltContient != '' AND @DescContient != '' AND @SGRMagContient = '') 
SELECT DISTINCT KNKNDK,KNKNMN,LAPRTN,LALADI 
FROM KNDF
INNER JOIN KLAF ON KLAF.LAKNDI = KNDF.KNKNDI
WHERE KNKNDK LIKE '%' + @CodeCltContient + '%' AND KNKNMN LIKE '%' + @DescContient + '%' AND KNDF.ENV_CD = @Env ORDER BY KNKNDK

Et cela pour tous les autres cas.

Y a t-il un façon plus optimisée d'écrire cette procédure?

Merci d'avance

24/11/2009, 15h54
SQLpro

Le like joker devant + joker derrière n'est pas optimisable par l'indexation courante car il faut lire tout le contenu de la ligne caractère par caractère...
Le like joker derrière est directement optimisable.
Le like joker devant est optimisable à condition de créer une colonne calculée indexée qui renverse le littéral et baser sa recherche dessus

Quand au like avec joker devant et derrière il faut implémenter une stratégie d'indexation textuelle ou recourir aux index rotatifs.

A +
24/11/2009, 16h33
Chacha35

Merci pour ta réponse. Mais c'était surtout au niveau des if que j'aurais voulu optimiser, car je n'ai mis qu'une partie du code.

En effet je fais :

Si varchar1 != "" et varchar2 = "" et varchar3 = ""

Si varchar1 != "" et varchar2 != "" et varchar3 = ""

Si varchar1 != "" et varchar2 != "" et varchar3 != ""
...
24/11/2009, 17h26
SQLpro

1) l'opérateur différent de est <> et non !=
2) les chaines de caractères se délimitent par des apostrophes ' et non par des guillemets. Ceci a son importance pour les performance.
3) il serait préférable de faire un CASE plutôt qu'une série de IF systématiquement testé
4) le mieux est de placer le CASE dans la requête.

A +

J'ai tenter ça mais il me dit qu'il y a une erreur de syntaxe sur le LIKE:

Code:

1
2
3
4
5
6
7
8
9
10
11
 
ALTER PROCEDURE [dbo].[Consult_SearchAvecCodeCltDescSGR] (@CodeCltContient varchar(50),@DescContient varchar(50),@SGRMagContient varchar(50),@Env varchar(50))
AS  
SELECT DISTINCT KNKNDK,KNKNMN,LAPRTN,LALADI 
FROM KNDF
INNER JOIN KLAF ON KLAF.LAKNDI = KNDF.KNKNDI
WHERE 
CASE WHEN @CodeCltContient <> '' THEN KNKNDK LIKE '%' + @CodeCltContient + '%' AND
CASE WHEN @DescContient <> '' THEN KNKNMN LIKE '%' + @DescContient + '%'
CASE WHEN @SGRMagContient <> '' THEN (LAPRTN LIKE '%' + @SGRMagContient + '%' OR LALADI LIKE '%' + @SGRMagContient + '%')
AND KNDF.ENV_CD = @Env ORDER BY KNKNDK

Le squelette de syntaxe de CASE est le suivant :

Code:

1
2
3
4
CASE uneColonneOuUneVariable
	WHEN valeur THEN valeur
	ELSE valeurParDefaut
END

Le ELSE est facultatif, mais pas le END ;)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DISTINCT KNKNDK,KNKNMN,LAPRTN,LALADI 
FROM KNDF
INNER JOIN KLAF ON KLAF.LAKNDI = KNDF.KNKNDI
WHERE CASE
		WHEN @CodeCltContient <> '' THEN KNKNDK LIKE '%' + @CodeCltContient + '%'
	END
AND CASE
		WHEN @DescContient <> '' THEN KNKNMN LIKE '%' + @DescContient + '%'
	END
AND CASE
		WHEN @SGRMagContient <> '' THEN
									(
										LAPRTN LIKE '%' + @SGRMagContient + '%'
										OR LALADI LIKE '%' + @SGRMagContient + '%'
									)
	END
AND KNDF.ENV_CD = @Env ORDER BY KNKNDK

@++ ;)

25/11/2009, 09h50
Chacha35

Merci, ça à l'air mieux, mais j'ai toujours mon erreur de syntaxe sur le like.

Je ne comprend pas pourquoi.:(

Désolé je n'ai pas fait attention.

le LIKE ne peut pas être après le THEN : on ne peut pas mettre un prédicat après THEN, mais seulement une valeur.

Essayez :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT DISTINCT KNKNDK,KNKNMN,LAPRTN,LALADI 
FROM KNDF
INNER JOIN KLAF ON KLAF.LAKNDI = KNDF.KNKNDI
WHERE KNKNDK LIKE CASE
						WHEN @CodeCltContient <> '' THEN '%' + @CodeCltContient + '%'
					END
AND KNKNMN LIKE CASE
					WHEN @DescContient <> '' THEN '%' + @DescContient + '%'
				END
AND 
(
	LAPRTN LIKE CASE
					WHEN @SGRMagContient <> '' THEN '%' + @SGRMagContient + '%'
				END
	OR LALADI LIKE '%' + @SGRMagContient + '%'
)
AND KNDF.ENV_CD = @Env
ORDER BY KNKNDK

mais je ne vous garantis rien ...

@++ ;)

25/11/2009, 10h44
Chacha35

Ca compile.

Mais si j'exécute la procédure stockée avec un parametre varchar null il ne me retourne rien.

Il faut que je fasse un else, mais comment lui dire si le varchar n'est pas différent alors tu ne fais pas le like?

Encore merci :D
25/11/2009, 11h24
elsuket

Vous n'avez pas d'autre choix que de passer par du SQL dynamique ...
Cela vous permettrait d'ailleurs d'avoir des plans de requête un peu plus optimaux ...

@++ ;)
25/11/2009, 15h40
Chacha35

J'ai réussis autrement en écrivant le requete sql dans mon code c#.

Mais merci quand même j'ai appris plein de chose.