SQL Server Cursor performance - dependece of cursor type and ORDER BY clause

Question

Hi!
Today I encountered an intresting problem.
I have table with 2 000 000 rows of data. There are three indexes. One cluseter index [Id] and two non-clustered index [Date] and [No].
I have opened SQL Server Cursor to fetch data from DB. It was Dynamic Cursor.

In first case I had statement like this:
SELECT * FROM TABLE1 ORDER BY [Date],[Id]

This statement executed after few miliseconds.

In second case I had statement like this:
SELECT * FROM TABLE1 WHERE [No] like 'ABC%' ORDER BY [Date],[Id]

This statement executed after 40 seconds.

Then I deleted ORDER BY clause from statement
SELECT * FROM TABLE1 WHERE [No] like 'ABC%'

This statement executed after few miliseconds again.

Why is ORDER BY making so difference in performance?

In addition, when I changed cursor type to Keyset, this problem not occured.

Answer 1

Oh, I did explain: I told you to stay from all other cursor types than static. But, OK, a little elaboration can be in place.

STATIC is very simple: the query is executed when you run OPEN on the cursor and the result is stored in a worktable in tempdb, and the cursor is served from this table. A concept which is very simple to understand.

KEYSET is similar, but only the keys are stored in the work table. Remaining values are taken from the live tables. This makes the cursor slower - and what is the point? I don't know.

DYNAMIC - the cursor is evaluated on every FETCH, and if this sound slow to you, I don't know what that sounds slow to you. It can lead to real performance disasters. And sometimes infinite loops, I believe.

FAST_FORWARD - I have never been able to understand what it does. Books Online says "like dynamic with optimizations". I don't think I have ever seen a FAST_FORWARD cursor that performed a lot worse than a static cursor, but I'm not using something I don't understand.

Depending on the query, you may get a different cursor type than what you ask for. STATIC is the only one that can work with any query.

Also, keep in mind that cursors is something you don't have reason to use very often. Set-based statements is almost always faster.

Answer 2

Why are you using a cursor at all? And if you use a cursor, why a dynamic cursor? The only cursor type that make sense is a static cursor.

The ORDER BY matters a whole lot, since you ask for a sorted output. If you say only:

SELECT * FROM TABLE1 WHERE [No] like 'ABC%'

SQL Server can use the non-clustered index on [No]. If you add an ORDER BY, there is the question, use the index on Date to look up all rows to see which match, so that data does not have to be sorted? Or search on No and then sort on Date? For a normal query, this is a no-brainer - the latter is the way to go. But for a dynamic cursor... But as I said, stay away from those.

Answer 3

Hi, thanks for reply.
In this example I'm using cursors because I want to understand how it works and I want to study the effect of cursor type on performance.

You explained how getting rid of the ORDER BY clause has improved query performance but still I don't understand why after changing cursor type to KEYSET, this three queries are completed after the same amount of time (few miliseconds).

Can somebody explain this? I didn't find any anserws in docs/web.
This is the first time when I have to ask somebody after spending few hours of searching information. :)

Answer 4

Thanks for explainig that.