2

I have been able to optimize large table UPDATES with JOINS by separating the UPDATE and JOIN into two queries. I know the UPDATES are logged, but by creating a temporary table I have greatly reduced my UPDATE time by joining with the temporary table that contains only the records I am interested in updating.

However, I believe the biggest reason for the success of this strategy is because the command: SELECT INTO #TempTable is NOT logged and treated as a BULK copy in the log file. I do not care about dirty reads as this is a Datawarehouse type of situation. Does anyone know if SELECT INTO Table or SELECT INTO #TempTable is stored in the transaction log with the data?

Clark Vera
  • 183
  • 1
  • 7
  • 1
    It is logged. otherwise ACID would break – user1443098 Jul 19 '18 at 20:38
  • After further consideration, I think it is irrelevant if the SELECT into is logged or not, because the actual UPDATE occurs on a table against a temporary table, which means NO blocking can occur on the temp table since it is isolated in the session, which would NOT be the case if a temp table were not created. The table being updated is logged, but because of the time savings, the chances for blocking are significantly reduced. – Clark Vera Jul 19 '18 at 21:38
  • SELECT INTO is minimally logged. Only extent allocations. et. al. are logged, not rows. – Dan Guzman Jul 20 '18 at 06:17

1 Answers1

2

I believe the biggest reason for the success of this strategy is because the command: SELECT INTO #TempTable is NOT logged and treated as a BULK copy in the log file.

First of all, in SQL Server there are NO non-logged user database operations (the only non-logged operations are the version stores and can be minimally workfiles in tempdb).

Bulk operations are also logged, but they can be minimally logged if they meet some requisites, in simple and bulk logged recovery models, and bulk operations are fully logged under the full recovery model.

In this sense, SELECT INTO temp table is "like BULK", it's minimally logged, tempdb recovery model is always SIMPLE.

In user database that is in SIMPLE or BULK LOGGED recovery model SELECT INTO is also minimally logged.

In The Data Loading Performance Guide you can find Minimal Logging Conditions and here: Understanding Logging and Recovery in SQL Server by Paul S. Randal read more on logging in SQL Server.

And this is the article dedicated to logging in tempdb: Understanding Logging in Tempdb

sepupic
  • 8,409
  • 1
  • 9
  • 20