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

Question

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?

Answer 1

A few thoughts:

1. Make sure your statistics are up to date. Bad statistics account for many of the bizarre "intermittent" query plan problems.

2. Make sure your indexes are covering, otherwise there's a much higher probability of the optimizer ignoring them.

3. If none of that helps, you can always force a specific join by writing INNER HASH JOIN as opposed to just INNER JOIN.

Answer 2

Does the destination table have a clustered index? The choice of join may be necessary to facilitate the ordering of the data in the insert. I've seen execution plans differ depending on whether the destination table has a clustered index and what column(s) it is on.