SQL Server使用参数会使结果变慢 [英] SQL Server slow results with parameters

问题描述

我有一个查询，它选择一些数据，并在其中传递一些参数:

I have a query which selects some data, I pass some parameters in it:

DECLARE @FromAccDocNo     INT = 1,
        @ToAccDocNo       INT = 999999999,
        @FromDate         CHAR(10) = '1900/01/01',
        @ToDate           CHAR(10) = '2999/12/30',
        @L1Code           INT = 129

SELECT ad.AccDocNo,
       ad.AccDocDate,
       add1.Row,
       add1.RowComment,
       add1.Debit,
       add1.Credit
FROM   AccDoc ad
       INNER JOIN AccDocDetail add1
            ON  add1.AccDocNo = ad.AccDocNo
       INNER JOIN Topic t
            ON  t.TopicCode = add1.TopicCode
WHERE  t.L1Code = @L1Code -- here is the difference
       AND add1.AccDocNo BETWEEN @FromAccDocNo AND @ToAccDocNo
       AND ad.EffectiveDate BETWEEN @FromDate AND @ToDate
ORDER BY
       ad.AccDocNo

首先，我将值129明确写为@L1Code(需要0.010秒)

In first, I write the value 129 explicitly as @L1Code (it takes 0.010 sec)

在第二种形式中，我将@L1Code传递给查询(需要2.500秒)

In second form I pass @L1Code into query (it takes 2.500 sec)

任何人都可以解释会发生什么情况吗?

Can anyone explain what happens?

推荐答案

请阅读规范参考:位)

Please read the canonical reference: Slow in the Application, Fast in SSMS? (specifically this bit)

一种解决方法是在查询末尾添加OPTION (RECOMPILE).

One way to fix, is to add OPTION (RECOMPILE) at the end of the query.

这篇关于SQL Server使用参数会使结果变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！