mdac 2.8 and sql 2005 cursor performace issue

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

Re: mdac 2.8 and sql 2005 cursor performace issue

(szymon.strus@gmail.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@sommarskog.se

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

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.

Re: mdac 2.8 and sql 2005 cursor performace issue

(szymon.strus@gmail.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@sommarskog.se

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

Re: mdac 2.8 and sql 2005 cursor performace issue

On Jun 19, 12:29 pm, Erland Sommarskog wrote:
> (szymon.st...@gmail.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

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

Re: mdac 2.8 and sql 2005 cursor performace issue

(szymon.strus@gmail.com) writes:
> Below are duration times of execution following commands:
>
> declare test_cursor cursor
> 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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx