SQL Server Performance Forum – Threads Archive

server side cursor problem

I need help to troubleshoot a server side curson issue. This is a 3rd party java app running on SQL2000 Ent using MS Jdbc driver. Most of the statements are OK, but not this one. The trace shows that it opens server side cursor and it takes over 40 sec to complete. There are other queries opening the same type cursor – exact/fetch but they run fast… When run from the QA the exec plan is OK and it’s fast. (but the bind variables are taken out…)Also, when the server side cursor is disabled, the app runs with no problems. I have checked all indexes on this particular table and rebuilt them – no result…
Please, give me some guidlines how to troubleshoot this problem – we cannot touch the application as it’s a 3rd party and cannot use another driver…
Here is the statement: declare @P1 int
set @P1=1
declare @P2 int
set @P2=4
declare @P3 int
set @P3=1
declare @P4 int
set @P4=-1
exec sp_cursoropen @P1 output, N’SELECT attribute.attr_id AS attr_id, attribute.dsrc_acct_id AS dsrc_acct_id, attribute.attr_type_id AS attr_type_id, attribute.entity_id AS entity_id, attribute.attr_value AS attr_value, attribute.attr_dt AS attr_dt, attribute.valid_from_dt AS valid_from_dt, attribute.valid_thru_dt AS valid_thru_dt, attribute.sys_create_dt AS sys_create_dt, attribute.sys_lstupd_dt AS sys_lstupd_dt, attribute.sys_delete_dt AS sys_delete_dt, attribute.qc_stat AS qc_stat, attr_type.attr_type AS attr_type FROM attribute, attr_type WHERE attribute.attr_type_id = attr_type.attr_type_id AND dsrc_acct_id = 127909 AND attr_id <= 127909 AND (sys_delete_dt IS NULL OR sys_delete_dt > @P1) ORDER BY attr_type_id, attr_value’, @P2 output, @P3 output, @P4 output, N’@P1 datetime ‘, ‘Nov 7 2005 6:31:10:000PM’
select @P1, @P2, @P3,@P4 Thanks alot for the help.mj

Server side cursors can be a pain when returning many rows. You often get the rows returned one by one and it could take a second for each row sometimes. Try running without server side cursors or see if you can change the number of rows returned in each batch. Don’t know about the jdbc driver but you should be able to specify this parameter on your recordset with something like .rs.Batchsize=20 to return 20 rows at a time instead of 1.

I cannot change the app – it’s 3rd party product.
Please, advice, if there’s something I can do on database level to slove the problem.
How can I troubleshoot this situation?
Thanks a lot,mj

I don’t know of any way to solve it on database level. It’s a client side problem.

How about new indexes, could you do that?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The query is already using the appropriate indexes. If I run it trough QA with server side cursors turned off – runs for 2 sec – usually return 1 to 2 rows.
If I turn the cursor ON then it takes 40-60 sec… It still using the same indexes, etc. I thought that may be this is a bad index and rebulit them all… It did not help.
Thanks, Mj

I made some progress today. If I chnage the cursor type in @p2 to 16 from 4 – this is the cursor type, it runs for 4 milliseconds. Also, the 4th parameter from 256 – default to 3-4 – cuts the time 60%. What else I can do on the database side to i prove the behavior of the application created cursor. The problem is that we are using MS jdbc – if I change the drive to Datadirect jdbc all these problems disappear! But we do not have license… too much money!
Please, help.mj

If the code is in a stored procedure try adding the WITH RECOMPILE option incase it’s an issue with bad query plans that are cached that works good for some parameters but not others.

]]>