June 7, 2019 at 9:01 pm
I'm looking for a general answer. In my experience, the performance of inserting data into a table from the results of a LEFT OUTER JOIN query is significantly worse than running two queries - one to populate the destination table followed by update of the destination table using INNER JOIN. I thought SQL Server was smarter and could handle that. A sample of the left outer join and inner join/update is below, and the problem is significant when the table sizes are large:
create table #TempTable_LeftJoin (
ID int,
Value1 int,
Value2 int,
primary key clustered ( ID )
);
create table #TempTable_InnerJoin_Update (
ID int,
Value1 int,
Value2 int,
primary key clustered ( ID )
);
create table #MyTable1 (
ID int,
Value int,
primary key clustered ( ID )
);
create table #MyTable2 (
ID int,
Value int,
primary key clustered ( ID )
);
-- Left outer join
insert #TempTable_LeftJoin ( ID, Value1, Value2 )
select
ID = T1.ID,
Value1 = T1.Value,
Value2 = T2.Value
from
#MyTable1 T1
left outer join #MyTable2 T2 on
T2.ID = T1.ID;
-- Insert with inner join update
insert #TempTable_InnerJoin_Update ( ID, Value1 )
select
ID = T1.ID,
Value1 = T1.Value
from #MyTable1 T1
update #TempTable_InnerJoin_Update
set Value2 = T2.Value
from
#TempTable_InnerJoin_Update TT
inner join #MyTable2 T2 on
T2.ID = TT.ID;
June 7, 2019 at 9:11 pm
A general answer to what? You haven't asked a question.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 8, 2019 at 3:49 pm
The performance is going to be based on the query performance - and that will be dependent on the indexes available and how the query is written.
I would not say that an insert/update is 'generally' faster...in fact I would probably lean the other way and say that a single insert will be faster than 2 separate operations across the full table.
But - it really depends on the tables, the indexes available - the execution plan generated for each as well as the number of rows affected in each operation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 10, 2019 at 1:09 pm
I'd need to see more specifics around this. There's nothing magically or uniquely bad or dangerous about a LEFT JOIN when manipulating data, so there's got to be more to the issue here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply