mdac 2.8 and sql 2005 cursor performace issue

[szymon.strus@gmail.com]

Hello

I have a VB6 application using classic ado (MDAC 2.8) for connecting
ms sql 2000 server. Application uses a lot of server side cursors. Now
I want to switch to ms sql 2005 server but I have noticed very serious
performance problem. Sql profiler results of execution of following
commands:

declare @p1 int
set @p1=180150131
declare @p3 int
set @p3=1
declare @p4 int
set @p4=16388
declare @p5 int
set @p5=22221
exec sp_cursoropen @p1 output,N' Select ... from ... where .... order
by ...',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

on sql server 2000:

CPU: 234
Reads: 82515
Writes: 136
Duration: 296

and on sql server 2005:

CPU: 4703
Reads: 678751
Writes: 1
Duration: 4867

Both databases are identical, the servers runs on the same machine
(Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums
I've read that Microsoft doesn't recommend using server side cursors
on sql 2005 but is there any way to increase performance to some
acceptable level?

thanks in advance

szymon strus

[Erland Sommarskog]

Re: mdac 2.8 and sql 2005 cursor performace issue

(szymon.strus@g mail.com) writes:

Both databases are identical, the servers runs on the same machine
(Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums
I've read that Microsoft doesn't recommend using server side cursors
on sql 2005 but is there any way to increase performance to some
acceptable level?

Have you tried running the queries that spawns the cursors from a query
window to compare the results? It could be an issue with the query plan.
If the database was upgraded from SQL 2000, be sure that you run
sp_updatestats, as statistics are invalidated when you upgrade the database.

What style of cursors do you use? Dynamic, keyset, static or forward_only?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[szymon.strus@gmail.com]

Re: mdac 2.8 and sql 2005 cursor performace issue

Have you tried running the queries that spawns the cursors from a query

window to compare the results? It could be an issue with the query plan.
If the database was upgraded from SQL 2000, be sure that you run
sp_updatestats, as statistics are invalidated when you upgrade the database.
>
What style of cursors do you use? Dynamic, keyset, static or forward_only?
>

Updating statistics didn't changed anything. Running the query from
the query analyzer takes about 5 seconds for both servers. Cursor from
my example is keyset-driven. Database Tuning Advisor for the specified
query created few indexes but it also hasn't speed up execution of
query.

[Erland Sommarskog]

Re: mdac 2.8 and sql 2005 cursor performace issue

(szymon.strus@g mail.com) writes:

Updating statistics didn't changed anything. Running the query from
the query analyzer takes about 5 seconds for both servers. Cursor from
my example is keyset-driven. Database Tuning Advisor for the specified
query created few indexes but it also hasn't speed up execution of
query.

Next step would be to try DECLARE CUSROR KEYSET from Query Analyzer on both
servers. The plan for a keyset or a dynamic cursor can be quite different
from the plan for a specific query.

Do you really need a keyset-driven cursor? Could a static cursor do?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[szymon.strus@gmail.com]

Re: mdac 2.8 and sql 2005 cursor performace issue

On Jun 19, 12:29 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(szymon.st...@g mail.com) writes:

Updating statistics didn't changed anything. Running the query from
the query analyzer takes about 5 seconds for both servers. Cursor from
my example is keyset-driven. Database Tuning Advisor for the specified
query created few indexes but it also hasn't speed up execution of
query.

>
Next step would be to try DECLARE CUSROR KEYSET from Query Analyzer on both
servers. The plan for a keyset or a dynamic cursor can be quite different
from the plan for a specific query.
>

Hi

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

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

[Erland Sommarskog]

Re: mdac 2.8 and sql 2005 cursor performace issue

(szymon.strus@g mail.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@sommarsk og.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