Who Needs No Stinkin’ Transaction Log (Well Sometimes)
I have a problem. I have to transform 33 million rows from one table to another in my staging SQL Server. The problem is that my Insert Into statement is getting logged and it takes over 10 hours to run due to File I/O. I want it to run faster since the data is not 100% ready and I will have to make some changes to the raw data then retransform in. I called my teammate and pal Andy Catlin (who claims that 1.1 million people read this blog daily) for some advice on how to have the insert statement without getting logged to the transaction log. So here is my insert statement that takes 10+ hours:
Insert into dbo.RPT_ADNPMP12
Select CCODE as County_ID, SICGP as SIC_Code, Rpt_Year, Rpt_Quarter, SpendOrig, SpendDest, 10 as MediaType_ID, 'Other Publications' as MediaType, 11 as Sort_Order,
0 as PercentOfTotal,(ONLNAT+ONLDIR) + ONLLOC as TotalSpending_AMT,
ONLLOC as Local_AMT, ONLNAT+ONLDIR as National_AMT
From dbo.PreptoUpload
Andy said to use with caution the following, setting the recovery mode (using alter database) before and after the insert statement. Not logging made the 33 million rows run in less than 2 hours from the 10 it took with logging. Here is the same code not logged:
Alter Database TestStageToProduction SET Recovery Simple
Insert into dbo.RPT_ADNPMP12
Select CCODE as County_ID, SICGP as SIC_Code, Rpt_Year, Rpt_Quarter, SpendOrig, SpendDest, 10 as MediaType_ID, 'Other Publications' as MediaType, 11 as Sort_Order,
0 as PercentOfTotal,(ONLNAT+ONLDIR) + ONLLOC as TotalSpending_AMT,
ONLLOC as Local_AMT, ONLNAT+ONLDIR as National_AMT
From dbo.PreptoUpload
Alter Database TestStageToProduction SET Recovery Full
So my advice to you is to use this with CAUTION, the transaction log is a GOOD THING, but in a rare case like this when we are just transforming data from one table to another life is good without the log.
6:30:04 AM 
|
