Daniel Calbimonte

SQL Server performance myth busters

December 21, 2016 by

Introduction

In this article, we will verify if the following statements are true or they are just myths:

  1. Truncate is faster than a delete and it consumes less space in the logs
  2. Do not use cursors to Create or drop multiple objects because it is very slow
  3. Do not use cursors to convert or process row values because it is very slow

Getting started

  1. 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 a 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:

    In order to test the truncate and delete statements, we will create a table named myprices in each database with a million rows:

    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:

    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:

    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.

  2. 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 statements. We will see which option is faster.

    First, we will create 10000 tables using T-SQL:

    This code will create the following T-SQL statements:

    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:

    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:

    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.

  3. 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:

    1,2,3

    We will create a table with 1000 values named #mysequence:

    We will first try with cursors:

    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):

    We will first concatenate the values using a variable named @concatenatevalues and concatenate the values separated by commas:

    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:

    Figure 12. XML PATH solution

Conclusions

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.

First test

Statement Time (seconds) Growth Size in Mb
Truncate 0 0
Delete 23 384

Conclusion: When you need to delete all the rows, you should use Truncate.

Second test

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.

Method Time (seconds)
Cursor 90
CTE 75

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.

Third test

In our third test, we converted 1000 rows into a single variable of 1000 values separated by commas.

We used 3 methods:

  • Cursors
  • Simple T-SQL
  • T-SQL with XML PATH mode
Method Time (seconds)
Cursor 3
Simple T-SQL 0.06
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.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)

Related posts:

  1. Truncate Table Operations in SQL Server
  2. SET NOCOUNT ON statement usage and performance benefits in SQL Server
  3. Difference between SQL Truncate and SQL Delete statements in SQL Server
  4. Pseudo-Simple SQL Server Recovery Model
  5. SQL Server cursor performance problems
Performance

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. 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 writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

168 Views