使用不确定数量的参数时如何避免动态 SQL? [英] How do I avoid dynamic SQL when using an undetermined number of parameters?

问题描述

我有一个类似 StackOverflow 的标记系统，用于我正在处理的数据库.我正在编写一个存储过程，该过程根据 WHERE 子句中不确定数量的标签查找结果.可以有 0 到 10 个标签来过滤结果.因此，例如，用户可能正在搜索标有apple"、orange"和banana"的项目，并且每个结果必须包含所有 3 个标签.我的查询变得更加复杂，因为我还要处理用于标记的交叉引用表，但就本问题而言，我不会深入探讨.

I have a StackOverflow-like tagging system for a database I'm working on. And I'm writing a stored procedure that looks for results based on an undetermined number of tags in a WHERE clause. There could be anywhere between 0 and 10 tags to filter results. So for example the user could be searching for items tagged with 'apple', 'orange', and 'banana' and each result must include all 3 tags. My query is made even more complicated because I'm also dealing with a cross reference table for the tagging, but for the purposes of this question I won't go into that.

我知道我可以做一些字符串操作并向 exec() 函数提供一个查询来处理这个问题，但我不想解决与动态 SQL 相关的性能问题.我认为最好是 SQL 缓存存储过程的查询计划.

I know I can do some string manipulation and feed the exec() function a query to take care of this but I'd rather not for performance problems associated with dynamic SQL. I figure it's best if SQL caches a query plan for the stored proc.

在这种情况下，您使用了哪些技术来避免动态 SQL?

What are some techniques you've used to avoid dynamic SQL in this type of scenario?

应大众需求，这是我正在处理的查询:

By popular demand, here's the query I'm working with:

SELECT ft.[RANK], s.shader_id, s.page_name, s.name, s.description, s.download_count, s.rating, s.price FROM shader s 
INNER JOIN FREETEXTTABLE(shader, *, @search_term) AS ft ON s.shader_id = ft.[KEY]
WHERE EXISTS(SELECT tsx.shader_id FROM tag_shader_xref tsx INNER JOIN tag t ON tsx.tag_id = t.tag_id WHERE tsx.shader_id = s.shader_id AND t.tag_name = 'color')
AND EXISTS(SELECT tsx.shader_id FROM tag_shader_xref tsx INNER JOIN tag t ON tsx.tag_id = t.tag_id WHERE tsx.shader_id = s.shader_id AND t.tag_name = 'saturation')
ORDER BY ft.[RANK] DESC

这是功能性但硬编码的.您会看到我已将其设置为查找颜色"和饱和度"标签.

This is functional but hard-coded. You'll see that I have it set to look for the 'color' and 'saturation' tags.

推荐答案

有关此问题和类似问题的详细概述，请参见:http://www.sommarskog.se/dyn-search-2005.html

For an extensive overview concerning this and similar problems see: http://www.sommarskog.se/dyn-search-2005.html

具体到您的问题是这里的部​​分:http://www.sommarskog.se/dyn-search-2005.html#AND_ISNOTNULL

Specific to your question is the part here: http://www.sommarskog.se/dyn-search-2005.html#AND_ISNOTNULL

还要考虑到(直接的)动态解决方案不一定比(可能是复杂的)静态解决方案慢，因为查询计划仍然可以被缓存:参见 http://www.sommarskog.se/dyn-search-2005.html#dynsql

Also take into account that a (straight) dynamic Solution is not necessarily slower than a (possibly convoluted) static one, as query plans can still get cached: see http://www.sommarskog.se/dyn-search-2005.html#dynsql

因此，您必须根据实际数据量仔细测试/衡量您的选项，同时考虑实际查询(例如，使用一两个参数的搜索可能比使用十个参数的搜索更常见，等等)

So you'll have to carefully test/measure your options against realistic amounts of data, taking into account realistic queries (e.g. searches with one or two parameters might be way more common than searches with ten, etc.)

提问者在评论中给出了优化这一点的充分理由，因此将过早"警告移开:

Questioner gave a good reason to optimize this in the comments, hence moving the 'premature' warning a bit out of the way:

(标准;)警告词适用，但:这闻起来很像过早优化！ - 你确定这个 sproc 会经常被调用，因为使用动态 SQL 会明显慢(也就是说，与您的应用中发生的其他事情相比)?

The (standard ;) word of warning applies, though: This smells a lot like premature optimization! - Are you sure this sproc will get called that often that using dynamic SQL will be significantly slower (that is, compared to other stuff going on in your app)?

这篇关于使用不确定数量的参数时如何避免动态 SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！