Post reply

fetch from cursor seems to be much slower than original query

  • November 18, 2010 at 2:43 pm

    #231783

    We're currently trying to upgrade from version 7.7 to version 8.1 of Siebel (running on MSSQL2005).

    There is a particular query that is causing problems at the moment. When run directly against the database it returns instantly ('0 seconds') but from the application it seems to hang on fetching the data from the cursor. This is the sort of thing we're seeing in the application logs (note the 21 second delay):

    10:11:39 Begin: Fetch for Sql Cursor at 18c64d08

    10:12:00 Begin: Close Sql Cursor at 18c64d08

    I'll admit to not knowing much about cursors as we don't use them in our homegrown code. The usual tricks of looking at the QEP, missing indices reports, tuning the statement etc are not producing anything helpful. I don't really expect them to seeing as the sql runs well taken out of the application. The problem definitely seems to be inside the cursor but we're at a loss as to why this should take so long.

    Any suggestions or pointers would be very welcome.

    thanks

    Mark

  • November 18, 2010 at 2:57 pm

    #1252766

    Avoid using cursors!

    Cursors are row based queries, in simple terms SQL defines which rows are required in what can be thought of as a temporary structure. Then when you step through the cursor each row must be "fetched". This is really not very efficient compared to how SQL works best, that that is on set based queries where you fetch a bunch of data at once.

    There are almost always better ways of doing things than with a cursor, but to give pointers we would need to see your code.

    It's worth your while to Google "Cursor performance SQL 2005" and see what you get.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • November 18, 2010 at 3:11 pm

    #1252772

    Thanks Leo

    I appreciate that's how a cursor works (but given my original post thankyou for the explanatiion) and I've also seen a fair bit of advice saying don't use them.

    Unfortunately it's a third party package so changing the code isn't possible. What I'm really after is a notion of why this query, or at least the fetch part, might get so snarled up.

    All the code seems to be going through cursors so it's generally working, if not in the most elegant way.

    cheers

    Mark

  • November 18, 2010 at 3:21 pm

    #1252777

    marko61 (11/18/2010)

    Thanks Leo

    I appreciate that's how a cursor works (but given my original post thankyou for the explanatiion) and I've also seen a fair bit of advice saying don't use them.

    Unfortunately it's a third party package so changing the code isn't possible. What I'm really after is a notion of why this query, or at least the fetch part, might get so snarled up.

    All the code seems to be going through cursors so it's generally working, if not in the most elegant way.

    cheers

    Mark

    What do you mean by "at least the fetch part"?

    Do you simply run a single statement or do you actually re-run the complete c.u.r.s.o.r. *cough*?

    Would it be possible for you to post the actual execution plan for a single statement?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 18, 2010 at 3:35 pm

    #1252779

    I've got to second Lutz here. It sounds from what you said that you ran the SELECT statement of the cursor and it ran in "0" seconds, but when you use the app and it uses the cursor it takes a lot longer.

    Have you run a trace to see how the code is performing when called from the app as opposed to being called from MS? You may find the CREATE CURSOR.... section still runs efficiently, but when you step through the cursor row by row things slow down.

    This is typical cursor behaviour.

    Give a bit more info around this please.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • November 18, 2010 at 3:39 pm

    #1252780

    I'm afraid I can't get the full details at the moment as work is (or at least should be) over for the day and I would need to set up a trace and get somebody who uses the application to repeat the processing that's running slowly.

    All I have to hand is the application log from which it seems to be doing:

    prepare sql for the cursor

    execute sql statment for the cursor

    fetch the data from the cursor

    It's the last bit that has a pause of 20 seconds. But in isolation the SQL that is being run to populate the curso takes 0 seconds.

    Just to be clear, it's not me doing anything. All I want to do is find out why there seems to be this pause.

    Sorry I can't offer a bit more, I will try to post further details (ie SQL Svr's point of view) tomorrow from work.

    thanks

  • November 18, 2010 at 3:45 pm

    #1252781

    Leo

    Yes this is what I meant - sorry if I've bodged the explanations so far.

    Taken out of the application the sql is ok. Within the app there is extra time (20x) taken to go through the cursor and check the data.

    As I said to Lutz I am unable to get further trace data until tomorrow (UK) but will try to do so.

    Are you able to advise which trace counters would be most useful to break down the different stages around the cursor?

    thanks

    Mark

  • November 18, 2010 at 5:52 pm

    #1252815

    marko61 (11/18/2010)

    sorry if I've bodged the explanations so far.

    Are you able to advise which trace counters would be most useful to break down the different stages around the cursor?

    No problem, I actually did understand you correctly from the beggining.

    For tracing this use at least the events: RPC:Complete, SQL Statement Complete.

    I assume the code is dynamic SQL and not stored procs, otherwise you would use the SP: Complet and SP: Stmt Complete.

    Columns should be at least: EventClass, textdata, logonName (to filter maybe), CPU, Reads, Writes, Duration, SPID (required), databasename (to filter), start & end Times.

    Filer on database name, select Ignore rows without data. If you can try run it during a time when no one else is using the database otherwise you may have too much data to wade through.

    As an aside...

    Typically what happens is the application is doing a whole bunch of stuff the DBA (you) aren't aware of. I recently did an investigation like this and found a "poor performing function" was giving a 0 duration, but when we traced it, the 3rd party app was iterating through the function 250 000 times for one query, and had a timeout of 10 seconds. Even if the query was only taking 10000th of a second it would still take 25 seconds.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • November 19, 2010 at 3:32 am

    #1252925

    Leo

    That's very helpful thankyou.

    I'm speaking to the development team now and getting access sorted so I can run the activity myself.

    I'll set up a trace with the events and counters you've advised and see what I can find.

    Cheers

    Mark

  • November 19, 2010 at 4:58 am

    #1252956

    Leo & Lutz

    I'd just got as far as collecting some trace data (which showed 31M rows being read instead of a couple) when the developer arrived and said they'd found where the vendor's code differed between releases. They've pasted the old version of the code into the application and everything works fine now. Progress eh?

    Thanks again for your help on this, I'll keep a copy of your advice on trace counters for next time we get one of these.

    Mark

  • November 19, 2010 at 1:52 pm

    #1253297

    I'm glad the source of the performance drop has been identified. 😀


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 21, 2010 at 6:36 pm

    #1253556

    Leo.Miller (11/18/2010)

    Avoid using cursors!

    Cursors are row based queries, in simple terms SQL defines which rows are required in what can be thought of as a temporary structure. Then when you step through the cursor each row must be "fetched". This is really not very efficient compared to how SQL works best, that that is on set based queries where you fetch a bunch of data at once.

    There are almost always better ways of doing things than with a cursor, but to give pointers we would need to see your code.

    It's worth your while to Google "Cursor performance SQL 2005" and see what you get.

    Cheers

    Leo

    You missed the part where the OP said... 😉

    I'll admit to not knowing much about cursors as we don't use them in our homegrown code.

    --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)
    Intro to Tally Tables and Functions

  • November 21, 2010 at 6:40 pm

    #1253557

    marko61 (11/19/2010)

    Leo & Lutz

    I'd just got as far as collecting some trace data (which showed 31M rows being read instead of a couple) when the developer arrived and said they'd found where the vendor's code differed between releases. They've pasted the old version of the code into the application and everything works fine now. Progress eh?

    Thanks again for your help on this, I'll keep a copy of your advice on trace counters for next time we get one of these.

    Mark

    If you can do that, then talk with the vendor and get them to officially fix the problem. Right now, you're likely in violation of any support agreement you may have. If you have no support agreement then, like I said, "if you can do that", rewrite the code to get rid of the cursor. Just remember that you'll need a similar change the next time you upgrade the code (which is why you really need to get the vendor to improve the code).

    --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)
    Intro to Tally Tables and Functions

  • November 22, 2010 at 1:55 am

    #1253622

    Jeff

    I believe from the development team (although thankfully I don't have to deal with it myself) that the code changed is in an area where customisation is allowed. The problem however isn't caused by the customisation as they tried reverting to the out of the box functionality in an attempt to get it working - it didn't solve the issue.

    I appreciate your warning and agree that changes to the main code would invalidate our support contract but I think in this case we're ok. As I understand it, the issue is being reported back to the vendor (along with others). Whether anything happens from that we will have to wait and see.

    thanks

    Mark

  • June 7, 2011 at 1:54 am

    #1334819

    Hi,

    We are facing a similar kind of issue, can you let me know what was the root cause for this issue.

    Thanks

    Chandra

Viewing 15 posts - 1 through 15 (of 18 total)

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