November 29, 2005 at 3:48 pm
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 serverside 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...)
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...
Thanks alot for the help.mj
November 29, 2005 at 4:25 pm
I'm so sorry - forgot to post the statement. here we go:
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 @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,mj
November 30, 2005 at 4:04 pm
MJ,
Why are your creating a cursor ? This is a stored procedure, it is executed on the server anyway, would not just a regular select statement do?
I think maybe I did not notice something or you have other reasons to create cursor.
Yelena
Regards,Yelena Varsha
November 30, 2005 at 8:36 pm
Please, read the initial posting - thi sis 3rd party java app! The statement and the cursor type come thru the app - I get it into the trace. When I run the query from QA I do not have problem and the query take 2-3 milliseconds. The problem is when I run the cursor satement - then the Index seek in the original query becomes Index scan and the time is 1.40 min.
Thanks,
mj
December 1, 2005 at 4:40 am
perhaps it is not the statement itself, but the connection settings set by the JDBC driver that mean that the statement cannot use an index - things such as ANSI_NULLS, etc can mean that you cannot use things such as indexed views or views on computed columns if set incorrectly.
December 1, 2005 at 9:13 am
MJ,
I did realise you have no control over application code, but I though you may have control over the stored procedures that app is using. But now I realise that the code you posted is invoked from the application not from the upper-level stored procedure.
The Index Seek becoming Index Scan taking a long time may mean that the indes statistics does not really work. Try the following:
1. Do Update Statistics or sp_updatestats and the clear the buffer by DBCC FREEPROCCACHE or restart SQL Server. The first time after restart all SPs, views and procedures work slow to cache execution plans and compile stuff.
2. Verify that AutoCreate Statistics and Auto Update Statistics is selected in the database properties
3. Verify that index exists and it is up-to-date by DBCC SHOWCONTIG. Use DBCC DBREINDEX to refresh indexes or create appropriate indexes if needed
To think of it, do it in the following order: #3, #2 and #1
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply