In this article, we will verify if the following statements are true or they are just myths:
- Truncate is faster than a delete and it consumes less space in the logs
- Do not use cursors to Create or drop multiple objects because it is very slow
- Do not use cursors to convert or process row values because it is very slow
- Truncate is faster than a delete and it consumes less space in the logs
To delete all rows in a table, the best option is to use a truncate statement. This option is faster and consumes fewer system and log resources. But is it true?
We will test if that is true by creating table with a million rows in two different databases. We will test the delete statement in one and in another; we will test the truncate statement.
We will use the simple recovery model in the database to reduce the number of entries in the log.
We will first create 2 databases:
create database deleteDemo
create database truncateDemo
These databases will be used to compare the results of the delete and truncate statements:
Figure 0. 2 databases created
The initial size is 16 MB for both databases (this value depends on the SQL Server version):
Figure 1. Database size
We will set the recovery model to simple. This mode reduces the entries in the log. However, you cannot restore the database in a specific time with this option.
In the Database Properties, go to Options and in Recovery model, select simple:
Figure 2. Recovery model
You can also change the recovery model using T-SQL:123456789USE [master]GOALTER DATABASE [deleteDemo] SET RECOVERY SIMPLE WITH NO_WAITGOUSE [master]GOALTER DATABASE [truncateDemo] SET RECOVERY SIMPLE WITH NO_WAITGO
In order to test the truncate and delete statements, we will create a table named myprices in each database with a million rows:123456789101112131415161718192021222324252627282930313233343536373839--Table truncateDemoUse truncateDemoGOwith listpricesas(select 1 id, CAST(RAND(CHECKSUM(NEWID()))*1000000 as int) pricesunion allselect id + 1, CAST(RAND(CHECKSUM(NEWID()))*1000000 as int) pricesfrom listpriceswhereid <= 1000000)select *into mypricesfrom listpricesOPTION(MAXRECURSION 0)--Table deleteDemoUse deleteDemoGOwith listpricesas(select 1 id, CAST(RAND(CHECKSUM(NEWID()))*1000000 as int) pricesunion allselect id + 1, CAST(RAND(CHECKSUM(NEWID()))*1000000 as int) pricesfrom listpriceswhereid <= 1000000)select *into mypricesfrom listpricesOPTION(MAXRECURSION 0)
Note that the size of the database has increased from 16 MB to 144. This is because of the large number of rows inserted. The log file grows even with a Recovery Model set to simple:
Figure 3. Database Size after inserting a million rows
We will first test the delete statement:123USE deleteDemoGODELETE FROM [dbo].[myprices]
As you can see, it takes 23 seconds to delete a million rows:
Figure 4. Time required to delete a million rows
If we check the database properties, we will notice that the database size increased to 528 MB!
Figure 5. Database growth after deleting a million rows
Now, we will try the truncate table statement:123USE truncateDemoGOTRUNCATE TABLE [dbo].[myprices]
When you run truncate, the execution time is 0 seconds:
Figure 6. Truncate execution time
If we check the log file, we notice that the database size did not change!
Figure 7. Database size after truncating
As you can see, you save space and the execution time is better with truncate. If you need to delete all the rows, it is a must to use it.
- Do not use cursors Create or drop multiple objects because they are very slow
In the next example, we will show 2 ways to delete multiple SQL Server Objects. One using cursors and another using CTEs to generate the statementsstatements. We will see which option is faster.
First, we will create 10000 tables using T-SQL:12345678910111213with ctesequenceas(select 1 idunion allselect id + 1from ctesequencewhereid < 10000)select 'create table table'+cast(id as varchar(8))+'(id int)'from ctesequenceOPTION(MAXRECURSION 0)
This code will create the following T-SQL statements:12345Create table table1Create table table2Create table table3….Create table table10000
Copy and paste the 10,000 create table statementsstatements and execute them.
You will have 10,000 tables created.
We will first create a cursor named delete_tables that will drop all the tables that start with the name table:1234567891011121314151617181920--create a cursor named delete_tablesDECLARE delete_tables CURSOR FAST_FORWARD FOR--The query will show all the tables whose name start with the word tableSELECT [TABLE_NAME]FROM [INFORMATION_SCHEMA].[TABLES]WHERE [TABLE_NAME] like 'table%'OPEN delete_tablesDECLARE @tablename varchar(30)FETCH NEXT FROM delete_tables into @tablenameWHILE @@FETCH_STATUS <> -1BEGIN--we are dropping all the tablesexecute ('drop table '+@tablename )FETCH NEXT FROM delete_tables into @tablenameENDCLOSE delete_tablesDEALLOCATE delete_tablesGO
The cursor will delete the 10,000 rows in 1 minute, 30 seconds:
Figure 8. Execution time
We will try now another method to delete 1,000 tables. Create the 1,000 tables again and run these statements:12345678910111213with ctesequenceas(select 1 idunion allselect id + 1from ctesequencewhereid < 100000)select 'drop table table'+cast(id as varchar(8))from ctesequenceOPTION(MAXRECURSION 0)
This sentence will generate 10,000 statements to drop 10,000 tables. Copy and paste them and finally execute them:
Figure 9. 10000 drop statements took 1 minute 15 seconds
In this example, we are running drop statements sequentially in both cases. Running without cursors is a little bit faster because of the locks and the where clause used in the cursors. However, it is not a big difference in this case. The advantage of using cursors is that you delete them at once and you do not need to copy and paste the drop statements.
- Do not use cursors to convert or process row values because they are very slow
In the last example, we will convert rows to a column separated by commas.
For example, if we have a table like this:
ID 1 2 3
We want to have the following result:
We will create a table with 1000 values named #mysequence:1234567891011121314with ctesequenceas(select 1 idunion allselect id + 1from ctesequencewhereid < 1000)select *into #mysequencefrom ctesequenceOPTION(MAXRECURSION 0)
We will first try with cursors:12345678910111213141516171819--Create a cursorDECLARE concatenate_values CURSOR FORSELECT cast(id as varchar(30))FROM #mysequenceOPEN concatenate_valuesDECLARE @value varchar(max)=''DECLARE @initialvalue varchar(8)=''FETCH NEXT FROM concatenate_values into @initialvalueWHILE @@FETCH_STATUS <> -1BEGIN--Concatenate the valuesSELECT @value= @value+','+@initialvalueFETCH NEXT FROM concatenate_values into @initialvalueENDCLOSE concatenate_valuesDEALLOCATE concatenate_valuesselect @value
The cursor will display the values separated by commas in 3 seconds:
Figure 10. Cursor execution time
We will now try to use something different from cursors. To verify the time we will set statistics and time to on in order to have the execution time in milliseconds (ms):123SET STATISTICS io ONSET STATISTICS time ONGO
We will first concatenate the values using a variable named @concatenatevalues and concatenate the values separated by commas:123456declare @concatenatedvalues varchar(max)SET @concatenatedvalues = ''select @concatenatedvalues = @concatenatedvalues + cast(id asvarchar(30))+ ',' from #mysequenceselect SUBSTRING(@concatenatedvalues, 0, LEN(@concatenatedvalues)) as[values]
These queries will take less than 100 ms in average:
Figure 11. Execution time
Another option is to use the XML PATH, this option is shorter, easier to read, but it takes longer time:123SELECT ',' + cast(id as varchar(30))FROM #mysequenceFOR XML PATH('')
Figure 12. XML PATH solution
In this article, we verified that truncate is more efficient that a delete statement. In our example, we verified that truncating 1 million rows does not fill the log file and it takes less than 1 second. By the other hand, we found that the delete statement increased the database size from 144 Mb to 528 Mb. In addition, the execution time to delete was 23 seconds.
|Statement||Time (seconds)||Growth Size in Mb|
Conclusion: When you need to delete all the rows, you should use Truncate.
In the second test, we dropped 10000 tables using cursors and then using CTE to generate the statements.
Cursors took 1 minute 30 seconds and CTE took 1 minute with 15 seconds.
The advantage of Cursors is that you can execute and delete the tables at once. In the CTE method, you need to generate the statements and after that, copy and paste the statements. The CTE code is simpler.
Conclusion: If you need to delete, create 100+ objects, using cursors is not a bad choice. Do not use Cursors if you can work with SETs of data.
In our third test, we converted 1000 rows into a single variable of 1000 values separated by commas.
We used 3 methods:
- Simple T-SQL
- T-SQL with XML PATH mode
|T-SQL with XML PATH||0.3|
Conclusion: When you need to update rows in a table, delete rows, insert or search values, using cursors isn’t a good idea. As you can see in this example, it is extremely slow.
XML PATH is commonly used in many scenarios to manipulate and modify row values to replace the cursors. However, there are sometimes faster solutions.
For more information, refer to these links:
- Examples: Using PATH Mode
- Using SQL Server cursors – Advantages and disadvantages
- SQL Server cursor performance problems
- SQL Server cursor tutorial
- DECLARE CURSOR (Transact-SQL)
- WITH common_table_expression (Transact-SQL)
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.
View all posts by Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
- How to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS - April 25, 2017
- SQL Server vNext – Does Microsoft love Linux? - April 21, 2017
- Functions and stored procedures comparisons in SQL Server - February 20, 2017