Posted by Erland Sommarskog on 06/21/07 21:48

(szymon.strus@gmail.com) writes:
> Below are duration times of execution following commands:
>
> declare test_cursor cursor <cursor_type>
> for Select ...
> open test_cursor
> fetch next from test_cursor
> close test_cursor
> deallocate test_cursor
>
> ------------------------------
> SQL 2000
>
> Dynamic 332
> Static 4997
> Keyset 263
> Forward_only 359
>
> ------------------------------
> SQL2005
>
> Dynamic 297
> Static 5286
> Keyset 299
> Forward_only 343

Interesting numbers. It's surprising to see STATIC to be so slow.
But I guess this is because all the data is copied to a worktable
in tempdb.

As I understand your example, you are only fetching one row, not
looping through the entire cursor. That could be different.

I need to confess that my poor experience with keyset-driven cursors
stems from SQL 6.5 where I regularly ran into performance problems
with the default type of cursor, which I resolved by adding
INSENSITIVE before CURSOR. I believe an insensitive cursor is the same
as a static cursor.

> So as you can see for keyset driven cursor (which is the same as from
> my example) the results are quite nice but it didn't solve the problem
> with ADO :/ I'm afraid that the only solution is to use some other
> data access technology :(

Well, ADO is a piece of crap if you ask me. But you probably get a
lot better performance if you use a client-side cursor. (Which is not
really a cursor at all, just a bunch of records in memory.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]