J'ai un système de marquage semblable à StackoverFlow pour une base de données que je travaille. Et j'écris une procédure stockée qui recherche des résultats basés sur un nombre indéterminé de balises dans une clause où. Il pourrait y avoir n'importe où entre 0 et 10 balises pour filtrer les résultats. Ainsi, par exemple, l'utilisateur pourrait rechercher des articles étiquetés avec 'Apple', 'Orange' et 'Banana' et Chaque résultat doit inclure les 3 étiquettes. Ma requête est rendue encore plus compliquée parce que je traite également d'une table de référence croisée pour le marquage, mais que je ne vais pas entrer dans cela. Je sais que je peux faire de la manipulation de chaîne et de nourrir L'EXEC () Fonctionnez une requête pour prendre en charge cela, mais je préfère ne pas être des problèmes de performance associés à SQL dynamique. Je pense qu'il est préférable que SQL cache un plan de requête pour la proc. Quelles sont les techniques que vous avez utilisées pour éviter Dynamic SQL dans ce type de scénario? par la demande populaire , voici la requête avec laquelle je travaille avec: Ceci est fonctionnel mais codé dur. Vous verrez que je l'ai défini pour rechercher les balises «couleur» et «saturation».
8 Réponses :
Cordez les étiquettes avec une virgule qui les séparait «Apple», «Orange», puis transmettez-la à un paramètre qui utilise la clause dans votre procédure stockée.
Bien sûr, si vous avez les valeurs (clé) de la table de recherche pour ces balises, je les utiliserais.
EDIT:
Puisque vous avez besoin de toutes les balises dans le résultat ....
Malheureusement, je pense que peu importe ce que vous faites, le SP sera en danger du plan régénéré.
Vous pouvez utiliser des paramètres facultatifs et utiliser le boîtier et ISNULL pour accumuler les arguments.
Je pense toujours que cela signifie que votre SP a perdu la majeure partie de sa bonté en cache, mais c'est mieux que la «chaîne» directe, je crois.
J'ai pensé à ça. Malheureusement, cela ne fonctionnera pas dans mon cas car dans des actes comme une ou. Et j'ai besoin de la fonctionnalité de et.
Ah, vous avez besoin de toutes les tags à trouver non seulement d'entre eux ... Désolé, vous avez mentionné cela dans votre message.
Aucun problème. C'est une bonne idée cependant. J'ai réaffiche un peu la question à inclure cette exigence.
Vous ne pouvez pas utiliser de paramètre dans une clause de cette façon de toute façon. Vous pouvez seulement dire "dans (@ param1, @ param2, @ param3)", vous ne pouvez pas mettre les paramètres dans une liste séparée par des virgules et faire "dans @params".
@Todd Owen - Bon point. Bien que cela ne soit pas important depuis les actes comme un ou; D'où mon édition.
Pour une vue d'ensemble approfondie concernant ce problème et des problèmes similaires, voir: http: //www.sommarskog .se / DYN-Search-2005.html
spécifique à votre question est la partie ici: http: //www.sommarskog .se / DYN-Search-2005.html # and_isnotnull
Prenez également en compte qu'une solution dynamique (droite) n'est pas nécessairement plus lente qu'une solution statique (éventuellement compliquée), car les plans de requête peuvent toujours être mis en cache: voir http:/dyn-search-2005.htm#dynsql
vous devrez donc tester / mesurer soigneusement vos options contre des quantités réalistes de données, en tenant compte des requêtes réalistes (par exemple, des recherches avec un ou deux paramètres peuvent être bien plus courantes que les recherches avec dix, etc.)
Edit: Le questionneur a donné une bonne raison d'optimiser cela dans les commentaires, en déplaçant donc l'avertissement «prématuré» un peu hors de la manière suivante:
Le mot (standard;) Word of Warton s'applique, cependant: Cela sent beaucoup comme une optimisation prématurée! - Êtes-vous sûr que cette SPROC sera appelée souvent que l'utilisation de SQL dynamique sera significativement plus lent (c'est-à-dire comparé à d'autres choses qui se passent dans votre application)?
+1 Yep, cela semble fonctionner et avoir de bonnes performances, bien que cela signifie que le nombre maximum de balises (10 dans ce cas) est codé dur.
Merci pour la suggestion. Je vais examiner cela en profondeur demain ainsi que les autres réponses ici pour être sûr. Quant à la pensée d'optimisation prématurée - cette requête sera absolument essentielle au fonctionnement du site. J'engage même cette requête via Ajax sur une base de type AS-You. Il est donc important d'extraire le plus de performances possible. Je prévois de tester plusieurs techniques pour déterminer lequel est le plus rapide.
Ceci peut ne pas être la méthode la plus rapide, mais pourriez-vous simplement générer une chaîne de requête pour chaque étiquette, puis rejoindre-les avec "Intersect"?
Edit: N'a pas vu l'étiquette SproC donc je ne sais pas si cela serait possible.
J'ai vu deux types de solutions à ce problème:
Le premier est de rejoindre le tableau code> Shader CODE> Table sur La deuxième solution consiste à joindre à cette balise une fois, limiter les étiquettes aux trois dont vous avez besoin , puis Tags CODE> (via XRef selon les besoins) une fois pour chaque étiquette que vous recherchez. Le résultat de la join interne ne comprend que des shaders qui ont une correspondance pour toutes les balises. P> groupe par code> le shader_id code> afin que vous puissiez compter les correspondances. Le nombre ne sera que trois seulement si toutes les balises ont été trouvées (en supposant une unicité dans la table XREF). P> SELECT s.shader_id
FROM shader s
JOIN tag_shader_xref x ON (s.shader_id = x.shader_id)
JOIN tag t ON (t.tag_id = x.tag_id
AND t.tag_name IN ('color', 'saturation', 'transparency'))
GROUP BY s.shader_id
HAVING COUNT(DISTINCT t.tag_name) = 3;
Le deuxième exemple pourrait fournir de faux positifs - un enregistrement de shader pourrait avoir 2+ valeurs de nom de nom de 'couleur' / etc.
Et maintenant? cf. Count (Nom_Tag_Name distinct)
Le prochain problème est que vous devez utiliser SQL dynamique afin de spécifier la liste des possibilités de votre clause dans votre clause. Une variable de chaîne / varchar contenant une liste délimitée par des virgules ne sera pas acceptée. Même si cela ne verra pas la variable pour saisir qu'il devrait s'agir d'une liste séparée par des virgules.
Vous pouvez toujours utiliser des paramètres de requête, mais vous avez besoin de nombreux espaces réservés de paramètres que vous avez des éléments de votre tableau: " tag.name dans (?,?,?) " Voir:
Oui, mais vous devez coder les paramètres. Selon les contraintes, vous ne pourrez peut-être pas utiliser NULL dans ces cas. Le premier exemple ne soulève pas ces préoccupations.
Vous seriez littéralement jamais devez utiliser NULL pour cette requête. "Je veux m'assurer que le shader a les trois propriétés suivantes:" couleur "," saturation "et, um ..."
Tag_Name dans (?,?) == Tag_Name Dans (@ param1, @ param2), etc. Par conséquent, ils sont codés durs par opposition à la construction dynamique de la clause in dans une seule variable (qui ne sera pas acceptée dans quoi que ce soit que SQL dynamique ). Pour l'OP, les paramètres sont facultatifs - les valeurs de paramètres paramètres seront en défaut à NULL jusqu'à ce que défini.
Vous devez utiliser de toute façon dynamique SQL. Et si seulement deux balises possibles sur cinq sont non nulles, vous mettez seulement deux placements de paramètres dans l'expression. Ne mettez pas plus d'espaces réservés, si vous savez déjà que les valeurs de paramètre seraient nuls.
Votre requête est parfaite pour utiliser une expression de table commune (CTE) en raison de la sous-requête corrélée en double dans les clauses existantes: à l'aide de la CTE, j'ai également converti l'existence en jointures . P> Parler à votre question initiale concernant l'utilisation de Dynamic SQL - la seule alternative consiste à vérifier le paramètre entrant pour obtenir des critères d'évacuation avant de l'appliquer. IE: P> WHERE (@param1 IS NULL OR a1.tag_name = @param1)
WOW ... Je ne savais pas à propos de CTE ou que SP_Executeql cachees plans de requête. J'apprends quelque chose de nouveau tous les jours. En passant, j'ai terminé dynamique SQL dans le passé avec la fonction Exec () (qui ne cache pas les plans de requête) et c'est pourquoi j'essaie de rester à l'abri. Mais c'est bon de savoir sur sp_executesql ... bonnes choses.
J'ai évoqué la réponse de Henrik, mais une autre alternative à laquelle je peux penser consiste à obtenir les étiquettes de recherche dans une variable de table ou de table temporaire, puis de la joindre à une connexion ou d'utilisation d'une clause dans une sous-sélection. Puisque vous voulez des résultats avec tous les balises recherchées, vous voudrez d'abord compter le nombre de balises de requête, puis trouver des résultats dans lesquels le nombre de balises appariées est égal à ce nombre. Comment mettre le valeurs dans une table? Si les balises sont transmises à votre procédure stockée, et si vous utilisez SQL Server 2008, vous pouvez utiliser la fonctionnalité de paramètres de la table de la nouvelle table et passer une variable de table directement à votre procédure stockée. Sinon, si vous recevez les balises dans une seule chaîne, vous pouvez utiliser une fonction stockée qui renvoie une table, telle que le Fonction SplitsTring présentée ici . Vous pouvez faire quelque chose comme:
Comment puis-je éviter Dynamic SQL lors de l'utilisation d'un Nombre indéterminé de paramètres?
Vous pouvez générer de manière dynamique les modèles de modèles SQL paramétrés (préparés) appropriés à la place.
Créez et préparez le modèle de relevé lorsque les paramètres se présentent pour la première fois pour la première fois, la mise en cache des énoncés préparés pour la réutilisation lorsque le même nombre de paramètres apparaît à nouveau.
Cela pourrait être fait dans l'application ou une procédure stockée suffisamment sophistiquée.
Je préfère de loin cette approche à dire, par exemple, une procédure qui prend au plus 10 étiquettes et a une logique grase pour faire face à l'un d'entre eux étant NULL.
Bill Karwin's Group par Réponse Dans cette question est probablement le modèle le plus facile à construire - vous concatéez simplement des espaces réservés pour ledans prédicat et met à jour le < Code> Nombre Clause. D'autres solutions impliquant des jointures par étiquette nécessiteraient d'incrémenter des alias de table (par exemple,xref1 ,xref2 , etc.) comme vous allez.
C'est donc plus facile que prévu. Après avoir implémenté une requête assez simple pour prendre en charge cela, j'ai instantanément une meilleure performance que je ne le pensais. Donc, je ne suis pas sûr qu'il soit nécessaire de mettre en œuvre et de tester les autres solutions.
J'ai actuellement ma base de données remplie d'environ 200 shaders et 500 étiquettes. J'ai couru ce que je pense, c'est un test un peu réaliste où j'ai effectué 35 requêtes de recherche différentes contre mon procédé stocké avec un nombre varié de tags, avec et sans terme de recherche. Je mets tout cela dans une seule instruction SQL, puis j'ai comparé les résultats dans ASP.NET. Il a constamment dirigé ces 35 recherches en moins de 200 millisecondes. Si je l'ai réduit à seulement 5 recherches, le temps passe à 10 ms. Ce genre de performance est génial. Cela aide à ce que ma taille de base de données est petite. Mais je pense que cela aide également que la requête utilise bien les indices. P>
Une chose que j'ai changée dans ma requête était la façon dont je cherchais des tags. Je recherche maintenant les balises par leur identifiant au lieu du nom. En faisant cela, je peux vous éloigner avec 1 moindre rejoindre et bénéficier d'un index pour la recherche. Et puis j'ai aussi ajouté "DBO". à l'avant des noms de table après avoir appris que SQL Caches requête sur une base par utilisateur. P>
Si quiconque est intéressé, voici mon procrété stocké fini: p>
ALTER PROCEDURE [dbo].[search]
@search_term varchar(100) = NULL,
@tag1 int = NULL,
@tag2 int = NULL,
@tag3 int = NULL,
@tag4 int = NULL,
@tag5 int = NULL,
@tag6 int = NULL,
@tag7 int = NULL,
@tag8 int = NULL,
@tag9 int = NULL,
@tag10 int = NULL
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@search_term) > 0
BEGIN
SELECT s.shader_id, s.page_name, s.name, s.description, s.download_count, s.rating, s.price FROM dbo.shader s
INNER JOIN FREETEXTTABLE(dbo.shader, *, @search_term) AS ft ON s.shader_id = ft.[KEY]
WHERE (@tag1 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag1))
AND (@tag2 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag2))
AND (@tag3 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag3))
AND (@tag4 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag4))
AND (@tag5 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag5))
AND (@tag6 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag6))
AND (@tag7 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag7))
AND (@tag8 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag8))
AND (@tag9 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag9))
AND (@tag10 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag10))
ORDER BY ft.[RANK] DESC
END
ELSE
BEGIN
SELECT s.shader_id, s.page_name, s.name, s.description, s.download_count, s.rating, s.price FROM dbo.shader s
WHERE (@tag1 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag1))
AND (@tag2 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag2))
AND (@tag3 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag3))
AND (@tag4 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag4))
AND (@tag5 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag5))
AND (@tag6 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag6))
AND (@tag7 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag7))
AND (@tag8 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag8))
AND (@tag9 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag9))
AND (@tag10 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag10))
END
END
Pouvez-vous poster votre Sprat ou une approximation de celui-ci? J'imagine que pour ce scénario, il n'y a pas de réponse générique. La bonne réponse dépendra probablement des spécificités de votre requête.
@Luke, je viens de poster la requête.