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

Question

我有一个正在使用的数据库的类似于StackOverflow的标记系统。我正在编写一个存储过程，该存储过程基于WHERE子句中未确定数量的标签来寻找结果。可能有0到10个标签之间的任意位置来过滤结果。因此，例如，用户可能正在搜索带有“
apple”，“ orange”和“ banana”标签的商品，并且 每个
结果都必须包含所有3个标签。我的查询变得更加复杂，因为我还要处理一个用于标记的交叉引用表，但是出于这个问题的目的，我将不再赘述。

我知道我可以做一些字符串操作，并为exec（）函数提供查询以解决此问题，但我宁愿不要解决与动态SQL相关的性能问题。我认为最好是SQL为存储的proc缓存查询计划。

在这种情况下，您使用了哪些技术来避免动态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

这是功能性但硬编码的。您会看到我已设置它来查找’color’和’saturation’标签。

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的速度会 大大
降低（也就是说，与应用程序中发生的其他事情相比）？