October 25, 2004 at 5:56 am
I have been tasked to evaluate the performance of a database application, looking especially at execution speed and tempdb usage. I have come across a large number of stored procedures that code inserts in the following manner:
insert into table2 exec('select id,col1 from table1')
Is there any performance difference between the above and
insert into table2 select id,col1 from table1
I am not sure why they used the first style, as none of the select statements are dynamically created.
TIA,
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
October 25, 2004 at 9:40 am
As far as I know there is no difference between the two statements. The only benefit I can see in using exec in a sql insert statement is to dynamically populate the insert values if you do not know what they will be at runtime. I.e. in dts packages using global variables.
However, I have found that by using ordinal values instead of column names, the insert statements do run marginally faster.
I.e - insert into table2 select 1,2 from table1
(where 1 and 2 are the column ordinal positions)
October 25, 2004 at 2:35 pm
Jonathan,
SQL Server does not let you select from tables using ordinal positions. Try the following batch:
create table #tbl1 (cola char(1), colb char(1))
create table #tbl2 (cola char(1), colb char(1))
insert #tbl1
select 'a', 'b'
union all
select 'c', 'd'
union all
select 'e', 'f'
insert #tbl2
select 1, 2
from #tbl1
select * from #tbl2
--
Adam Machanic
whoisactive
October 26, 2004 at 2:19 am
Aplogies, there. I was thinking of dts packages within activex code. I have been working on them for the last couple of weeks and completely lost my marbles.
Thanks for pointing it out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply