Truncate table updating statistics

Rated 4.59/5 based on 809 customer reviews

Background information: Accuracy High is SAPDBA statistics method C (Calculate).

Now I think it's safe to say which update will perform better but let's see how much better. UPDATE Test Upd Table SET col1='23' WHERE pk=10 UPDATE Test Upd Table SET col2='25' WHERE pk=10 UPDATE Test Upd Table SET col3='27' WHERE pk=10 UPDATE Test Upd Table SET col6=getdate()-1 WHERE pk=10 UPDATE Test Upd Table SET col7=getdate()-1 WHERE pk=10 UPDATE Test Upd Table SET col8=getdate()-1 WHERE pk=10 UPDATE Test Upd Table SET col11='test23' WHERE pk=10 UPDATE Test Upd Table SET col12='test25' WHERE pk=10 UPDATE Test Upd Table SET col13='test27' WHERE pk=10 GO UPDATE Test Upd Table SET col1='23',col2='25',col3='27', col6=getdate()-1,col7=getdate()-1,col8=getdate()-1, col11='test23',col12='test25',col13='test27' WHERE pk=15 GO Looking at the SQL Profiler statistics below it's pretty obvious just how much better the performance is when performing this update using a single statement.If the runtime is acceptable, then repeat the statistics refresh with High, if desired.The system updates the CBO statistics for the table.-- change to sqlcmd mode truncate table Test Table GO BULK INSERT dbo.Test Table FROM 'C:\scripts\Test Table10.dat' WITH (DATAFILETYPE='native') GO truncate table Test Table GO BULK INSERT dbo.

Leave a Reply