SQL Server 2005 查询的性能 [英] Performance of SQL Server 2005 Query

问题描述

-------------------- 这需要 4 秒来执行(有 2000 000 行)为什么?--------------------

-------------------- this takes 4 secs to execute (with 2000 000 rows) WHY?---------------------

DECLARE @AccountId INT 
DECLARE @Max INT 
DECLARE @MailingListId INT 

SET @AccountId = 6730
SET @Max = 2000
SET @MailingListId = 82924

SELECT TOP (@Max) anp_Subscriber.Id , Name, Email 
FROM anp_Subscription WITH(NOLOCK) 
  INNER JOIN anp_Subscriber WITH(NOLOCK) 
    ON anp_Subscriber.Id = anp_Subscription.SubscriberId
WHERE [MailingListId] = @MailingListId 
  AND Name LIKE '%joe%' 
  AND [AccountID] = @AccountId

<小时>

--------------------- 这需要 <1 秒执行(2000 000 行) ---------------

---

--------------------- this takes < 1 sec to execute (with 2000 000 rows) -----------------------

SELECT TOP 2000 anp_Subscriber.Id ,Name, Email 
FROM anp_Subscription WITH(NOLOCK) 
  INNER JOIN anp_Subscriber WITH(NOLOCK)
    ON anp_Subscriber.Id = anp_Subscription.SubscriberId
WHERE [MailingListId] = 82924 
  AND Name LIKE '%joe%' 
  AND [AccountID] = 6730

<小时>

为什么执行时间不同?我想使用顶部的查询.我可以做些什么来优化它吗?

---

Why the difference in excecution time? I want to use the query at the top. Can I do anything to optimize it?

提前致谢！/克里斯蒂安

Thanks in advance! /Christian

推荐答案

将 OPTION (RECOMPILE) 添加到查询的末尾.

Add OPTION (RECOMPILE) to the end of the query.

SQL Server 不会嗅探"变量的值，因此您将获得基于猜测统计数据的计划，而不是针对实际变量值量身定制的计划.

SQL Server doesn't "sniff" the values of the variables so you will be getting a plan based on guessed statistics rather than one tailored for the actual variable values.

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