free_mascot (4/21/2015)
Select statement is always fater than Insert statement.
This is interesting - I had never heard that. Why would INSERT...SELECT take noticeably longer than SELECT INTO?
free_mascot (4/21/2015)
This looks like optimizer issue.Extract from Microsoft:
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.
UNKNOWN
Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.
I am curious, instead of temp table if we create create physical table and try your query will improve the performance? Just both way give it a try 1. How you are doing and 2. what Igor has suggested.
i.e.
INSERT tbl1 SELECT x, y, zā¦
AND
create table tbl1(
ID int identity(1,1) prmiary key,
col1 data type c1,
col2 data type c2,
...
coln data type cn)
And let's see the difference.
HTH
I will try your suggestions, but since I recompiled the SP I can no longer reproduce the behavior, so I will have to wait until this breaks again.
The behavior is not occurring in my non-prod environments either, but just for grins, I did try Igor's suggestion (creating the #tmp table with a declared primary key). The query plan was identical except for a Clustered Index Insert that wasn't there before š