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

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.

Performance of SQL Server 2005 Query

Related

Recent Posts