.NET.Refactor()

This article is a continuation of Cursors. Previous Parts can be viewed here:

Cursors : An In Depth Look – Part 1

Cursors : An In Depth Look – Part 2 

Cursors : An In Depth Look – Part 3

Now that we have looked at query execution time, I/O and CPU statistics we are now going to look at overall system performance when running these operations.

 

One of the greatest tools for a DBA is the performance monitor. You can see what your system is doing at the exact event of a query that is being executed.

So we have executed our query once in each scenario. What happens if you execute the query thousands of times? Well, we should know what happens to our system when this happens because normal SQL servers don’t sit idle with 1 query execution every day.

In order to test this process, we can’t use the standard messages window or the execution plan. We need to use Profiler. Setting up profiler is a whole different beast and that will not be covered here.

To test this setup, I will be outputting my results to a table.

NOTE: This is normally a VERY bad thing and you should not do this. I am doing it for this example to save the time of exporting to a file and then back into a table.

I will be running these tests in the same order as the previous articles, Cursor, Set-Based and Set-Based with Temporary table.

One of the key differences in this test is I will be creating a dump table so that our cursor and set-based operations will actually do something versus just sitting there and looking pretty.

Create Setup Environment

First thing I am doing to do is create the dump table:

CREATE TABLE DumpTable (filler CHAR(200))

We also want to make sure that statistics are turned off and your actual execution plan is turned off from our previous examples.

You also want to set the nocount on, so that your message window does not get flooded with all the execution messages.

The Operations

We will be using the following cursor for your test. The cursor will process 100 rows.

DECLARE
    @keycol AS INT,
    @filler AS CHAR(200)
DECLARE C CURSOR FAST_FORWARD
    FOR SELECT
            keycol,
            filler
        FROM
            dbo.T1 WHERE keycol <= 100
OPEN C
FETCH NEXT FROM C INTO @keycol, @filler
WHILE @ @fetch_status = 0
    BEGIN
        INSERT INTO DumpTable VALUES(@filler)
        FETCH NEXT FROM C INTO @keycol, @filler
    END
CLOSE C
DEALLOCATE C
GO 1000

Here is the set-based operation we will be using (non-temporary table)

DECLARE
    @keycol AS INT

SELECT
    @keycol = keycol
FROM
    (SELECT TOP (1) keycol
      FROM dbo.T1 where keycol < 101) AS D
WHILE @ @rowcount = 1
    BEGIN
        INSERT INTO DumpTable values (@keycol)

        SELECT
            @keycol = keycol
        FROM
            (SELECT TOP (1) keycol
              FROM dbo.T1 WHERE keycol > @keycol and keycol < 101
            ) AS D 

    END
GO 1000

Here is the set-based operation with temporary table that we will be using

DECLARE @Counter INT
DECLARE @TempValue CHAR(1)
DECLARE @MaxCount INT
DECLARE @keycol CHAR(200)
Create Table #tmpCursor
    (
      id int not null
             identity(1, 1)
             primary key,
      filler char(200)
    )

CREATE NONCLUSTERED INDEX [IX_CursorDump] ON #tmpCursor ( filler ASC )

INSERT INTO
    #tmpCursor
    SELECT
        filler
    FROM
        dbo.T1
    TABLESAMPLE(100 ROWS) REPEATABLE(200)

SELECT @Counter = Count(*) FROM #tmpCursor
SET @MaxCount = 100
WHILE( @Counter < @MaxCount )

    BEGIN
        SELECT @keycol = keycol from #tmpCursor WHERE keycol = @counter
        INSERT INTO DumpTable values (@keycol)
        SET @Counter = @Counter + 1
    END

DROP TABLE #tmpCursor
GO 1000

While running profiler and performance monitor at the same time we get some interesting results.

For performance monitor I am monitoring the following statistics (These are just the main items out of 120 counters):

Logical Disk: Disk Reads/Sec

Logical Disk: Disk Writes/Sec

Logical Disk: Avg. Disk Bytes/Write

Physical Disk: Disk Read Bytes/Sec

Physical Disk: Disk Write Bytes/Sec

Logical Disk: Current Disk Queue Length

Processor: Interrupts/ Sec

SQL Server: Wait Statistics : Page IO Latch Waits

The Results

 

Okay, if you have ever run performance monitor you will know that it can be difficult to read. I am not going to go into super details on the following graphs. They are for visual representation only. The purpose here is to show you visually what is happening to your system when you run each of these operations. While some counters may be higher then others and visa versa, we are looking at how much overhead your machine has when running cursor or set-based operations.

The Cursor

The Set-Based Operation (Non Temporary Table)

The Set-Based Operation (Temporary Table)

 

As you can see, there is a HUGE difference between the activity from each of the query types. Some of the processes for the temporary table are higher (Yellow Line, which is the Page IO Latch Waits). This means that the temporary table has a higher I/O rate then CPU and Memory. The way around this is to either use a table variable (different discussion on this) or make sure your tempdb is on a different disk (it should be anyway).

Conclusion

So, have we really learned anything here? Sure we have, we have learned that cursors have a huge amount of overhead. Set-based operations don’t have as much overhead.

So, what can you decide from this? Should I use a cursor or not? Well, like it was said in the beginning. It all depends on what you need it for. Just because you have to write a bit of extra code to make a temporary table and make your "fake” cursor work that way, does not make it bad. If you are looking for performance and do not want to kill your server, I would suggest the temporary table version of the cursor.

Please feel free to comment and add your input to these articles.

This article is a continuation of Cursors. Previous Parts can be viewed here:

Cursors : An In Depth Look – Part 1

Cursors : An In Depth Look – Part 2 

This section will go into the finer details of the cursor and set-based operations. We will be looking at what is going on behind the scenes to get a better understanding of how these operations work, and to show you how to calculate performance so you can determine the best approach for your query, either cursor or set-based.

We will be looking at the STATISTICS IO and the Query Execution Plan when we execute these queries.

Before we dive into these details, we are going to explore some definitions that you need to know when looking at the Query Execution Plan.

Copyright Information:

The following definitions were taken directly from “Inside SQL Server 2005 T-SQL Programming” by Itzik Ben-Gan.

Estimated I/O Cost and Estimated CPU Cost:

The estimated part of the operator’s cost associated with that particular resource (I/O or CPU). These measures will help you identify whether the operator is I/O or CPU intensive.

Estimated Operator Cost:

The cost associated with the particular operator.

Estimated Subtree Cost:

The cumulative cost associated with the whole subtree up to the current node.

In order to not overload our results window, we need to make sure we get a TOP 1 operation when doing the initial selects. This way we get 1 result set. The results will be slightly skewed but we are only really looking for the basic I/O operations for each type of query.

Before you run these queries, run the following command AND turn on the Actual Execution Plan by hitting Control-M on your keyboard.

SET STATISTICS IO ON

Also, we need to make sure you clear your cache for each type, so we can compare cold cache against warm cache results.

The query for clearing your cache is located in Part 2 of this article.

 

The Cursor

We are first going to execute the cursor. The query will be the same as our previous cursor query from Part 2 of this article, the only difference is we are going to add TOP 1 to the operation.

DECLARE
    @keycol AS INT
   ,@filler AS CHAR(200)
DECLARE C CURSOR FAST_FORWARD
    FOR SELECT TOP 1
            keycol
           ,filler
        FROM
            dbo.T1
OPEN C
FETCH NEXT FROM C INTO @keycol, @filler
WHILE @@fetch_status = 0
    BEGIN
        FETCH NEXT FROM C INTO @keycol, @filler
    END
CLOSE C
DEALLOCATE C 

 

When you execute your query, we are going to look at the message window.

Note: your results may vary slightly, however, all in all the results should be very close.

Cold Cache:

Table 'T1'. Scan count 1, logical reads 3, physical reads 2,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0,
  lob read-ahead reads 0.

Warm Cache:

Table 'T1'. Scan count 1, logical reads 3, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0,
  lob read-ahead reads 0.

As you can see, table T1 did 1 scan, 3 logical reads and 2 physical reads total for the cold cache and the warm cache had the same except for the physical reads because the data was in the cache.

NOTE: If you do not use the FAST_FORWARD Command you will get an ADDITIONAL 3 logical reads from a ‘Worktable’

Logical reads are from memory, physical reads are from disk.

When you look at your execution plan you will see something like the following (this will be the same for both cold and warm cache).

Because we have a clustered index on the primary key and we did not specify actual key columns, we got an Index Scan. If we had specified key columns we would have an index seek (which provides better performance).

Now, the key aspect of this is if you move your mouse over the Clustered Index Scan you will see something like the following:

The scary part about this is the estimated number of rows at 1 million and the Operator Cost and Subtree Cost of 20.5966.

Set-Based Operations

We are going to run our set based operation (query taken from Part 2 but without the loop for demonstration purposes)

 

DECLARE
    @keycol AS INT,
    @filler AS CHAR(200) 

SELECT
    @keycol = keycol, @filler = filler
FROM
    (SELECT TOP (1) keycol,filler
      FROM dbo.T1 ORDER BY keycol) AS D 

SELECT
    @keycol = keycol,
    @filler = filler
FROM
    (SELECT TOP (1) keycol, filler
      FROM dbo.T1 WHERE keycol = @keycol
      ORDER BY keycol
    ) AS D

You will see that the STATISTICS IO is the same on the logical and physical reads. The best part about this is the execution plan:

Here you can see that we only have 1 estimated row versus 1 million and the Operator Cost is significantly lower then before!

I ran the same process using a temporary table and with the results I have created a table to show you the output from the Execution Plan:

 

	Cursor	Set-Based /w Top	Set-Based /w Temp
Estimated I/O	19.4965	19.4965	0.006250
Estimated CPU	1.10016	1.10016	0.000336
Estimated Operator Cost	20.5966	0.0032831	0.006586
Estimated Subtree Cost	20.5966	0.0032831	0.006586
Estimated Number of Rows	1000000	1	1

As you can see, running the cursor (even with the TOP 1 operator) will still have significant overhead on the machine. Using a set-based operation has less Operator and Subtree cost and significant estimated number of rows. Using the temporary table method, you can see that all numbers are drastically reduced.

So, just by looking at this, you can see that a Set-Based operation using a temporary table for row by row manipulation has the best impact on the machine.

 

Continue to Cursors: An In Depth Look Part 4

This article is a continuation of Cursors : An In Depth Look – Part 1

Testing Cursors and Set-Based Operations (Single Query Execution)

To help speed up the testing (not the process its self)  you can check the box “Discard Results After Execution” option in SSMS. This will ensure that when your query executes it will throw away the data and will not generate the output, hence giving you a little more time for testing.

We are first going to setup our test environment and then we will scan the same amount of data with set-based code and a cursor. We will be testing with 1 million records, each row containing a bit more than 200 bytes.

Note: Testing results may vary on the computer you are using. Hard drive and processor differences will drastically change the results of these operations.

If you would like to follow along with the examples I have step by step instructions on what you need to do. I entered these examples as I was doing them in SSMS so there should not be anything missing.

Setup the Environment

We are going to create a temporary database for these examples. This same database will be used in all parts of this article.

Create the database:

USE Master
IF EXISTS ( SELECT
                NULL
            FROM
                sys.databases
            WHERE
                name = 'CursorTest' )
    BEGIN
        ALTER DATABASE CursorTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE CursorTest
    END

GO
CREATE DATABASE CursorTest
GO
USE CursorTest

Next we are going to create a table that we will be using to scan data from..

IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1
GO
CREATE TABLE T1
    (
     keycol INT NOT NULL
                IDENTITY(1, 1)
    ,filler CHAR(200)
    )

Okay, now we are going to insert the 1,000,000 records into this table. You can do this simply by adding an insert statement with GO 1000000 after the statement (by the way, this is an undocumented feature of TSQL).

INSERT INTO T1 (filler) VALUES ('a')
GO 1000000 

When you execute this statement you will see in your messages window, after it has finished, the following message:

Beginning execution loop
Batch execution completed 1000000 times.

Now that you have a million rows, you want to apply a clustered unique index to the table. This will improve your performance greatly. Not only that, we don’t want to wait hours for a simple test like this.

CREATE UNIQUE CLUSTERED INDEX idx_keycol on dbo.T1(keycol);

Okay, we now have our data setup. We can write the code for the cursor and the set-based operation. Before we run the code we want to make sure that our cache is cleared so we do not use memory for this operation. In a production environment, your queries will most likely hit the cache, but for demonstration purposes, we want to create as much as a what-if environment as possible.

So, how do you clear the cache in SQL? Run the following statement:

DBCC DROPCLEANBUFFERS;

We will be using this statement quite a bit during this process, so if I am referring to the “Clear Cache” statement, we will be running this statement.

Let the Testing Begin

Okay, we are finally to the fun part. The actual testing. We are going to run a query against what is called “cold cache” and then run the same query against what is called “warm cache”.

Make sure you have executed your clear cache statement first.

Execute the following query:

SELECT keycol, filler FROM dbo.T1

When running the query against cold cache, it took 6 seconds on my machine to execute.

Note: If you had not enabled the “Discard Results After Execution" your query would have taken substantially longer to produce the results in your output window.

Execute the query again. You are now running this against the warm cache. This time it only took 1 second to execute on my machine. So, as you can see, cached queries will outperform non cached queries quite a bit.

The Cursor

Now we are going to test our cursor operation.

Clear your cache with the clear cache statement and write the following code. We are going to execute the code twice, once against cold cache and the other against the warm cache. Initially we are only going to look at the speed of the query execution. We will dive into the statistics a bit later.

DECLARE
    @keycol AS INT
   ,@filler AS CHAR(200)
DECLARE C CURSOR FAST_FORWARD
    FOR SELECT
            keycol
           ,filler
        FROM
            dbo.T1
OPEN C
FETCH NEXT FROM C INTO @keycol, @filler
WHILE @@fetch_status = 0
    BEGIN
        FETCH NEXT FROM C INTO @keycol, @filler
    END
CLOSE C
DEALLOCATE C

Running against cold cache on my machine this query took 27 seconds to execute. When I executed it again against the warm cache, it took 15 seconds. Using the warm cache you can see that the query exhibited the same type of speed increase based on our initial query (1 second result). However, as you can see, we have a significantly higher execution time for both the cold and warm cache for the same query. The only difference is we now have the overhead of the cursor.

If you really look at the cursor, you will notice that it is not doing anything. It is just a simple cursor that loops. There is no complex row by row manipulation happening. Imagine the overhead if we added more complex code to it.

Set-Based Operation

First we want to clear our cache (using the clear cache statement above). We then want to execute the following set-based operation query.

DECLARE
    @keycol AS INT,
    @filler AS CHAR(200) 

SELECT
    @keycol = keycol, @filler = filler
FROM
    (SELECT TOP (1) keycol,filler
      FROM dbo.T1 ORDER BY keycol) AS D 

WHILE @@rowcount = 1
    BEGIN
        SELECT
            @keycol = keycol,
            @filler = filler
        FROM
            (SELECT TOP (1) keycol, filler
              FROM dbo.T1 WHERE keycol > @keycol
              ORDER BY keycol
            ) AS D
    END

Against the cold cache, this process took 29 seconds. This was 2 seconds slower then a cursor against the cold cache! Against the warm cache it took 11 seconds, which comes out to 4 seconds faster. At this point you are probably thinking there is not much of a difference between the set-based operation and the cursor.

You may also be thinking that a cursor might be faster then a set-based operation. Well, as I pointed out initially, they can be faster in some operations. Looking at the queries we have here, they are not full of complex operations inside of the loops.

The important part here is the process is slightly cleaner then working with a cursor. Also, it is not just the speed of the execution that is important, it is what goes on behind the scenes. We are getting to that, just be patient.

Temporary Table Option (Set-Based Operation Modified)

There is another way to write this query (which is not in the book) by using a temporary table. Using a temporary table would allow you to gather specific data first versus doing key column identification on a table.

Here is how the temporary table would work.

DECLARE @Counter INT
DECLARE @TempValue CHAR(1)

CREATE TABLE #tmpCursor
    (
      id INT NOT NULL
             IDENTITY(1, 1)
             PRIMARY KEY,
      filler CHAR(200)
    )

CREATE NONCLUSTERED INDEX [IX_CursorDump] ON #tmpCursor ( filler ASC )

INSERT INTO
    #tmpCursor
    SELECT
        filler
    FROM
        dbo.T1
    TABLESAMPLE(10 PERCENT)

SELECT @Counter = COUNT(*) FROM #tmpCursor
PRINT @Counter
WHILE( @Counter > 0 )
    BEGIN
        SET @Counter = @Counter - 1
    END

DROP TABLE #tmpCursor

As you can see, I used the TABLESAMPLE at 10 percent. This will gather ~10% of the rows in the table. Which is what you are most likely going to do when working with row by row operations. Now, if you really did need to process every single row in our 1 million rows in the table, I would not recommend using a temporary table as the overhead of filling up the tempdb would be tremendous versus using the TOP operator.

Running this query on cold cache took 10 seconds and returned 97432 rows. On warm cache it took 9 seconds at 98800 rows. Not much of a difference because we are gathering a random 10% of rows. If we use the REPEATTABLE(SEED) and gather the same rows each time, cold cache took 10 seconds at 100738 rows and warm cache took 5 seconds resulting in the same rows.

As you can see, if you need to do row by row operations with a specific set of query based data, you can get substantial improvements to your set-based operations.

 

Continue to Cursors : An In Depth Look Part 3

I am breaking this article out into a few parts, only due to the length of the content. I don’t want to overwhelm you with tons of reading that you will not finish.

So, first things first:

Copyright Information:

This information on Cursors comes from “Inside Microsoft SQL Server 2005 T-SQL Programming” written by Itzik Ben-Gan. All examples on this page are taken directly from Chapter 3 of the book. Some of the examples have been modified only to eliminate processes that occurred in previous chapters of the book, such as creating test data in other tables.

Okay, now that that is out of the way, let us get to the meat of the article. As stated in my copyright information, this information is taken from Chapter 3 of the book. One thing I really like about Inside Microsoft SQL Server 2005 T-SQL Programming is that it provides great in depth knowledge on how things work. The one thing that I wish it had was the gory details on how things work.

Well, I am here to provide you with the gory details on cursors.

Overview

Now, one way to start an argument is to talk about coding standards in a room full of developers. If you ever get into this situation you know you will be spending a ton of time figuring out what is the best approach on your current argument. Eventually you will be arguing about cursors. Some people love them and some people hate them.

Cursors are something in SQL that can be a good thing and a bad thing and both at the same time.

The purpose of this article is to show you the good and the bad and also to show you how you can use set-based operations to avoid cursors. According to Itzik Ben-Gan the goal of his chapter on cursors is to “show you how to use them wisely”.

Why Avoid Cursors

Cursors conflict with the foundation of the relational model. When you use a cursor you are applying procedural logic versus using set-based logic.

This means that you write code with iterations and you focus on the “how” to deal with data.

Applying set-based logic you will typically, but not all cases, write far less code as you focus on “what” you want and not how to get it.

Why Use Cursors

Cursors have a lot of overhead involved with the row-by-row manipulation. You will need to calculate and estimate the cursor overhead of your query and make sure you cannot do your query with a set based operation before moving to a cursor. There are cases where a cursor will yield better performance then a set based operation. This article will show you how to see the performance differences between the set based operation and the cursor.

One important aspect of cursors is that they can request and assume ordered data as input. Queries can accept only a relational input, which by definition cannot assume a particular order. This is where the difference comes in when identifying the case where you may actually want to use a cursor as it might be faster. Examples of these are running aggregations and ranking calculations. The I/O cost involved with the cursor activity plus the cursor overhead might actually be lower then a set-based operation which does perform a greater amount of I/O.

What is the Cursor Overhead

For the most part, efficiently writing set-based solutions will outperform cursor-based solutions for two reasons:

1. You empower the optimizer to do what it is good at, which is generating multiple valid execution plans and then choosing from the most efficient one. When you use a cursor-based solution, you are pretty much forcing the optimizer to go with a rigid plan that does not have much room for optimization.
2. Row by row manipulation can create a ton of overhead. You can see that scanning a table with a simple query versus scanning with a cursor has a significant difference in the processing time.

 

Okay, so how do we go about “proving” that a cursor has more overhead then a set-based query? Well, we test it of course!

 

Continue to Cursors : An In Depth Look Part 2