Solved

Slow query only when doing a SET to a variable

Posted on 2006-11-06
6
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

689 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