September 4, 2012 at 9:35 am
I had to test this myself so I created 2 small SP's that use the same SQL statement, they just load the temp table differently
#SP1
CREATE TABLE #TEMP1
INSERT #TEMP1
SELECT field1,field2,field3 FROM tableblabla
#SP2
CREATE TABLE #TEMP1
INSERT INTO #TEMP1 (field1,field2,field3)
SELECT field1,field2,field3 FROM tableblabla
I thought that the INTO was optional and that these two statements were actually the same. I turned on client stats and ran both SP's
with:
USE DB
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS TIME ON
EXEC dbo.crsp_timmy1
SET STATISTICS TIME OFF
After mutiple runs, the SP with the INTO was always faster, not by much but faster. From the messages, it apears as though the loading of the temp table is just faster when you add the INTO.
Seems as though the INTO should not be optional if it adds to performance?
Am I missing something here?
September 4, 2012 at 10:25 am
Whatever differences you are seeing are anecdotal. There is no difference in the two uses of the syntax in terms of performance. The statements will be optimized and executed the exact same way.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 4, 2012 at 11:16 am
From the messages, the SQL server Execution times when the temp table is loaded is where the time difference is.
On average the diff is:
For CPU time: 38%
For elapsed time: 8%
Is it possible that when the INTO and fields are added this saves SQL from having to map them out perhaps?
I ran my tests on a fairly small table, maybe someone can run a similar test on something really large to see what happens. I agree they should be the same but this doesn't seem to be the case.
Even if this provides a tiny performance edge it could make a difference in some of the huge SP's I see.
September 4, 2012 at 11:19 am
hanrahan_tim (9/4/2012)
Is it possible that when the INTO and fields are added this saves SQL from having to map them out perhaps?
No. There is no difference.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 4, 2012 at 11:45 am
There's no difference whether or not the INTO keyword is specified. The very first thing that happens as part of query execution is parsing and once that's done, the query has been turned into what's called the parse tree, the syntatical differences will be gone by then.
Test table
CREATE TABLE #test (ID INT, SomeInt INT, SomeChar CHAR(1) )
6 million or so rows in a handy table...
TRUNCATE TABLE #test
INSERT INTO #test
SELECT ID ,
JoinKey ,
ArbFilterColumn
FROM dbo.Spilling1 AS s
PRINT 'insert 1 done'
GO
TRUNCATE TABLE #test
INSERT #test
SELECT ID ,
JoinKey ,
ArbFilterColumn
FROM dbo.Spilling1 AS s
PRINT 'insert 2 done'
Insert into (5 runs):
SQL Server Execution Times:
CPU time = 3089 ms, elapsed time = 3184 ms.
SQL Server Execution Times:
CPU time = 3026 ms, elapsed time = 3080 ms.
SQL Server Execution Times:
CPU time = 3011 ms, elapsed time = 3074 ms.
SQL Server Execution Times:
CPU time = 3026 ms, elapsed time = 3283 ms.
SQL Server Execution Times:
CPU time = 2964 ms, elapsed time = 3044 ms.
Insert (5 runs):
SQL Server Execution Times:
CPU time = 3011 ms, elapsed time = 3123 ms.
SQL Server Execution Times:
CPU time = 3057 ms, elapsed time = 3085 ms.
SQL Server Execution Times:
CPU time = 3058 ms, elapsed time = 3117 ms.
SQL Server Execution Times:
CPU time = 3026 ms, elapsed time = 3167 ms.
SQL Server Execution Times:
CPU time = 3073 ms, elapsed time = 3181 ms.
Now specifying the columns for the insert is good practice (which I didn't follow in my example :blush:) and necessary if you're not inserting all columns. The column list should always be specified for an insert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply