为什么 SQL Server 在使用变量时会变慢? [英] Why SQL Server go slow when using variables?

问题描述

我有一个 sql 查询运行速度超快，大约一秒钟，当不使用变量时，例如:

I have a sql query that runs super fast, around one second, when not using variables, like:

WHERE id BETWEEN 5461094 and 5461097

但是当我有:

declare @firstId int
declare @lastId int

set @firstId = 5461094
set @lastId = 5461097

...
    WHERE id BETWEEN @firstId and @lastId

...查询运行非常缓慢，几分钟后才完成.为什么会发生?我需要使用变量.我可以做任何改进来避免这种性能问题吗?

... the query runs really slow, finishing only after some minutes. Why does it happen? I need to use variables. Can I do any improvement to avoid this performance problems?

推荐答案

这是因为当值被硬编码时，它可以查找 它对表中数据的统计数据，并找出要运行的最佳查询.查看每个查询的执行计划.使用变量时一定要扫描.

It's because when the values are hard coded, it can look up the statistics it has on the data in the table, and figure out the best query to run. Have a look at the execution plans of each of these queries. It must be scanning when your using variables.

如果范围总是很小，您可以使用索引提示来帮助解决这个问题.

if the range is always small you might be able to use an index hint to help this.

这篇关于为什么 SQL Server 在使用变量时会变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！