Solved

Slow query only when doing a SET to a variable

Posted on 2006-11-06
6
214 Views
Last Modified: 2008-02-01
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
Comment
Question by:marms767
  • 2
  • 2
  • 2
6 Comments
 
LVL 11

Expert Comment

by:rw3admin
ID: 17883228
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
 

Author Comment

by:marms767
ID: 17883519
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17883780
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 16

Accepted Solution

by:
muzzy2003 earned 500 total points
ID: 17884328
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
 

Author Comment

by:marms767
ID: 17884962
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17885242
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question