Recherche d'une chaine dans plusieurs colonnes

[darkgamin]

Ma recherche ne me retourne jamais de résultat quelqu'un peut m'aider?
J'utilise sql server !

Code :

1
2
3
4
5
6
7
8
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RechercheClient]  @critere varchar(50)
AS 
SELECT *
FROM dbo.CLIENTS_AVANTAGE
WHERE upper(CNOM)+upper(CRESP)+upper(CADR)+upper(CVIL) LIKE upper('%@critere%')

Merci d'avance

[7gyY9w1ZY6ySRgPeaefZ]

Votre façon de faire est dégueulasse et ne doit pas être très performante.
Vous feriez mieux de faire une clause différente par champs.
En plus, si un des champs est null, la concaténation retournera un null...
Sinon, c'est :

Code :

1
2
WHERE upper(CNOM)+upper(CRESP)+upper(CADR)+upper(CVIL) LIKE upper('%'+@critere+'%')

[zinzineti]

Essaye ceci pour voir

Code :

1
2
3
4
5
6
7
8
9
10
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RechercheClient]  @critere varchar(50)
AS 
DECLARE @varUpper VARCHAR (1024)
SET @varUpper = upper (@critere)
SELECT *
FROM dbo.CLIENTS_AVANTAGE
WHERE upper(CNOM)+upper(CRESP)+upper(CADR)+upper(CVIL) LIKE '%' + @varUpper + '%'

[darkgamin]

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

Votre façon de faire est dégueulasse et ne doit pas être très performante.
Vous feriez mieux de faire une clause différente par champs.
En plus, si un des champs est null, la concaténation retournera un null...
Sinon, c'est :

Code :

Merci pour l'encouragement Je suis débutant en sql server alors je suis ouvert au suggestion

zinzineti : Merci sa fonctionne !

[7gyY9w1ZY6ySRgPeaefZ]

Envoyé par darkgamin

Merci pour l'encouragement Je suis débutant en sql server alors je suis ouvert au suggestion

Désolé de vous ouvrir les yeux, mais les pistes sont dans la suite du message :

Vous feriez mieux de faire une clause différente par champs.
En plus, si un des champs est null, la concaténation retournera un null...

Si vous voulez plus de détails, je reste à votre disposition.

[darkgamin]

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

Désolé de vous ouvrir les yeux, mais les pistes sont dans la suite du message :
Si vous voulez plus de détails, je reste à votre disposition.

En effet j'aimerais bien améliorer ma procédure. Cependant je ne comprend pas très bien ce que vous voulez dire par "faire une clause différente par champ" Merci!

[7gyY9w1ZY6ySRgPeaefZ]

Envoyé par darkgamin

En effet j'aimerais bien améliorer ma procédure. Cependant je ne comprend pas très bien ce que vous voulez dire par "faire une clause différente par champ" Merci!

Faites comme ceci, comme ça si vous avez un champ à null, ça pourra tout de même vous ramener la ligne :

Code :

1
2
3
4
WHERE CNOM LIKE '%'+@critere+'%'
	OR CRESP LIKE '%'+@critere+'%'
	OR CADR LIKE '%'+@critere+'%'
	OR CVIL LIKE '%'+@critere+'%'

De plus, MS Sql server n'est pas case sensitive par défaut - (sauf si la collation a été modifié pour que la bd le soit), alors les upper ne sont pas vraisemblablement pas nécessaire dans votre requête.

[elsuket]

Bonjour,

Effectivement 7gyY9w1ZY6ySRgPeaefZ a raison d'enlever les UPPER si la collation des colonnes n'est pas sensible à la casse.
C'est d'ailleurs fort probablement le cas si vous avez fait une installation de SQL Server par défaut ...
Dans ce cas, la collation utilisée est celle de la base de données.
Et si vous ne l'avez pas spécifié lors de la création de la base de données, c'est celle de l'instance SQL Server

Pour le savoir, et en supposant que vous êtes sous SQL Server 2005 ou 2008 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT		S.name + '.' + T.name AS table_name
		, C.name AS column_name
		, C.collation_name
		, FHC.description
FROM		sys.COLUMNS AS C
INNER JOIN	sys.TABLES AS T
			ON T.object_id = C.object_id
INNER JOIN	sys.schemas AS S
			ON S.schema_id = T.schema_id
INNER JOIN	sys.fn_helpcollations() AS FHC
			ON C.collation_name = FHC.name
WHERE		S.name = 'dbo'
AND		T.name = 'CLIENTS_AVANTAGE'
AND		C.name IN ('CNOM', 'CRESP', 'CADR', 'CVIL')

Pour connaître la collation utilisée par votre base de données :

Code :

1
2
3
4
5
6
7
SELECT		D.name AS database_name
		, D.collation_name AS database_collation_name
		, FHC.description AS collation_description
FROM		sys.DATABASES AS D
INNER JOIN	sys.fn_helpcollations() AS FHC
			ON D.collation_name = FHC.name
--WHERE		D.name = 'leNomDeVotreBaseDeDonnees'

Et pour l'instance SQL Server :

Code :

1
2
3
4
5
SELECT		SC.server_collation_name
		, FHC.description AS server_collation_name
FROM		(SELECT	SERVERPROPERTY('Collation') AS server_collation_name) AS SC
INNER JOIN	sys.fn_helpcollations() AS FHC
			ON SC.server_collation_name = FHC.name

Vous pouvez retrouver ces requêtes dans le billet que je viens de publier (merci de m'en avoir donné l'idée )

Dans la description de la collation, vous pouvez savoir si la collation est sensible ou non à la casse.
Si elle ne l'est pas, alors l'utilisation de la fonction UPPER() est inutile et surtout contre-performante pour deux raisons :

- d'abord il faut que SQL Server mette en majuscules le mot
- comme il se base sur les statistiques de distribution des données dans les colonnes, l'utilisation d'une fonction sur une des colonnes participant à un prédicat (JOIN ou WHERE) devient non-cherchable.
C'est la raison pour laquelle 7gyY9w1ZY6ySRgPeaefZ a qualifié votre façon de faire de dégueulasse : comme SQL Server ne peut pas prédire combien de lignes dans votre table vont vérifier le prédicat, il est obligé de lire toute la table, ce qui est très contre-performant.

En changeant le prédicat de recherche par une suite de OR, c'est un peu mieux.
Cela aurait été encore mieux si votre prédicat de LIKE peut être @critere + '%', parce que dans ce cas un index sur au moins l'une des colonnes du WHERE/OR aurait accéléré la requête.
SQL Server aurait alors cherché toutes les lignes qui vérifient le prédicat sur cette colonne à travers l'index, puis aurait extrait le reste des données en lisant les pages de données référencées par les lignes trouvées dans l'index.

Pour accélérer la recherche floue, il faut penser à un index de texte intégral.
Vous pouvez l'article de SQLPro à ce sujet ici

@++

[iberserk]

Attention au FULLTEXT qui ne reproduit pas complètement le LIKE....

En combinant les deux?

[darkgamin]

Merci beaucoup sa fonctionne très bien !
Cependant, la recherche me semble être devenu plus lente.
Est-ce que cette nouvelle requête parcours 4 fois la base de donnée pour la recherche?
Est-ce que l'ancienne requête parcourais seulement 1 fois la base de donnée en additionant les champs?

Merci encore a vous pour votre aide !

[elsuket]

Il faudrait pour cela que vous nous donniez :

- la structure complète de la table (colonnes et index)
- la requête ...
- ... et son plan d'exécution réel (que l'on obtient dans une onglet distinct de la console en pressant CTRL + M avant d'exécuter la requête)

@++

[7gyY9w1ZY6ySRgPeaefZ]

Si vous aviez beaucoup de champs à Null, ça peut être une explication mais bon, là je fais Madame Soleil...
Il faudrait les informations demandées par elsuket pour être plus concret.

[darkgamin]

Voici la première requête (qui me semble plus rapide)

Code :

1
2
3
4
5
6
7
8
9
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RechercheClient]  @critere varchar(50)
AS 
SELECT *
FROM dbo.CLIENTS_AVANTAGE
WHERE (CNOM)+ ' ' + (CRESP)+ ' ' + (CADR)+ ' ' + (CVIL)+ ' ' +(CTELB)+ ' ' +(CPROV)+ ' ' +(CPAYS) LIKE('%' + @critere + '%')

voici la deuxième requête (qui me semble plus lente)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RechercheClient]  @critere varchar(50)
AS 
SELECT *
FROM dbo.CLIENTS_AVANTAGE
 WHERE CNOM LIKE '%'+@critere+'%'
	OR CRESP LIKE '%'+@critere+'%'
	OR CADR LIKE '%'+@critere+'%'
	OR CVIL LIKE '%'+@critere+'%' 
	OR CPROV LIKE '%'+@critere+'%' 
	OR CPAYS LIKE '%'+@critere+'%' 
	OR CTELB LIKE '%'+@critere+'%'

Pour la table, elle contient une trentaine de champs.
la clé primaire est CNUM et contient des valeur du genre :
0000ACR100
0000ACR105
0000AGR100
0000AGR105
...
etc.

pour le plan d'exécution réel je n'arrive pas a le trouver. J'utilise sql server 2005.

Je me demandais aussi si c'étais possible d'ajouter un deuxième critère de recherche avec un opérateur comme (ou, et , sans)

Merci encore pour l'aide ! C'est très appréciée !

[iberserk]

Que donne cette requête:

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
32
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RechercheClient]  @critere varchar(50)
AS 
SELECT *
FROM dbo.CLIENTS_AVANTAGE
 WHERE CNOM LIKE '%'+@critere+'%'
UNION 
SELECT *
FROM dbo.CLIENTS_AVANTAGE
 WHERE CRESP LIKE '%'+@critere+'%'
UNION
SELECT *
FROM dbo.CLIENTS_AVANTAGE
 WHERE CADR LIKE '%'+@critere+'%'
UNION
SELECT *
FROM dbo.CLIENTS_AVANTAGE
 WHERE CVIL LIKE '%'+@critere+'%'
UNION
SELECT *
FROM dbo.CLIENTS_AVANTAGE
WHERE CPROV LIKE '%'+@critere+'%'
UNION
SELECT *
FROM dbo.CLIENTS_AVANTAGE
WHERE CPAYS LIKE '%'+@critere+'%'
UNION
SELECT *
FROM dbo.CLIENTS_AVANTAGE
WHERE CTELB LIKE '%'+@critere+'%'

Pour le plan d’exécution, sous management studio à droite du bouton 'executer' vous devez avoir un bouton à cocher avant de lancer la requête...
Cochez le puis lancez la requête, un deuxième onglet devrait apparaître a côté du résultat de requête avec le plan d’exécution...

[elsuket]

Le problème est le LIKE de toute façon.
Donc la seule façon que je vois pour optimiser cela est de dé-normaliser en stockant les mots qui composent les critères de recherche dans une seule colonne ...

@++