June 3, 2009 at 10:25 am
Hi experts,
I facing problem with one application they are complaining about performance ..
Today I trace the db and found this information
declare @p1 int
set @p1=180150005
declare @p3 int
set @p3=2
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'SELECT * FROM pep.ZSDI38_SHIPMENT WHERE SID = ''80168840'' AND MANDT = ''300'';',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
Does this code has any impact on Performance ?
Let me know your suggestions.
Thanks
ichbinraj
June 3, 2009 at 4:02 pm
Hi chbinraj,
I hate this stuff. These are "API Cursors" and are very poorly documented. They are the mechanism that ODBC, OLEDB, and other providers use to implement database accessors for developers. And, yes they can be major performance issues sometimes. This link to Source Forge (http://jtds.sourceforge.net/apiCursors.html) provides some documentation for the calls you will see in profiler.
in profiler add most of the Cursor Events class to your trace (Open, Close, Execute, etc) and you will find that the snippet you posted is usually followed by anywhere from a few (3 or 4) to very many (I have seen 100 or more) cursor events. In some cases the application is holding locks on database objects all the way from the sp_CursorOpen until the cursor close (or longer).
You can enable Transaction Class events to catch the start and end of the transaction.
I generally have to get with the developer(s) to find out exactly what they are doing. I don't know how to read all of the cursor events so it is easier to get into the C++ and find the base query.
HTH.
Ray
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply