sql - 使用不确定数量的参数时如何避免动态 SQL？

Question

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

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

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

根据大众需求，这是我正在使用的查询:

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

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

Answer 1

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

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

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

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

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

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