Performance de la fonction SUBSTRING()

Version imprimable

Bonjour à tous,

Je rencontre un cas actuellement ou j'ai un SELECT dans une fonction qui prends environ 7 secondes par exécution. Le SELECT se fait sur 40000 lignes.

La requete est la suivante:

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
 
ALTER FUNCTION [dbo].[SDD_PMTINFID] (@lot_id VARCHAR(20))
RETURNS VARCHAR(35) AS
BEGIN
 
  DECLARE @ret_ VARCHAR(35)='';
 
-- Recuperation du compte concerne
SELECT @ret_ = 
				SUBSTRING(
				CASE WHEN 
						SUBSTRING(PAIEMENTX.USERZONE011,1,2) NOT IN ('T/','T|') THEN PAIEMENTX.USERZONE011 
				WHEN 
						(SUBSTRING(PAIEMENTX.USERZONE011,1,2) IN ('T/','T|')) AND (SUBSTRING(PAIEMENTX.USERZONE012,1,2) NOT IN ('T/','T|')) THEN PAIEMENTX.USERZONE012 
				WHEN
						(SUBSTRING(PAIEMENTX.USERZONE011,1,2) IN ('T/','T|')) AND (SUBSTRING(PAIEMENTX.USERZONE012,1,2) IN ('T/','T|')) THEN PAIEMENTX.USERZONE013 
				END,
				CHARINDEX('||',
				CASE WHEN 
						SUBSTRING(PAIEMENTX.USERZONE011,1,2) NOT IN ('T/','T|') THEN PAIEMENTX.USERZONE011 
				WHEN 
						(SUBSTRING(PAIEMENTX.USERZONE011,1,2) IN ('T/','T|')) AND (SUBSTRING(PAIEMENTX.USERZONE012,1,2) NOT IN ('T/','T|')) THEN PAIEMENTX.USERZONE012 
				WHEN 
						(SUBSTRING(PAIEMENTX.USERZONE011,1,2) IN ('T/','T|')) AND (SUBSTRING(PAIEMENTX.USERZONE012,1,2) IN ('T/','T|')) THEN PAIEMENTX.USERZONE013 
				END)+2,16) + 
				PAIEMENTX.PAIEMENTXLOTID 
FROM PAIEMENTX 
WHERE 
	PAIEMENTX.PAIEMENTXLOTID = @lot_id;
  RETURN @ret_;	 
END

Est ce que, selon vous, l'utilisation de la fonction SUBSTRING() dans ma requete peut être à l'origine des problemes de perf que je rencontre ( car 7 s pour un select c'est un peu long je trouve ) .
Le probleme est peut etre ailleurs je ne suis pas expert apres tout.

Merci d'avance pour votre avis et éventuels conseils :)

Bonne journée, :D

Le problème de performance vient en général de l'accès aux données bien avant leur manipulation.

Avez-vous un index / une partition sur la colonne PAIEMENTXLOTID ?

Sinon, vos CASE peuvent être légèrement simplifiés, mais je doute que ça améliore significativement les performances :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
with cte_base (USERZONE) as
(
select case
         when substring(USERZONE011 from 1 for 2) not in ('T/','T|') then USERZONE011
         when substring(USERZONE012 from 1 for 2) not in ('T/','T|') then USERZONE012
                                                                     else USERZONE013
       end as USERZONE
  from PAIEMENTX
 where PAIEMENTXLOTID = @lot_id
)
select @ret_ = substring(USERZONE from charindex('||', USERZONE) + 2 for 16) + @lot_id
  from cte_base;

05/12/2019, 11h12
P3dr075

Bonjour Waldar,

Merci pour votre reponse.

Citation:

Envoyé par Waldar

Avez-vous un index / une partition sur la colonne PAIEMENTXLOTID ?

Oui j'ai bien un index sur la colonne PAIEMENTXLOTID. Je vais tester votre proposition d'optimisation et je vous dirai.

Question subsidiaire si je peux : Est ce qu'il est judicieux de suivre les recommandations de SQL Server qui me préconise de faire de créer un index et de faire un INCLUDE ( nom de colonne, ... ).
A quoi sert cet argument INCLUDE ? J'ai lu sur le sujet mais je ne suis pas sur d'avoir bien compris

merci bcp!
05/12/2019, 12h53
Waldar

Oui ici ce serait très bien d'avoir l'index sur PAIEMENTXLOTID include (USERZONE011, USERZONE012, USERZONE013).

Ça permet à SQL-Server de ne pas avoir besoin de lire la table puisque toutes les informations nécessaires sont présentes dans l'index.

Bonjour,

Merci pour vos reponses sur le INCLUDE. J'ai modifié l'index en conséquence:

La requete

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
with cte_base (USERZONE) as
(
select case
         when substring(USERZONE011 from 1 for 2) not in ('T/','T|') then USERZONE011
         when substring(USERZONE012 from 1 for 2) not in ('T/','T|') then USERZONE012
                                                                     else USERZONE013
       end as USERZONE
  from PAIEMENTX
 where PAIEMENTXLOTID = @lot_id
)
select @ret_ = substring(USERZONE from charindex('||', USERZONE) + 2 for 16) + @lot_id
  from cte_base;

me retourne le message

Code:

1
2
3
4
5
 
Msg*156, Niveau*15, État*1, Ligne*4
Incorrect syntax near the keyword 'for'.
Msg*156, Niveau*15, État*1, Ligne*11
Incorrect syntax near the keyword 'for'.

Je bosse sur un serveur SQL Server 2014 ca peut expliquer?

merci

05/12/2019, 14h37
Waldar

Je n'ai pas vérifié si SQL-Server supportait la syntaxe normative de SUBSTRING.
Tant pis remplacez les from et for par des virgules (comme vous aviez fait).
05/12/2019, 15h28
P3dr075
C'est corrigé je pense que ma version ne supporte pas cette syntaxe;

J'ai ce message lorsque je créé ma fonction
Code:

1 2 3 Msg 8116, Niveau 16, État 1, Procédure TOTAL_SDD_PMTINFID, Ligne 8 [Ligne de départ du lot 21] Argument data type int is invalid for argument 1 of substring function.
j'ai tenté un CAST() sur le champs "USERZONE" mais sans succès. Une idée?

Je continue de regarder

merci
05/12/2019, 16h29
Waldar

Étrange, pouvez-vous fournir le DDL de la table ?
05/12/2019, 16h41
P3dr075

ca ne va pas être possible désolé...:( du moins pas sur le site. PAIEMENTX est une vue dont les champs USERZONEXXX et PAIEMENTXLOTID sont de type nvarchar si cela peut aider

Merci
05/12/2019, 17h20
Waldar

Oui ça suffit comme info. Vous n'avez pas simplement un problème de virgule mal placée ?
Le message d'erreur dit que l'argument 2 ou 3 d'une des trois fonctions substring n'est pas un entier.
05/12/2019, 17h54
aieeeuuuuu

bonjour,

comme Waldar, je pense que l'erreur est ailleurs, pouvez-vous reposter la nouvelle requête complète.
Et toujours comme Waldar, je pense que le problème de performance se situe plus dans l’accès aux données, et pas dans les fonctions substring ou autre surtout sur 40000 lignes (au fait, c'est le nombre de lignes dans la table ou le nombre de lignes renvoyées par la requete ?)

Toutefois, vous pouvez remplacer SUBSTRING(PAIEMENTX.USERZONE011,1,2) NOT IN ('T/','T|') par PAIEMENTX.USERZONE011 NOT LIKE 'T[/|]%'.

Mais je doute quand ça change beaucoup le temps d’exécution.

Avez-vous vérifié le plan d'exécution ? est-ce que l'index est utilisé ?

avez-vous testé le SELECT en dehors de la fonction ?

Attention, l'utilisation de fonction multi-instruction comme la votre empêche le parallélisme...

Bonjour aieeeuuuuu,

je pense avoir trouvé ou etait mon erreur. Waldar avait raison je confirme.
Le probleme etait dans:
Code:

1 2 select @ret_ = substring(USERZONE from charindex('||', USERZONE) + 2 for 16) + @lot_id
apres modif. La requete complète retourne bien un resultat. Par contre dans les résultats j'ai "6" au lieu de par exemple "ABCDEFGH...."
Code:

1 2 3 4 5 6 7 8 9 10 11 12 with cte_base (USERZONE) AS ( select case when substring(PAIEMENTX.USERZONE011,1,2) not in ('T/','T|') then PAIEMENTX.USERZONE011 when substring(PAIEMENTX.USERZONE012,1,2) not in ('T/','T|') then PAIEMENTX.USERZONE012 else PAIEMENTX.USERZONE013 end as USERZONE from PAIEMENTX ) select substring(cast(charindex('||', USERZONE) as nvarchar),2,16) from cte_base;
Citation:

Avez-vous vérifié le plan d'exécution ? est-ce que l'index est utilisé ?

oui, je confirme que l'index est utilisé.

Citation:

Attention, l'utilisation de fonction multi-instruction comme la votre empêche le parallélisme...

et si j'ajoutais l'option :

Code:

OPTION (QUERYTRACEON 8649)

ca forcerait le parallélisme non? Ca ne fait peut etre pas parti des bonnes pratiques je pense.

Merci pour votre aide ! :)

Comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
with cte_base (USERZONE) 
AS (
select case
         when substring(USERZONE011,1,2) not in ('T/','T|') then USERZONE011
         when substring(USERZONE012,1,2) not in ('T/','T|') then USERZONE012                
		 else USERZONE013
       end as USERZONE
  from PAIEMENTX
)
select substring(USERZONE, charindex('||', USERZONE)  + 2, 16)
  from cte_base;

06/12/2019, 10h08
aieeeuuuuu

Citation:

Envoyé par P3dr075

oui, je confirme que l'index est utilisé.

Avec quelle méthode ? est-ce qu'il n'y a pas un accés table derrière ?
Quel est le nombre de lignes estimé, et le nombre réel après la recherche (ou l'analyse) d'index ?

Citation:

Envoyé par P3dr075

et si j'ajoutais l'option :

Code:

OPTION (QUERYTRACEON 8649)

ca forcerait le parallélisme non? Ca ne fait peut etre pas parti des bonnes pratiques je pense.

Non, ça ne force le parallélisme que si celui-ci est possible (typiquement, dans le cas où le moteur estime que le cout de la requête est trop faible pour chercher un plan parallélisé), mais pas dans votre cas, puisque l'utilisation de fonction rend impossible la génération d'un plan parallélisé.
09/12/2019, 16h08
SQLpro

Remplacer

Code:

NOT LIKE 'T[/|]%'

par

Code:

LIKE 'T[^/|]%'

A +
16/12/2019, 10h04
P3dr075

Hello tout le monde,

J'ai finalement reussi à résoudre mon probleme. Un grand merci à tous pour votre aide et conseils

Bonne semaine à tous!