Solved

Slow query only when doing a SET to a variable

Posted on 2006-11-06
6
207 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Accepted Solution

by:
muzzy2003 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now