slow cursor / sp_cursorprepexec

[oracleSQLdba]

I've the following which returns as slow as molasses. I've searched the msft kb and it seems to be saying the oledb or ado settings for the cursor are not set to the default. How / where can I check this?

Many thanks.


set @P1=7
declare @P2 int
set @P2=180150011
declare @P3 int
set @P3=16
declare @P4 int
set @P4=1
declare @P5 int
set @P5=0
exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 varchar(8000),@P2 varchar(8000),@P3 int,@P4 varchar(8000)', N'SELECT t2.INSTANCEID , t2.INSTANCEITERATION FROM WMPROCESS t2 WHERE t2.PROCESSKEY LIKE @P1 AND t2.PARENTINSTANCEID = @P2 AND t2.PARENTINSTANCEITERATION = @P3 AND t2.PARENTSTEPID = @P4 ORDER BY t2.AUDITTIMESTAMP DESC', @P3 output, @P4 output, @P5 output, '%proj.21_PDTYPE_I_MODULE/doc.263/object.xml_1.0%', '80fa3fb0892911dbb145ee122ae2307d', 1, 'N12'
select @P1, @P2, @P3, @P4, @P5

 

[mrdenny]

Those connection settings would be in the calling client which is making the database call.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[oracleSQLdba]

We're halfway there. Where in the calling client? Through ODBC settings, a registry hack, somewhere else??

 

[Catadmin]

The calling client is the program that is sending the query to the database. Whether it is Oracle or a third party app accessed by your users, etc.

What software is using the cursor? That's the place you need to look.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLSister]

What are you doing inthe cursor and why on earth are you using a cursor to begin with? Cursors are extremely poor performers.

Questions about posting. See faq183-874

 

[oracleSQLdba]

~I'm~ not doing anything in a cursor. The application was written using a server side ado or ole-db cursor, I believe.