I was reviewing the performance of a procedure recently and stumbled over another pumpkin.
My last blog post was about the Halloween Problem, and we saw its effects on an UPDATE statement. In this case, it was the same issue but with an INSERT statement. Here’s the code being executed:
INSERT INTO Schema1.Object1 ( Column1, Column2, Column3, Column4 ) SELECT Object2.Column1, Object2.Column2, Object2.Column3, Object2.Column4 FROM Object3 Object2 LEFT LOOP JOIN Schema1.Object1 Object4 WITH(INDEX(Column5)) ON Object4.Column3 = Object2.Column3 AND Object4.Column1 = Object2.Column1 AND Object4.Column2 = Object2.Column2 AND Object4.Column4 = Object2.Column4 WHERE Object4.Column6 IS NULL
The gist is, we’re trying to insert a record into Object1, assuming said record doesn’t already exist. We’re querying the data we want to insert from a temp table, but joining to the base table to make sure a record doesn’t already exist with the same values.
In the case of an UPDATE statement, if we update fields that are in our query’s search criteria, we could update the same row multiple times. SQL Server’s Halloween protections prevent this, but result in extra work that affect our performance.
The INSERT statement here is similar, trying to insert a record while querying to see if the same record exists. So, again SQL Server adds Halloween protections to our plan:
I would have expected us to scan the temp table, then have a LEFT JOIN to the base table. The Table Spool is the red flag that we have an issue with the plan, and is frequently seen with Halloween protections.
The index scan on the base table seems to be overkill since we’re joining on the primary key columns (the key lookup isn’t much of a concern). But we’re likely doing the scan because of the spool; it’s SQL Server’s way of getting all relevant records in one place at one time, breaking the normal flow of row mode operation, to make sure we don’t look up the same record multiple times.
The data we are trying to insert is being passed into the procedure using a memory-optimized table valued parameter. We’ve queried that into the temp table as another step before our final INSERT SELECT query, because SQL Server will sometimes make poor optimizations when TVP’s are involved (because they have no statistics).
The solution then is an easy one. We move our LEFT JOIN out of the final INSERT, and we make that check as we query the TVP’s data into the temp table. We separate the SELECT against that table from the INSERT; they are now in separate operations, and the Halloween protections are no longer necessary.
If you liked this post, please follow me on twitter or contact me if you have questions.