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

Question

我有一个类似 StackOverflow 的标记系统，适用于我正在开发的数据库。我正在编写一个存储过程，该过程根据 WHERE 子句中不确定数量的标签查找结果。可以有 0 到 10 个标签来过滤结果。例如，用户可能正在搜索标记有“苹果”、“橙色”和“香蕉”的项目，并且每个结果必须包含所有 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

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

---

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

不过，（标准；）警告词适用：这闻起来很像过早优化！ - 您确定这个存储过程会经常被调用吗？使用动态 SQL 会显着变慢（也就是说，与应用程序中发生的其他事情相比） ？

Answer 2

所以这比我预期的要容易。在实现一个相当简单的查询来解决这个问题后，我立即获得了比我想象的更好的性能。所以我不确定是否有必要实施和测试其他解决方案。

目前，我的数据库包含大约 200 个着色器和 500 个标签。我运行了一个我认为有点现实的测试，其中我使用不同数量的标签（带或不带搜索词）对我的存储过程执行了 35 个不同的搜索查询。我将所有这些都放在一个 SQL 语句中，然后在 ASP.NET 中对结果进行基准测试。它始终在 200 毫秒内运行这 35 次搜索。如果我将其减少到仅 5 次搜索，那么时间就会减少到 10 毫秒。这样的表演真是太棒了。我的数据库规模较小，这很有帮助。但我认为查询充分利用索引也有帮助。

我在查询中改变的一件事是查找标签的方式。我现在通过 ID 而不是名称来查找标签。通过这样做，我可以少做 1 个连接，并获得使用索引进行搜索的好处。然后我还添加了“dbo”。在了解 SQL 会按用户缓存查询后，将其添加到表名称的前面。

如果有人感兴趣，这是我完成的存储过程：

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

尽管我没有用尽所有选项，但这仍然是一个很好的练习，因为我已经向自己证明我的数据库设计非常适合这项任务。我也从发布这个问题中学到了很多东西。我知道 exec() 很糟糕，因为它不缓存查询计划。但我不知道 sp_executesql 缓存查询计划，这非常酷。我也不知道通用表表达式。 Henrik Opel 发布的链接充满了针对此类任务的好提示。

当然，如果数据库急剧增长，我仍然可能会在一年后重新审视这个问题。在此之前，感谢大家的帮助。

更新：

所以我在网上有一个该搜索引擎的工作示例 http://www.silverlightxap.com/controls< /a> 如果有人有兴趣看到这个实际情况。

Answer 3

您的查询非常适合使用公共表表达式 (CTE)，因为 EXISTS 子句中存在重复的相关子查询：

WITH attribute AS(
  SELECT tsx.shader_id,
         t.tag_name
    FROM TAG_SHADER_XREF tsx ON tsx.shader_id = s.shader_id
    JOIN TAG t ON t.tad_id = tsx.tag_id)
SELECT ft.[RANK], 
       s.shader_id, 
       s.page_name, 
       s.name, 
       s.description, 
       s.download_count, 
       s.rating, 
       s.price 
  FROM SHADER s 
  JOIN FREETEXTTABLE(SHADER, *, @search_term) AS ft ON s.shader_id = ft.[KEY]
  JOIN attribute a1 ON a1.shader_id = s.shader_id AND a1.tag_name = 'color'
  JOIN attribute a2 ON a2.shader_id = s.shader_id AND a2.tag_name = 'saturation'
 ORDER BY ft.[RANK] DESC

通过使用 CTE，我还将 EXISTS 转换为 JOIN。

谈到您关于使用动态 SQL 的原始问题 - 唯一的选择是在应用传入参数之前检查转义条件。 IE：

WHERE (@param1 IS NULL OR a1.tag_name = @param1)

如果@param1包含NULL值，则后面括号中的SQL部分不会被执行。我更喜欢动态 SQL 方法，否则您将创建可能不会被使用的 JOIN 等 - 这是对资源的浪费。

您认为动态 SQL 存在哪些性能问题？使用 sp_executesql 会缓存查询计划。坦率地说，我发现奇怪的是，如果查询经过语法/等验证（使用 exec 或 sp_executesql ），则查询计划不会被缓存 - 验证将在查询计划，为什么后面的步骤被跳过？

Answer 4

使用时如何避免动态 SQL
参数数量未定？

您可以动态生成适当的参数化（准备好的）SQL 模板。

当参数第一次出现时构建并准备语句模板，缓存准备好的语句以便在再次出现相同数量的参数时重复使用。

这可以在应用程序或足够复杂的存储过程中完成。

我更喜欢这种方法，比方说，一个最多需要 10 个标签并具有处理它们中任何一个 NULL 的逻辑的过程。

比尔·卡尔文这个问题中的 GROUP BY 答案 可能是最容易构建的模板 - 您只需连接 IN 谓词的占位符并更新 COUNT 子句。其他涉及 joins-per-tag 的解决方案需要不断增加表别名（例如，xref1、xref2 等）。

Answer 5

我已经看到了解决此问题的两种类型：

第一种是将 shader 表连接到 tags（根据需要通过外部参照），为您的每个标签加入一次正在寻找。内连接的结果仅包括与所有标签匹配的着色器。

SELECT s.*
FROM shader s
JOIN tag_shader_xref x1 ON (s.shader_id = x1.shader_id)
JOIN tag t1 ON (t1.tag_id = x1.tag_id AND t1.tag_name = 'color')
JOIN tag_shader_xref x2 ON (s.shader_id = x2.shader_id)
JOIN tag t2 ON (t2.tag_id = x2.tag_id AND t2.tag_name = 'saturation')
JOIN tag_shader_xref x3 ON (s.shader_id = x3.shader_id)
JOIN tag t3 ON (t3.tag_id = x3.tag_id AND t3.tag_name = 'transparency');

第二种解决方案是加入该标签一次，将标签限制为您需要的三个，然后对 shader_id 进行 GROUP BY ，以便您可以对匹配项进行计数。仅当找到所有标签时，计数才会为 3（假设外部参照表中的唯一性）。

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;

你应该使用哪个？取决于您的数据库品牌对一种方法或另一种方法的优化程度。我通常使用MySQL，它与GROUP BY的效果不太一样，所以最好使用前一种方法。在 Microsoft SQL Server 中，后一种解决方案可能会做得更好。

Answer 6

这可能不是最快的方法，但是您可以为每个标签生成一个查询字符串，然后用“INTERSECT”将它们连接起来吗？

编辑：没有看到存储过程标签，所以我不知道这是否可行。

Answer 7

我赞成 Henrik 的答案，但我能想到的另一种选择是将搜索标签放入临时表或表变量中，然后对其进行 JOIN 或使用带有子 SELECT 的 IN 子句。由于您想要包含所有搜索标签的结果，因此您需要首先计算查询标签的数量，然后查找匹配标签数量等于该数量的结果。

如何将值放入表中？如果标签被传递到您的存储过程，并且您使用的是 SQL Server 2008，那么您可以使用新的表值参数功能并将表变量直接传递到您的存储过程。

否则，如果您收到单个字符串中的标签，则可以使用返回表的存储函数，例如 此处显示的 SplitString 函数。你可以这样做：

... WHERE @SearchTagCount = (SELECT COUNT(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 IN (SELECT * FROM dbo.SplitString(@SearchTags,',')))

Answer 8

将标记串在一起，并用逗号分隔它们“apple”、“orange”，然后将其传递给在存储过程中使用 IN 子句的一个参数。

当然，如果您有这些标签的查找表中的值（键），我会使用它们。

编辑：

由于您需要结果中的所有标签...

不幸的是，我认为无论您做什么，SP 都将面临重新生成计划的危险。

您可以使用可选参数并使用 CASE 和 ISNULL 来构建参数。

我仍然认为这意味着你的 SP 已经失去了大部分缓存的优点，但我相信它比直接执行“字符串”要好。