Discussion :

[Philace86]

Bonjour,
Je souhaite faire une procédure d'un code comme celui ci:

Code :

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

1
2
3
4
5
 
 
SELECT ID
FROM MAIN_TABLE
WHERE (ID=1 or ID= 2 or ID=4 or .....)

Je pensais faire quelque chose comme

Code :

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

1
2
3
4
5
6
7
8
 
CREATE PROCEDURE Get_myID
          @List_ID ntext,
AS
 
SELECT ID
FROM MAIN_TABLE
WHERE (@List_ID)

Cependant je recois une erreur de type:
Msg*156, Niveau*15, État*1, Ligne*1
Incorrect syntax near the keyword 'WHERE'.

Vous l'aurez compris, il s'agit d'un exemple, et la condition de mon where peut être très grand.

Merci d'avance

[julien94320]

Bonjour

Essayez la syntaxe suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
DECLARE @SQL nvarchar(max) ;
SET @SQL = N'SELECT ID
                       FROM MAIN_TABLE
                       WHERE';
DECLARE @List_ID nvarchar(max) ;
SET @List_ID = N' ID = 1 OR ID = 2  OR ID= 3 ';
 
SET @SQL = @SQL + @List_ID
 
EXEC SP_EXECUTESQL @SQL;

[StringBuilder]

Sinon, votre série de OR porte toujours sur la même colonne.
Elle peut donc être avantageusement remplacée par un IN.

Et votre paramètre devient à ce moment un paramètre de type table.

Et zou

https://msdn.microsoft.com/fr-fr/lib...v=vs.110).aspx

On a donc :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TYPE dbo.ListeID AS TABLE (ID int);
go
 
CREATE PROCEDURE Get_myID (@tvpListID dbo.ListID READONLY)
AS
SELECT ID
FROM MAIN_TABLE
WHERE ID in (select id from @tvpListID);
go

[Philace86]

Ok pour cette solution mais cela m'implique de passer mes objets dans une table donc bof mais y'a pas un moyen en passant une liste ?

[elsuket]

Bonjour,

Le plus simple est probablement de passer, par exemple, un document XML et le dépouiller avec les possibilités SQLXML / XQuery offertes par SQL Server, ou vous aider de ce billet qui détaille comment utiliser une liste de valeurs CSV pour ce faire. En revanche attention, car cela explose l'estimation de cardinalité si vous avez un grand nombre de valeurs, ce qui peut résulter en un plan de requêtes sous-optimal.

Vous pouvez aussi utiliser un type TABLE pour ce faire, mais il vous faudra faire attention à la charge que prendra la base de données TempDB en termes d'allocation et désallocation. En effet, si la procédure stockée est appelée très fréquemment, le nombre d'accès aux pages de mappage (Index Allocation Map, Page Free Space et Shared Global Allocation Map) des pages de données est lourd et génère donc de la contention. Si cela vous arrive, activez donc le drapeau de trace 1118 (avec DBCC TRACEON), et créez plusieurs fichiers de données dans la base de données TempDB, tous devant avoir exactement la même taille totale, et être en nombre total pair. Le désavantage de cette approche est encore une fois l'estimation de cardinalités : si vous avez de nombreuses valeurs, vous aurez de nombreuses lignes dans une telle table; or SQL Server ne maintient pas de statistiques sur les variables de type TABLE, et estime leur nombre toujours à 1, sauf si la requête qui la référence est décorée par l'indicateur de requête OPTION (RECOMPILE) : ceci force la recompilation (systématique) de la requête, qui produit donc une estimation exacte du nombre de lignes dans une telle table. En revanche, toute compilation étant intensive en consommation de CPU, si la procédure stockée est appelée très fréquemment, la charge sur le CPU peut devenir intenable. Par ailleurs, supposons que vous deviez insérer 500 valeurs dans une telle table : il me semble que la couche .NET fait 500 INSERTs d'une valeur, au lieu d'un INSERT de 500 valeurs, ce qui s'avère long et coûteux. Au final, ceci est donc une solution pour laquelle on peut opter pour des requêtes de reporting.

Une autre solution qui fonctionne bien est de créer une table de valeurs avec un jeton de type uniqueidentifier (GUID). Vous insérez donc dans une telle table la série de valeurs avec une valeur de type uniqueidentifier que vous générez en appelant la fonction NEWID(), que vous retournez à l'application, et vous faites la jointure en filtrant par la valeur du jeton. Ladite colonne étant indexée, les performances sont correctes puisque l'estimation de cardinalité est généralement adéquate. Si vous avez une grosse charge, partitionnez la table suivant ABS(CHECKSUM(jeton)), ou créez un ensemble de tables avec la contrainte de domaine (CHECK) correspondante, et mettez une vue qui est l'UNION ALL de toutes ces tables; vous insérez la vue, et SQL Server fait le reste.

Quelqu'un a-t-il testé les options 2 et 3 ci-dessus dans SQL Server 2014 avec des tables in-memory / Hekaton ?

@++

[StringBuilder]

elsuket, en complément de votre billet, il existe aussi cette solution :

http://www.developpez.net/forums/d14...utilisant-clr/

J'attends un résultat de benchmarking de la part de SQLpro.

Pour ma part je n'ai pas remarqué de problème particulier, mais je n'ai pas non plus la volumétrie ni l'infrastructure nécessaire pour tester.

[elsuket]

La fonction CLR proposée par Adam Machanic ici fonctionne pas mal. Ensuite comme vous dites, tout dépend de la complexité et de la volumétrie.
Mon expérience est que si le nombre de valeurs passé en paramètre est faible, on peut se contenter d'utiliser SQLXML / XQuery.
Avec un plus grand nombre de valeurs, j'opte pour la table et le jeton, car la CLR introduit un trou de sécurité.
SQLCLR est bien pour les chaînes très longues, puisque SQL n'est pas conçu pour le traitement de chaînes de caractères dans leur détail.

Du coup je serai quand même tenté de faire un test de performances, mais il nous faudrait un cas réél !

@++