• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Slow query only when doing a SET to a variable

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)


0
marms767
Asked:
marms767
  • 2
  • 2
  • 2
1 Solution
 
rw3adminCommented:
you mean you run this complete script and it runs faster in QA vs when you run this in a proc?
there maybe other logic in proc which is the reason for slow down,

--If it finds no record it returns a 0
Set rowcount 1
Select @ContactID=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
set rowcount 0


but then also run kind of benchmark to find exact bottleneck


Select 'Starting declaration '+convert(varchar(20),Getdate(),100)
DECLARE @ContactID as int
DECLARE @CurrentDateTime as DateTime
DECLARE @DaysAgo as datetime

Select 'Set @DaysAgo'+convert(varchar(20),Getdate(),100)
-- How long ago the last time a contact as been pulled before it can be pulled again
SET @DaysAgo = dateadd(dd,-60,getdate())

Select 'Set ACurrentDateTime'+convert(varchar(20),Getdate(),100)
-- 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()

Select 'Set @ContactID'+convert(varchar(20),Getdate(),100)
--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)

Select 'Finished Set @ContactID'+convert(varchar(20),Getdate(),100)

0
 
marms767Author Commented:
Actually, either in QA or in the StoredProc the T-SQL runs slow. What I mean is it I just run the following part by itself in QA:

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 = 1
          and cu.ActiveOn = 1

It returns instantly, but if I set that result to a variable in QA or the StoredProc

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 = 1
          and cu.ActiveOn = 1)

It takes 20 to 30 seconds to return

I ran your "benchmark" test and the bottleneck is for sure the  "SET @ContactID = (thequery)" part. It seems that setting the resutl to a variable is the issue.

The strange thing is I have te exact same query running in production on a different MS SQL Server and it has no problems.

Could it be some setting in MS SQL Server I need to change?

FYI: I need to set it to a varaible in the stored proc because later on down I "UPDATE" the record to change the "co.LockedBy" bit to keep another user from pulling the same record.

0
 
rw3adminCommented:
Once a wiseman told me, if nothing else seems to work on MSSQL then restart SQL services :), maybe a tempdb related issue I dont see how but I have limited knowledge of SQL structure I just know programming side more.

rw3admin
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
muzzy2003Commented:
Have you tried:

select top 1 @ContactID = 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 = 1
          and cu.ActiveOn = 1

0
 
marms767Author Commented:
The "select top 1 @ContactID = isnull(co.ContactID,0)" from muzzy2003 worked! It returned instantly.

Can you give me an idea of why that would make a difference?
0
 
muzzy2003Commented:
It's because the subquery will be spooled to tempdb, and as rw3admin says there may be some issue with this. The direct select like this avoids it. But you still should investigate it because it shouldn't happen.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now