While reviewing an
archiving script at my company I realised that most of the time spent waiting
for the script to complete was due to the deletion process. While trying to
find a better way to perform the deletion of data from the database I came
across the DDL statement: TRUNCATE TABLE.
The TRUNCATE TABLE and DELETE statements can be used to remove records from a table, the difference is that DELETE can be used to remove some or all of the data in the
table while TRUNCATE TABLE can only be used to remove all the data from a table
(no WHERE clause). However TRUNCATE TABLE is faster than the DELETE statement
and requires fewer system and transaction logs resources because of the
difference in how they execute and operate. TRUNCATE TABLE removes data by
deallocating data pages instead of removing rows one at a time.
I decided to do a comparison of the two statements using a test table with 3.4 million rows.
The results for each
script/statement had four grids:
- The first result grid shows
the size of the MDF and LDF before the process.
- The second result grid shows
the number of records in the table before the DELETE/TRUNCATE TABLE
statement is executed.
- The third result grid shows
the number of records in the table after the DELETE/TRUNCATE TABLE
statement is executed.
- The fourth result grid shows
the size of the MDF and LDF after the process.
DELETE Statement
Script
Results
Notice the increase
sized of LDF after the process (from 5.5625 MB to 2321.375 MB). The script was
executed three times and each time the LDF was the increased to the same size.
The execution time
results:
SQL Server Execution Times:
CPU time = 6177 ms, elapsed time = 114451 ms.
SQL Server Execution Times:
CPU time = 6349 ms, elapsed time = 105675 ms.
SQL Server Execution Times:
CPU time = 6193 ms, elapsed time = 105816 ms.
TRUNCATE TABLE Statement
Script
Results
Notice no change to
the size of the MDF or LDF. The script was also executed three times.
The execution time
results:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 140 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 96 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 191 ms.
Conclusion:
Based on the
execution times results it is very clear that the TRUNCATE TABLE statement is
faster than the DELETE statement. It is
also clear that the DELETE statement process of removing rows one at a time
requires more transaction log space when compared to the TRUNCATE TABLE
process.
See the following
MSDN links to understand for more information about
TRUNCATE TABLE and
DELETE.
