Performance of SQL Server 2005 Query

问题:

-------------------- 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

---

--------------------- 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

回答1:

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

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.

回答2:

One possible item to check is whether the MailingListId and AccountId fields in the tables are of type INT. If, for example, the types are BIGINT, the query optimizer will often not use the index on those fields. When you explicitly define the values instead of using variables, the values are implicitly converted to the proper type.

Make sure the types match.

回答3:

The second query has to process ONLY 2000 records. Point.

The first has to process ALL records to find the maximum.

Top 2000 does not get you the highest 2000, it gets you the first 2000 of the result set - in any order.

if yo uwant to change them to be identical, the second should read

TOP 1

and then order by anp_Subscriber.Id descending (plus fast first option).