Post reply

sp_cursorfetch

  • February 27, 2009 at 9:31 am

    #75424

    Hello all,

    I am using a 3rd party application which has been running very poorly.

    I have run a few traces, and perfmon and have pinpointed the issue.

    Basically the application is using often tens of thousands of sp_cursorfetch commands to return the results of a query within the application, resulting in many millions of reads and creating a substantial I/O bottleneck on the server, which in turn is killing operformance even more.

    I know that there are many better ways of executing commands than sp_cursoropen/fetch/close, such as sp_sqlexecute, but I have no opportunity to rewrite the method of fetching data within the application unfortunately.

    I can run a query in QA and it returns the same result in milliseconds, what the application is taking up to minutes at a time to do and this is very frustrating.

    Has anyone else had this problem, and is there any way of improving the performance of this type of execution?

    Any responses would be greatly appreciated.

    Thanks in advance for any help! 🙂

  • February 27, 2009 at 1:24 pm

    #951374

    Hi ,

    We had this issue and we rewrote the Third party App with our own. Thats how we solved the issue.. 😀

    I do not think you can do anything to improve the performance.

    -Roy

  • February 27, 2009 at 2:09 pm

    #951386

    A lot of it depends on your relationship with the 3rd party vendor. What kind of support agreement, and/or SLA's are in place?

    Sometimes(most times?) they just don't know how bad their code really is. If you decide to rewrite portions of their app, will they still support it? Will they accept it as new code in their next build/Service Pack hopefully for a discount cause you're doing their work for them? Will they fix it for you? If it's something you(your company) shelled out some cash for one would hope they would be amenable to fixing it, or at least helping you to make it run better.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • February 27, 2009 at 2:34 pm

    #951392

    The application is using server side cursors. There is probably no good reason for this, so you should talk to the software vendor about it.

    YOu might want to read this:

    http://msdn.microsoft.com/en-us/library/aa905893(SQL.80).aspx

  • February 27, 2009 at 8:12 pm

    #951474

    sp_cursorfetch isn't something the 3rd party vendor wrote into their code... it's what get's used as a result of their code (or anyone's code).

    They key here is, and I don't know a nice way to say it, the vendor wrote some crap code and you only have a few choices...

    1. Get the vendor to fix it... heh... yeah... right... like that's gonna happen.

    2. Buy new software to replace it... then, explain to the CEO how you just replaced his/her pet project.

    3. Outsource some new code (BWWWAAAA -HAAAAA!!!! Sorry, had to sneak that in).

    4. Do what I did... replace the vendor's crap code one module at a time. No one will even notice until one day when someone says, "Wow, that 3rd part software sure is good!". Then, you can feed them some very high velocity pork chops while you explain what you actually did.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 28, 2009 at 3:16 pm

    #951547

    Unfortunately CEO's buy software after successful demos running out of empty databases populated with several dummy sample items.

    Poor things, nobody told them they need to tests on a base of 3-5 year worth amount of data.

    And then, after a year o two it becomes a fault of that stupid DBA who cannot even support such a brilliant software they so smartly purchased for the company.

    In this situation any reference to their stupidity not to check the application scalability typically is taken as an assault and brings desire to get rid of this "smarty" as soon as possible.

    So, it must be a very well calculated political movement, taken at the moment of maximum desperation, it must include the steps to fix the problem (what will cover CEO's ***), the source of the problem must be carefully wrapped with some politically correct crap, and it should be addressed not only to the people responsible for the purchase, preferably they should be informed when the plan is already supported by some other people (that will mean that they also aware and agree with your description of the problem).

    If you survive this battle (by not entering the battle you lose it immediately - it's you who failed to support ;)) and if your description of the problem was clear and complete then you have a good chance to be involved in decision making process when you need to buy another piece of software.

    _____________
    Code for TallyGenerator

  • February 28, 2009 at 5:36 pm

    #951555

    Thanks for all the replies guys!

    Looks like I'll have to redesign some of the queries and put together some test results to see if they can be applied to the app, and hope that they can be used.

    I was hoping for a quick fix, but as ever, there isn't really one as an option 😉

    Sounds like quite a few people have had the same sort of nightmares with 3rd party app designs anyway!

    Probably won't be the last time I come across this :w00t:

    I think your summary is exactly right Sergiy.

    I will have to do something to sort it out, although my hands are tied in most things which would actually make a difference.

Viewing 7 posts - 1 through 6 (of 6 total)

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