October 10, 2006 at 1:09 pm
Hi,
I have got two scripts:
first one
---------------------------------------
DECLARE @TableVariable table(field int)
DECLARE @Counter int
SET @Counter = 100000
WHILE @Counter>0
BEGIN
INSERT INTO @TableVariable (field) VALUES (@Counter)
SET @Counter= @Counter-1
END
/*
CREATE TABLE #TempTable (field int)
INSERT INTO #TempTable SELECT * FROM @TableVariable
DROP TABLE #TempTable
*/
declare @StartTime datetime
declare @EndTime datetime
set @StartTime = getdate()
select * from @TableVariable T1 join @TableVariable T2 on (T1.field = T2.field)
set @EndTime = getdate()
select datediff(millisecond, @StartTime, @EndTime)
-----------------------------------------------------
second:
-----------------------------------------------------
DECLARE @TableVariable table(field int)
DECLARE @Counter int
SET @Counter = 100000
WHILE @Counter>0
BEGIN
INSERT INTO @TableVariable (field) VALUES (@Counter)
SET @Counter= @Counter-1
END
CREATE TABLE #TempTable (field int)
INSERT INTO #TempTable SELECT * FROM @TableVariable
DROP TABLE #TempTable
declare @StartTime datetime
declare @EndTime datetime
set @StartTime = getdate()
select * from @TableVariable T1 join @TableVariable T2 on (T1.field = T2.field)
set @EndTime = getdate()
select datediff(millisecond, @StartTime, @EndTime)
------------------------------------------------------
Please, note that the only difference between those two is fragment:
---------------------------------
CREATE TABLE #TempTable (field int)
INSERT INTO #TempTable SELECT * FROM @TableVariable
DROP TABLE #TempTable
-----------------------------------
which is commented in first version.
Thing to compare is the speed of:
select * from @TableVariable T1 join @TableVariable T2 on (T1.field = T2.field)
In first case it runs about two times slower than in second.
Does it mean that inserting into temprary table causes data stored in @TableVariable to be organized somehow (in second case select returns ordered data)?
Could someone please explain to me what is the mechanism behind this or give me some clues where I could find out?
I run both in Query Analyzer.
Cheers
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 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply