Incompréhension sur une requête

[SoaB]

Bonjour,

voici une requête

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT 13309, 'C05' , L.[TxtValeurCle1], L.[TxtValeurCle2], L.[TxtValeurCle3] , L.[NbNumLigne]
FROM [dbo].[ETL_vLoadLigneOK] L 
WHERE L.[IdLoadFichier] = 13309 
AND L.[TxtCodeMouvement] = 'C05' 
AND ( 
		SELECT SUM ( CAST (	SUBSTRING ( L2.[TxtLigne], 148,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING( L2.[TxtLigne], 153,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING ( L2.[TxtLigne], 163,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING ( L2.[TxtLigne], 168,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING (L2.[TxtLigne], 173,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING ( L2.[TxtLigne], 178,5)	AS INTEGER ) ) 
		FROM [dbo].[ETL_vLoadLigneOK] L2 
		WHERE L2.[IdLoadFichier] = 13309
		AND	L2.[TxtCodeMouvement] = 'C05'
 
		AND L2.[TxtValeurCle1] = L.[TxtValeurCle1] ) 
> 2500

La SOUS requête plante car il n'arrive pas à convertir une donnée en INTEGER.
Ces données qu'il n'arrive pas à convertir sont issues de la table mais avec un IdLoadfichier Différent !

L'erreur est la n°245 impossible de convertir un varchar en integer

Si j'exécute la requête suivante, il n'y a aucune erreur :

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT SUM ( CAST (	SUBSTRING ( L2.[TxtLigne], 148,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING( L2.[TxtLigne], 153,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING ( L2.[TxtLigne], 163,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING ( L2.[TxtLigne], 168,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING (L2.[TxtLigne], 173,5)	AS INTEGER ) ) 
		+ SUM ( CAST (		SUBSTRING ( L2.[TxtLigne], 178,5)	AS INTEGER ) ) 
		FROM [dbo].[ETL_vLoadLigneOK] L2 
		WHERE L2.[IdLoadFichier] = 13309
		AND	L2.[TxtCodeMouvement] = 'C05'

Pourtant c'est bien la sous requête qui plante dans le cas précédent.

Ma question est donc pourquoi ce cas plante alors que ça ne devrait pas à mes yeux.

Je pourrais modifier la requête afin de l'optimiser (avec un group by + having) mais mon objectif est de comprendre comment cela se fait que SQL Server se foire dans son plan d'exécution ?

Merci

[aieeeuuuuu]

Bonjour

Auriez vous un index sur TxtValeurCle1, avec TxtLigne (eventuellement en colonne incluse), mais sans IdLoadFichier ?
(avec ou sans TxtCodeMouvement)

Dans ce cas, peut etre que le moteur estime plus rapide d'utiliser cet index et de vérifier que la somme est >2500 avant d'aller chercher la valeur de IdLoadFichier dans la table ?

Pouvez-vous poster la DDL de la table et de ses index ?

[SoaB]

Envoyé par aieeeuuuuu

Pouvez-vous poster la DDL de la table et de ses index ?

Alors il n'y a pas d'index autre que la clé primaire de la table, ainsi que la clé étrangère IdLoadFichier

les 3 champs TxtvaleurCleX ne sont pas indexés car les données sont génériques et facultatives.

[elsuket]

Bonjour,

Je pourrais modifier la requête afin de l'optimiser (avec un group by + having) mais mon objectif est de comprendre comment cela se fait que SQL Server se foire dans son plan d'exécution ?

Non, SQL Server fait les transtypages pour vous parce que vous n'utilisez pas les types de données adaptés à vos besoin.
Vous pouvez utiliser SSIS pour transformer vos données et les stocker dans une table avec les types de données correspondants.
Sinon vous pouvez écrire des fonctions qui font les SUBSTRING() à votre place et créer des colonnes calculées.
En les indexant en plus (sans pour autant les persister), vous devriez avoir des performances correctes.

Quelle est la valeur que vous retourne la sous-requête lorsque vous l'exécutez seule ?

@++

[SoaB]

Alors la valeur retournée par la sous requête seule n'a pas de sens en soit car je suis obligé de virer le filtre sur TxtValeurCle1.

Mais elle renvoie au final le bon chiffre "total" de ce que je recherche.

par contre une fois remis dans la sous requête ca plante car il n'arrive pas à convertir "030.4" en integer.

cette valeur "030.4" provient d'une autre ligne de la table mais avec un Idloadfichier différent ...

[elsuket]

Étant donné vous extrayez les des valeurs entières d'une chaîne, il est difficile de s'assurer qu'il n'y a pas un caractère bizarre qui traîne et empêche le transtypage.
Testez vos SUBSTRING() un à un en filtrant sur l'IdLoadFichier 13309, je suis absolument certain que vous aurez un problème pour l'un d'entre eux.

@++

[SoaB]

C'est la 1ère chose que j'ai faite, j'arrive à convertir l'ensemble des lignes pour l'idloadfichier 13309 en integer sans aucun souci ...

mais c'est bien en sous requête que SQL Server se vautre lamentablement, en me renvoyant des erreurs sur des lignes d'idloadfichier différent.

Avant d'arriver sur le forum avec une telle question, j'ai effectué des tests poussés et au maximum complet

[elsuket]

OK, désolé. Si tout le monde faisait comme vous, ça serait pas mal

Sans le jeu de données, j'ai un peu de mal à comprendre, et je me doute que vous ne pouvez pas l'exposer sur le forum ...

@++

[SoaB]

Le 1er souci de sortir le jeu de données c'est qu'on parle de plusieurs centaines de milliers de lignes par IdLoadfichier.

Ensuite les données en elle même je devrais cacher certains morceaux effectivement.

Mais de toute façon c'est très simple je vais essayer de mettre un exemple simplifié


IdLoadFichier|TxtLigne|TxtValeurCle1
13309|2009X1XYY00320|X1X
13309|2009X1XYY00240|X1X
13309|2009X1XYY00450|X1X
12491|2009X1XZZ030.4|X1X
12358|2009X2XZZ030.4|X2X



si on prend le substring pour récupérer '00320' sur IdLoadFichier avec un cast en integer puis la somme on trouvera 1010

si maintenant on essaie de récupérer les lignes en erreur qui ont leur total > 1000 en fonction des différents paramètres (TxtValeurCle1, idLoadFichier et 2009) on fera la requête que j'ai donné plus haut avec un select des lignes avec une sous requête pour voir si le total est > 1000

et la ca plante sur les lignes qui ont '030.4' malgré l'idloadfichier saisi en dur.

[elsuket]

Je viens de me créer la table suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @t TABLE
(
	IdLoadFichier int
	, TxtLigne varchar(255)
	, TxtValeurCle1 char(3)
	, TxtCodeMouvement char(3)
)
 
INSERT	@t VALUES (13309, '2009X1XYY00320', 'X1X', 'C05')
	, (13309, '2009X1XYY00240', 'X1X', 'C05')
	, (13309, '2009X1XYY00450', 'X1X', 'C05')
	, (12491, '2009X1XZZ030.4', 'X1X', 'C05')
	, (12358, '2009X2XZZ030.4', 'X2X', 'C05')

J'espère que c'est représentatif de ce que vous avez en base.

Puis j'ai exécuté la requête suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 13309, 'C05' , L.TxtValeurCle1
FROM @t L 
WHERE L.IdLoadFichier = 13309 
AND L.TxtCodeMouvement = 'C05' 
AND ( 
		SELECT SUM(CAST(RIGHT(L2.TxtLigne,5) AS int)) 
		+ SUM (CAST(RIGHT(L2.TxtLigne,5) AS int)) 
		+ SUM (CAST(RIGHT(L2.TxtLigne,5) AS int)) 
		+ SUM (CAST(RIGHT(L2.TxtLigne,5) AS int)) 
		+ SUM (CAST(RIGHT(L2.TxtLigne,5) AS int)) 
		+ SUM (CAST(RIGHT(L2.TxtLigne,5) AS int)) 
		FROM @t L2 
		WHERE L2.IdLoadFichier = 13309
		AND L2.TxtCodeMouvement = 'C05' 
		AND L2.TxtValeurCle1 = L.TxtValeurCle1 
	) 
> 2500

Qui me retourne 3 lignes.

Ensuite j'ai changé la ligne (13309, '2009X1XYY00450', 'X1X', 'C05') de l'INSERT par , (13309, '2009X1XYY004.50', 'X1X', 'C05'), et j'ai obtenu l'erreur suivante :

Msg 245, Level 16, State 1, Line 21
Conversion failed when converting the varchar value '04.50' to data type int.

Qui est celle que vous obtenez.
Donc j'ai de plus en plus de mal à comprendre ce qui se passe sur votre base de données.

@++

[SoaB]

C'est surement une histoire de plan d'exécution où le serveur se foire lamentablement mais j'aimerais bien comprendre où, pourquoi et comment modifier ce plan d'exécution.

[aieeeuuuuu]

hmmm

N'auriez vous pas non plus une vue indexée, sur cette table...
... qui reprendrait les substring sans les caster par exemple ???

[elsuket]

C'est surement une histoire de plan d'exécution où le serveur se foire lamentablement mais j'aimerais bien comprendre où, pourquoi et comment modifier ce plan d'exécution.

Ni l'un, ni l'autre. Le problème est dans vos données.
La suggestion d'aieeeuuuuu est bonne à prendre aussi

@++

[aieeeuuuuu]

Bonjour,

du nouveau ?
j'aimerais bien avoir le fin mot de l'histoire

[SoaB]

Bonjour à tous,

Les vues ne sont pas indexées le même problème persiste sans les vues.

Pour le moment aucune explication à fournir. le problème est toujours la.

Une personne m' a contacté par Email, je lui ai envoyé ce qu'elle m'a demandé mais eu de retour

[elsuket]

Bonjour,

Code :

SELECT	CAST('30.4' AS int)

Pose problème, et on obtient bien l'erreur :

Conversion failed when converting the varchar value '30.4' to data type int.

En revanche :

Code :

SELECT CAST(30.4 AS int)

Fonctionne sans problème et retourne bien 30.

C'est donc bien un problème de types que nous avons.
Vous êtes donc obligé d'écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT	13309
	, 'C05'
	, L.TxtValeurCle1
	, L.TxtValeurCle2
	, L.TxtValeurCle3
	, L.NbNumLigne
FROM	dbo.ETL_tLoadLigne L 
WHERE	L.IdLoadFichier = 13309 
AND	L.TxtCodeMouvement = 'C05' 
AND	( 
		SELECT	SUM(CAST(SUBSTRING(L2.TxtLigne, 148, 5)	AS decimal(5,1)))
			+ SUM(CAST(SUBSTRING(L2.TxtLigne, 153, 5) AS decimal(5,1)))
			+ SUM(CAST(SUBSTRING(L2.TxtLigne, 163, 5) AS decimal(5,1)))
			+ SUM(CAST(SUBSTRING(L2.TxtLigne, 168, 5) AS decimal(5,1)))
			+ SUM(CAST(SUBSTRING(L2.TxtLigne, 173, 5) AS decimal(5,1)))
			+ SUM(CAST(SUBSTRING(L2.TxtLigne, 178, 5) AS decimal(5,1)))
		FROM	dbo.ETL_tLoadLigne L2 
		WHERE	L2.IdLoadFichier = 13309
		AND	L2.TxtCodeMouvement = 'C05' 
		AND L2.TxtValeurCle1 = L.TxtValeurCle1
	) > 2500

C'est-à-dire de remplacer les INTEGER de votre requête par decimal(5,1) au moins pour le voir fonctionner.

Quand à la raison pour laquelle la sous-requête fonctionne parfaitement, c'est encore une question pour moi ...

Croyez bien que j'y ai passé un moment ...

@++

[SoaB]

Merci d'avoir passé du temps, moi même je n'arrive vraiment pas à comprendre.

Le souci c'est que 30.4 est une valeur qu'il devrait pas prendre. Convertir en décimal pour convertir en integer pourrait arranger les choses mais qu'est ce qui me dit que plus tard ca ne sera pas une valeur '30A4' qui sera rencontrée ?

Bref la question reste en suspens, et SQL Server commence sérieusement à nous taper sur le système à force de constater des comportement incohérents et non maîtrisables.

[aieeeuuuuu]

Il doit bien y avoir un explication

Les vues ne sont pas indexées

Vous avez donc bien des vues sur ces tables ?

contiennent-elles des colonnes avec les "SUBSTRING" que vous utilisez dans vos requetes ?

le même problème persiste sans les vues.

sinon n'avez vous pas non plus de colonnes calculées reprenant ces substring ?

force de constater des comportement incohérents et non maîtrisables

Avez vous d'autres "mystères" comme celui-ci ?

[iberserk]

C'est surement une histoire de plan d'exécution où le serveur se foire lamentablement mais j'aimerais bien comprendre où, pourquoi et comment modifier ce plan d'exécution.

Hum... je vois bien un cas ou le plan d’exécution pourrait expliquer le phénomène (mais je peux me tromper bien sûr)...

Pourriez vous poster les deux plans d’exécutions: celui de la requête entière et celui de la sous requête seule?

[elsuket]

Bref la question reste en suspens, et SQL Server commence sérieusement à nous taper sur le système à force de constater des comportement incohérents et non maîtrisables.

Vous n'auriez jamais eu ce genre de problèmes si vous aviez un modèle de données digne de ce nom, en utilisant dans le cas de ce post un outil d'ETL comme SSIS qui est livré avec SQL Server, et avec lequel vous auriez pu nettoyer vos données.

Reconnaissez au moins que 30.4 n'est pas un entier !

@++