Solved

Slow query only when doing a SET to a variable

Posted on 2006-11-06
6
212 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 31
insert wont work in SQL 14 22
Flattening heirachies 3 31
transaction in asp.net, sql server 6 33
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 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