mdac 2.8 and sql 2005 server side 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

I have the same problem, I noticed performance problem too with our application and the ms sql 2005. (but we already have customers with sql 2005 server (and the express edition)).

In the SQL Server Profiler I find this:

declare @.p1 int

set @.p1 = 180160609

...

exec sp_cursoropen @.p1 output, N'Select * from ...'

and some rows down

exec sp_cursorfetch 180160609,32,1,1

At this row this query takes about 8 o more sec to execute.

Somebody can help?

Thanks in advance

(sorry about my english)

Darío

|||

Moving this thread to T-SQL so that we can see if there is a better way to state the T-SQL statements or to improve efficiency through server settings.

John