参数的 SQL Server 性能问题 [英] SQL Server performance issue with parameters

问题描述

SQL 查询存在性能问题.这是我的查询

There is a performance issue with a SQL query. This is my query

declare @siteId int,    
@totalCount int

 SELECT @totalCount = COUNT(DISTINCT pro.product_id)
    FROM product AS pro 
    INNER JOIN product_to_category AS proCat ON pro.product_id = proCat.product_id
    INNER JOIN product_to_vendor proVen ON  pro.product_id = proVen.product_id
WHERE pro.site_id = @siteId 
    AND pro.product_type <> 3
print @totalCount

执行需要6秒..

当我删除参数如下

@totalCount int

SELECT @totalCount = COUNT(DISTINCT pro.product_id)
FROM product AS pro 
INNER JOIN product_to_category AS proCat ON pro.product_id = proCat.product_id
INNER JOIN product_to_vendor proVen ON  pro.product_id = proVen.product_id
WHERE pro.site_id = 28
AND pro.product_type <> 3
print @totalCount

再次执行查询，只需要2秒.

and execute query again, it takes only 2 seconds.

有没有一种方法可以提高这样的查询的性能?为什么给参数赋值需要时间?任何意见....

is there a method to improve performance of an query like this? why it takes time to assign values to parameters? any comments....

推荐答案

这不是参数，而是变量.

This is not a parameter it is a variable.

SQL Server 不会嗅探变量的值，因此它在编译时不知道它将包含的值是 28

SQL Server does not sniff the values of variables so it does not know at compile time that the value it will contain is 28

将 OPTION (RECOMPILE) 添加到查询的末尾，使其在变量值被赋值后重新编译语句，并生成更合适的执行计划.

Add OPTION (RECOMPILE) to the end of your query to get it to recompile the statement after the variable's value has been assigned and it should generate a more suitable execution plan.

这篇关于参数的 SQL Server 性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！