Select slow using date range held in variables

Question

Select slow using date range held in variables

ken tracy

SSC Rookie

Points: 29
More actions
September 16, 2005 at 4:56 am

#91297

Hi there.

I am selecting a set of records from a table based on date range (24 hours). This job will be scheduled in the early hours selecting all records from yesterday into a temporary table in order for further queries to work on a smaller set of data.
As its a scheduled job I have calculated a start and end date for yesterday and stored each in a variable.
When I run the select query the job takes over 3 mins. This seemed oddly long... On testing I found that if I hard-coded the date range right into the select statement it took under 2 seconds, using variables it always takes over 3.
I tried on both sql 7 and 2000 with the exact same result.
The variables in question are @startdate and @enddate.
So how come using the variable affects performance so badly?

Thanks.
Ken
Code is below:
SET NOCOUNT ON
SET DATEFORMAT dmy
declare @day decimal(2)
declare @yesterday decimal(2)
declare @today decimal(2)
declare @month decimal (2)
declare @year decimal(4)
declare @startdate datetime
declare @enddate datetime
declare @callcount decimal (4)
declare @answeredcount decimal (4)
declare @lostcount decimal (4)
declare @lostunder10seccount decimal (4)

Declare @Calls decimal
Declare @Answered decimal
Declare @Lost decimal
Declare @LostUnder10 decimal
select @today = datepart (d,getdate())
select @month = datepart (m, getdate())
select @year = datepart (yyyy,getdate())
select @yesterday = @today - 1

select @startdate = cast( replace( cast(@yesterday as char) +'/' +cast(@month as char)+'/'+cast(@year as char),' ','') as datetime)
select @enddate = cast( replace( cast(@today as char) +'/' +cast(@month as char)+'/'+cast(@year as char),' ','') as datetime)

SELECT * into #tmp
FROM tblIndex
where DateID >= @startdate
and DateID < @enddate
and CDir in('I', 'N')
and len (ndbo) >4 and len (ndbo)<7

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

for tempory table it will be better to use insert into than Select * into. Eventhoguh it required you to create temp first, but it will be better in performance wise.

I think better to use convert than cast

My Blog: http://dineshasanka.spaces.live.com/

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

What does the queryplan say? Do you get tablescan when using variables and index seek with hardcoded values?

..felt I had to give a small tip on the datehandling when setting the variables.. You're making it unnecessary difficult with all those parts concatenated together, and even more unfortunate is that the effort is all in vain. (that is, trying to format the date in the specific way)

In my QA I get this result for @startdate and @enddate:

--------------------------- ------------------------

2005-09-15 00:00:00.000 2005-09-16 00:00:00.000

Here's another way for finding 'today' and 'yesterday' that is 100% foolproof and also doesn't care what setting DATEFORMAT may be set to, or the language either - it always works the same on all servers on the globe.

declare @startdate char(8)

declare @enddate char(8)

select @startdate = convert(char(8), dateadd(day, -1, getdate()), 112),

@enddate = convert(char(8), getdate(), 112)

select @startdate as 'yesterday', @enddate as 'today'

yesterday today

--------- --------

20050915 20050916

/Kenneth

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

You are trying to skin the dead cat of getting yesterday the hard way. Why not use something like

BETWEEN DATEADD(D, -1, CONVERT(CHAR(10), GETDATE(), 101)) AND CONVERT(CHAR(10), GETDATE(), 101)

Yes I know there are different and more than likely better ways (can search here for them) on how to do it. But this gives you an alternative. Doesn't use variables and may help speed your query.

I would also review your indexes using the execution plan to ensure that your query is using the indexes you think it should....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

ken tracy SSC Rookie Points: 29 More actions · Answer 4

Hi Kenneth.

Query plan is identical on both queries.

Cheers for the tip on the date calculation. Though that part of the query takes no time at all.

The slowness only happens when using variables.

Even if you just hard code the right values right into the variables missing out any sort of calculation to work yesterday out, it goes slow....

Ken

ken tracy SSC Rookie Points: 29 More actions · Answer 5

Just to say thanks to everyone for the tips on the date calculation.

All suggstions were much more sensible than my own!

Query still slow though...

Ken

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Hmm... are you positive that both plans are identical? If that indeed is the case, there has to be something else. At least the step of retrieveing the rows has to take the same time with variables or hard dates.

/Kenneth