2

I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1.

#TempTable1 has a BIT field called ToInclude which is set in a subsequent UPDATE query and uses a CASE statement based on other fields in the table to set the value appropriately.

The UPDATE #TempTable1 SET ToInclude = CASE... query takes a full minute to execute. But instead of doing an UPDATE, if I SELECT *, CASE...END AS ToInclude INTO #TempTable2 that only takes about five seconds, even though it's the same exact CASE statement I'm using in either query.

Here are the TIME and IO statistics for each instance.

UPDATE Query (#TempTable1): Update Query

SELECT *, CASE...END AS ToInclude INTO #TempTable2 Query: SELECT *, CASE...END AS ToInclude Query

I see in the first case (for the UPDATE query) SQL uses the Worktable. I've never heard of this concept before, but more so I'm interested in why it goes this route. It looks like in the first case, it takes an extra 15 million logical reads too, not sure why.

Also, I've tested this multiple times, switching the order of which query I run first, and dropping #TempTable1 between each test, to ensure I'm not experiencing a difference in memory caching. These results continue to be repeatable.

  • 1
    My guess is that you end up with some kind of halloween-protection. I.e., SQL Server need to protect itself from modifying a row, making the row move to somewhere else and it need to make sure that it now don't update that same row once again. Perhaps having a clustered index on the table will make a difference, that would be my first thing to explore, if not else just as a test... – Tibor Karaszi Nov 29 '19 at 17:15
  • I usually like to index my tables if they're going to be re-used, but unfortunately I've found that I get the best performance with the operations that this table undergoes for it's lifespan by not indexing it. (I.e. In this case, it takes longer to create the index on the table than the time the index saves on the subsequent queries executed against the table.) – J.D. Nov 29 '19 at 18:09
  • Also, you might be right. I just re-read how SQL server handles the Halloween problem, and it seems like it introduces an extra step into the execution plan for Eager Spool. I re-ran my test case for the UPDATE query and it does have the Eager Spool step in the execution plan (with a fairly significant amount of time tied to it) whereas my SELECT INTO query does not have that step, otherwise the execution plans are pretty similar. – J.D. Nov 29 '19 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.