Post reply

Slow query with server side cursors

  • October 27, 2006 at 11:31 am

    #115243

    I’m troubleshooting a slow running application that is using server side cursors.  When I run a trace, most of the queries are fast except for two; they each take around 2.5 seconds to run.  They only make three round trips to the server (1 cursoropen, 1 cursorfetch and 1 cursorclose).  The only difference I can find is the number of reads they perform is far more then any of the other queries (~6000 reads).  The tables have been indexed and the queries are using the indexes.  When I pull either query out and run it in Query Analyzer it runs in less than 1 second.  I think that it has to do with the server side cursor and processing all of the reads through the cursor. 

     

    What I need to know is how do I explain this to the project manager, or if you think the problem is somewhere else, what is the problem?

     

    (SQL 2000, SP4)

     

    Thanks,

     

    Tom

  • October 30, 2006 at 5:55 am

    #668685

    I suppose the Project Manager knows nothing about SQL Server?

    I try to never use cursors unless there is no other way or I'm attempting to stress test a server , usually to do with disk throughput.

    There are some read only fast forward cursors ( firehose?? ) which perform better. I'm not sure I understand your comparision though - are you running the whole cursor in QA or just the query? and is your QA on a client so you're comparing like for like?

    I suspect, and it's a guess , that the slowness maybe the client accepting the data. As I can't simulate this off the cuff so to speak, you should be able to check with profiler by comparing the batch completion time vs the sql completion time ( sorry I can't remember exactly ) maybe another reader will help ? However you can compare the times in profiler to see if it's the return to the client that's the problem. I did it a few years ago to show a middle tier server was running slow.

    As an aside 6k i/o at 1 i/o per loop in a cursor will be slow ( if that's what's happening ) and i'd say 2.5 seconds is pretty good ( if that's what it's doing )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply