Same query has nested loops when used with INSERT, but Hash Match without.

Posted by AaronLS on Stack Overflow See other posts from Stack Overflow or by AaronLS
Published on 2010-04-22T23:12:49Z Indexed on 2010/04/22 23:33 UTC
Read the original article Hit count: 298

Filed under:
|
|

I have two tables, one has about 1500 records and the other has about 300000 child records. About a 1:200 ratio. I stage the parent table to a staging table, SomeParentTable_Staging, and then I stage all of it's child records, but I only want the ones that are related to the records I staged in the parent table. So I use the below query to perform this staging by joining with the parent tables staged data.

--Stage child records
INSERT INTO [dbo].[SomeChildTable_Staging]
           ([SomeChildTableId]
           ,[SomeParentTableId]
           ,SomeData1
           ,SomeData2
           ,SomeData3
           ,SomeData4    
        )
    SELECT [SomeChildTableId]
        ,D.[SomeParentTableId]
           ,SomeData1
           ,SomeData2
           ,SomeData3
           ,SomeData4    
  FROM [dbo].[SomeChildTable] D
INNER JOIN dbo.SomeParentTable_Staging I ON D.SomeParentTableID = I.SomeParentTableID;

The execution plan indicates that the tables are being joined with a Nested Loop. When I run just the select portion of the query without the insert, the join is performed with Hash Match. So the select statement is the same, but in the context of an insert it uses the slower nested loop. I have added non-clustered index on the D.SomeParentTableID so that there is an index on both sides of the join. I.SomeParentTableID is a primary key with clustered index.

Why does it use a nested loop for inserts that use a join? Is there a way to improve the performance of the join for the insert?

© Stack Overflow or respective owner

Related posts about Performance

Related posts about tsql