March 5, 2013 at 9:25 am
All:
I have noticed some behavior that I can't explain with sql similar to the following. It is reproducible on multiple machines in our enterprise so isn't machine specific. Basically by moving the insert statement to put data into a temp table outside of dynamic sql vs. inside the dynamic sql, we are seeing completely different query plans and performance which seems ... strange.
It seems that this is a scope issue but I'm curious if somebody has a more specific explanation of what we are seeing.
My example:
CREATE #table (myFoo varchar(10) )
DECLARE @sql NVARCHAR(MAX) = '
INSERT INTO #table
SELECT foo FROM bar
'
EXEC (@sql)
Results in:
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 862 ms.
VS:
CREATE #table (myFoo varchar(10) )
DECLARE @sql NVARCHAR(MAX) = '
SELECT foo FROM bar
'
INSERT INTO #table
EXEC (@sql)
Results in:
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 51 ms. and a much better and different plan
Can somebody please explain what is happening?
Thanks,
Tim Januario
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy