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