Performance de la fonction SUBSTRING()

**P3dr075** · 05/12/2019, 10h40

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 :

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
 
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,

**Waldar** · 05/12/2019, 10h57

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 :

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

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;

**P3dr075** · 05/12/2019, 11h12

Bonjour Waldar,

Merci pour votre reponse.

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!

**Waldar** · 05/12/2019, 12h53

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.

**P3dr075** · 05/12/2019, 14h07

Bonjour,

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

La requete

Code :

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

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 :

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

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

**Waldar** · 05/12/2019, 14h37

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).

**P3dr075** · 05/12/2019, 15h28

C'est corrigé je pense que ma version ne supporte pas cette syntaxe;

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

Code :

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

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

**Waldar** · 05/12/2019, 16h29

Étrange, pouvez-vous fournir le DDL de la table ?

**P3dr075** · 05/12/2019, 16h41

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

**Waldar** · 05/12/2019, 17h20

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.

**aieeeuuuuu** · 05/12/2019, 17h54

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...

**P3dr075** · 05/12/2019, 18h16

Bonjour aieeeuuuuu,

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

Code :

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

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 :

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

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;

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

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

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

et si j'ajoutais l'option :

Code :

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

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 !

**Waldar** · 05/12/2019, 23h04

Comme ceci :

Code :

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

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;

**aieeeuuuuu** · 06/12/2019, 10h08

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 ?

Envoyé par P3dr075

et si j'ajoutais l'option :

Code :

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

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é.