I have a strange problem. Whenever I run a particular query by itself, it returns a result in an instant. When I set the results of the query to a variable it returns in 30 seconds or so.
Why is it doing this? Is there any settings I need to change. The reason I say this is that this query runs great on the production server, but on the backup SQL Server I am setting up it runs slow.
The following is part of a Stored Proc.
DECLARE @ContactID as int
DECLARE @CurrentDateTime as DateTime
DECLARE @DaysAgo as datetime
-- How long ago the last time a contact as been pulled before it can be pulled again
SET @DaysAgo = dateadd(dd,-60,getdate())
-- The time used to compair time zones (may need to add or subtract hours for which server it is on to be CST)
SET @CurrentDateTime = getdate()
--If it finds no record it returns a 0
SET @ContactID =(select top 1 isnull(co.ContactID,0)
from contact co
inner join CampaignContact cc on (cc.ContactID = co.ContactID)
inner join CampaignUser cu on (cu.CampaignID = cc.CampaignID)
where co.LastUpdated <= @DaysAgo
and co.LockedBy = 0
and cu.UserID = @UserID
and cu.ActiveOn = 1)