September 21, 2005 at 11:43 pm
I am running a query on SQL Server 2000 Service Pack 3 in Query Analyser. It's performance in this environment is fine - a matter of millisecs. However when I request the very same SQL from within my client application the performance is terrible. I then altered the method by which I connect to the db from the client and the performance returned to the original level experienced in Query Analyser. So I had a situation where I can make the same SQL request from the same client perform either badly or well depending on a connection setting
I then decided to run SQL Profiler against both the slow and fast options to try and isolate any differences. The profiler suggests things are exactly the same, except, in the slow option, an sp_cursorfetch is being called while an sp_executesql is being called in the fast option. In both case the same 59 rows are returned.
Further to that, the profiler tells me that the sp_executesql performs 5318 reads and has cpu time of 485 m/secs, while the sp_cursorfetch with the very same sql performs 2.9 million reads and has a cpu time of 20750 m/secs. Is the execution plan different from one case to another?
Can anybody explain to me what issues would make the very same query run badly via sp_cursorfetch but still be lightening fast via sp_executesql and also what I might try to improve the poor performance?
Thanks
John
September 22, 2005 at 1:24 pm
see http://www.sqlteam.com/item.asp?ItemID=11842
on more info about ado properties.
Basically sp_cursorfetch is called for each row of your recordset, causing a roundtrip (network delay (100ms?) -> 59*100ms = 5.9 sec).
Plus it use a cursor on the server which is really bad , and to avoid unless absolutely necessary, for your performance.
sp_cursorfetch is also undocumented in the books online.
http://jtds.sourceforge.net/apiCursors.html
sp_executesql fetches all rows at once (ideally 1 roundtrip -> 100ms to fetch all data)
plus sql server caches its Execution Plan in case the query is called again with the same parameters.
March 4, 2008 at 6:06 am
Jo,
I don't think that really answers the question. Although you are correct in that the sp_cursorfetch does a round trip for each row, I believe the duration shown in a trace is time spend inside SQL server and does not measure any network travel.
John,
I am experiencing a very similar problem at our site where a simple query with a unique identifier returning only one row executes 6 IOs in Query Analyser but using a sp_cursoropen followed by a sp_cursorfetch reports 1.5 million IOs as well as high CPU usage. The entire table only has 140000 rows and is less then 300 meg so I can't fathom 1.5 million IOs even with a table scan. Incidentally the execution plan generated by Query Analyser shows an Index Seek returning 1 row as expected.
I am now putting all the facts together and sending them to Microsoft since the application is 3rd party and we cannot make any changes. Regardless, I don't think this is an application issue. I do understand the overheads of a cursor but not 6 to 1.5 million.
I'd be interested to hear if anyone else is experiencing simolar problems or even better, has an explanation.
Incidentally, this application is also running against SQL2000.
cheers
March 6, 2008 at 8:13 pm
I've seen that that behavior before and it was an application issue;in query analyzer it took less than a second to return the results, from the application it took over 5 minutes. A profiler trace showed many sp_cursorfetch calls.
ADO settings in the application like CacheSize, CursorType, LockType and CursorLocation determine the number and type of API server cursor calls.
I found the link on the other post helpful in interpreting the sp_cursoropen and sp_cursorfetch calls in the trace at the time.
October 2, 2008 at 2:24 pm
Has anyone found a solution to this? I am experiencing the same issue against a SQL 2005 database. When running a query that returns 0 records takes 15ms when run from SSMS and 3 logical reads. The application uses cursors and wraps the same SQL I just ran from SSMS in a cursor call. However, the sp_cursorfetch call results in 4.4 million reads.
exec sp_cursorfetch 180150129,2,1,1
Any ideas?
October 2, 2008 at 2:34 pm
Alin Winters (10/2/2008)
Any ideas?
Two options that would probably solve that. One, use a stored procedure instead of sending the query from the client side application. Two, change the type of cursor to static/read-only.
What does the sp_cursoropen call look like in your trace? You'll be able to tell interpret what type it is from this site: http://jtds.sourceforge.net/apiCursors.html
October 2, 2008 at 3:49 pm
Todd Engen (10/2/2008)
Alin Winters (10/2/2008)
Any ideas?
Two options that would probably solve that. One, use a stored procedure instead of sending the query from the client side application. Two, change the type of cursor to static/read-only.
What does the sp_cursoropen call look like in your trace? You'll be able to tell interpret what type it is from this site: http://jtds.sourceforge.net/apiCursors.html%5B/quote%5D
Along those lines - if it's a SQL to SQL call - using OPENQUERY to execute the SP will "encourage" the SP to be executed on the remote server (where it lives), instead of trying to execute the statement locally and update remote resources (which is what you seem to be doing).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2008 at 11:36 am
Unfortunately we don't have control over the way the client app sends the requests to the DB. We can't change it from cursors to stored procs. I can do some research and see if we have control of the *type* of cursors it uses (like static, read-only, etc).
I don't see an sp_cursoropen call. I see an sp_cursorprepexec call, followed by an sp_cursorfetch call, then a CursorClose event.
declare @p1 int
set @p1=1073741887
declare @p2 int
set @p2=180150127
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(15)',N'SELECT statement here....
OPTION (FAST 40)',@p5 output,@p6 output,@p7 output,N'1-XXXX'
select @p1, @p2, @p5, @p6, @p7
-----------------------------------------------------------------------------------------------
Next call is to:
exec sp_cursorfetch 180150129,2,1,1
October 3, 2008 at 11:43 am
In the profiler trace, are you capturing all event types for the Cursor event class?
October 3, 2008 at 12:00 pm
Yes. All of them.
October 3, 2008 at 12:02 pm
I was just wondering if somehow the code running inside the cursor was ignoring any indexes already present on the table. I can't verify that it is doing a table scan, but I am making a guess that it is doing so based on the high number of reads.
October 20, 2009 at 4:34 am
SSC Rookie
I'm experimenting the very same trouble with several queries. For what I see in your post, I assume that you had also this problem with Siebel, am i right? Did you find a any way to solve this problem?
CyclingRabbit (10/3/2008)
Unfortunately we don't have control over the way the client app sends the requests to the DB. We can't change it from cursors to stored procs. I can do some research and see if we have control of the *type* of cursors it uses (like static, read-only, etc).I don't see an sp_cursoropen call. I see an sp_cursorprepexec call, followed by an sp_cursorfetch call, then a CursorClose event.
declare @p1 int
set @p1=1073741887
declare @p2 int
set @p2=180150127
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(15)',N'SELECT statement here....
OPTION (FAST 40)',@p5 output,@p6 output,@p7 output,N'1-XXXX'
select @p1, @p2, @p5, @p6, @p7
-----------------------------------------------------------------------------------------------
Next call is to:
exec sp_cursorfetch 180150129,2,1,1
October 20, 2009 at 7:22 am
Yes, the application was Siebel. The difficulty I was having was reproducing the query behavior within a cursor. The execution plan used by the cursor was different than the execution plan used by firing the query using SSMS.
Frank McBath was instrumental in providing articles and resources to work with SQL Server, Siebel and cursors. Check out the resources below:
July 19, 2010 at 5:01 am
What changes did you make to the way application connects to the DB?....because Even I am facing same sort of issue.
April 14, 2011 at 3:28 pm
Is this still an issue for you?
Thank you,
Phillip Cox
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply