September 16, 2005 at 4:56 am
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 @Answered decimal
Declare @Lost decimal
Declare @LostUnder10 decimal
select @month = datepart (m, getdate())
select @year = datepart (yyyy,getdate())
select @yesterday = @today - 1
select @enddate = cast( replace( cast(@today as char) +'/' +cast(@month as char)+'/'+cast(@year as char),' ','') as datetime)
FROM tblIndex
where DateID >= @startdate
and DateID < @enddate
and CDir in('I', 'N')
and len (ndbo) >4 and len (ndbo)<7
September 16, 2005 at 5:15 am
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:
September 16, 2005 at 5:30 am
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
September 16, 2005 at 5:35 am
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
September 16, 2005 at 5:36 am
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
September 16, 2005 at 6:15 am
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
September 16, 2005 at 7:21 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply