sql - 存储过程比动态查询慢

Question

所以我有这个非常简单的 SP GetData，它有两个参数，看起来像这样

SET ANSI_NULLS ON
GO
SET QOUTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetData]
    @Id int = NULL,
    @ExternalId nvarchar(500) = NULL
AS
BEGIN

    SET NOCOUNT ON;
    SELECT
        t.Id,
        t.ExternalId,
        t.Column1 -- other columns ommited for brevity
    FROM [SomeTable] t
    WHERE
        (t.Id = @Id OR @ID IS NULL) AND
        (t.ExternalId = @ExternalId OR @ExternalId IS NULL)

END

非常简单。一张表中只有一个选择语句。现在，我担心的是，如果我执行此过程，平均花费的时间是 0.505369 秒，但如果我提取该选择查询并执行它，查询平均需要 0.023923 秒。我真的很担心这一点，因为这个过程被调用得非常频繁，并且是我的应用程序中的关键过程之一，这就是为什么它如此最小化，现在 0.5 秒是可以接受的。这次该表仅包含 495 万行。 Id 列上有一个聚集索引，ExternalId 列上有一个非聚集索引。该表应该在即将到来的弱点中增加到 4500 万行，并且数据插入率将降低。在 4500 万行上，我认为上面显示的 SP 不会给出一些合理的时间。我真的不明白这里的问题是什么，或者它应该是那样的吗？据我所知，在执行 SP 后计划被缓存，下一次计划不会重新创建，所以它应该比 on the fly query 更快吗？在这种情况下，使用 Ad hoc 查询代替 SP 会更好吗？数据库是 Sql Server 2012。提前致谢

Answer 1

我的第一个建议是使用“重新编译”选项。发生的情况是查询是在存储过程第一次运行时编译的(这称为“参数嗅探”)。如果第一次运行的执行路径与最佳执行路径不同，这可能会对性能产生影响。例如，有时存储过程在超小表上进行测试，因此不会使用索引。

语法是:

SELECT
    t.Id,
    t.ExternalId,
    t.Column1 -- other columns omited for brevity
FROM [SomeTable] t
WHERE
    (t.Id = @Id OR @ID IS NULL) AND
    (t.ExternalId = @ExternalId OR @ExternalId IS NULL)
OPTION (recompile);

但是，您的查询在 where 子句中使用了 or，这使得优化器根本无法使用索引。一种选择是切换到动态 SQL，如下所示:

declare @sql nvarchar(max) = '
SELECT t.Id, t.ExternalId,
       t.Column1 -- other columns omited for brevity
FROM [SomeTable] t
WHERE 1=1 ';
set @sql = @sql + (case when @id is not null then ' and t.Id = @id'` else '' end) +
           (case when @ExternalId is not null then ' and t.externalId = @externalId' else '' end);

exec sp_executesql @sql, N'@Id int, @ExternalId int', @id = @id, @externalId = @externalId;