October 10, 2006 at 1:45 pm
I would recommend running your test multiple times and clearing out the buffers and cache in between. I get identical execution times (and execution plans) when I run both of your examples.
October 10, 2006 at 11:58 pm
Of course I have run it multiple times, what's more I did it on different machines. EVERY time I get about twice as fast execution time of this select:
select * from @TableVariable T1 join @TableVariable T2 on (T1.field = T2.field)
in second case. It is not about the overall execution time of this script but only this select.
Do you get the same time difference here:
select datediff(millisecond, @StartTime, @EndTime)
for both cases?
October 11, 2006 at 1:54 am
How can I clear out buffers and cache? Which command does that? I got the same effect as Tomasz described - two different times; second query runs twice faster than first.
October 11, 2006 at 1:55 am
sorry but I was not able to reproduce what your posting suggests ( sql 2k sp4 ) , I used my local sql server and a high end server for the tests, run multiple times. times were near enough consistantly the same regardless.
Out of interest if you replace the table variable with a #temp table the whole thing runs in under 50% of the time.
what version of sql server and sp level are you running and have you made any changes to your QA settings ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 11, 2006 at 2:16 am
MSDE 2000 with SP3, QA runs on default settings.
October 11, 2006 at 2:17 am
My Sql Server details are:
Version = 8.00.2039
Level = SP4
Edition = Developer Edition
I didn't change anything in settings of QA (default settings used).
If you are using a high end machine maybe try running this for 1000 000 @counter value or 2000 000. The more records there are the more significant the difference is.
October 12, 2006 at 2:03 am
All run locally to database engine? My local sql is developer, the server was ent edition. In both cases I ran the queries connected locally.
Sorry but I cannot get any differences other than a few milliseconds. You could try re-applying the service pack.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply