fetch from cursor seems to be much slower than original query

Question

fetch from cursor seems to be much slower than original query

marko61

Mr or Mrs. 500

Points: 559
More actions
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:39Begin: Fetch for Sql Cursor at 18c64d08
10:12:00Begin: 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

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

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

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

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.

marko61 Mr or Mrs. 500 Points: 559 More actions · Answer 2

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

LutzM SSC Guru Points: 107049 More actions · Answer 3

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]

Leo.Miller SSChampion Points: 13051 More actions · Answer 4

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.

marko61 Mr or Mrs. 500 Points: 559 More actions · Answer 5

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

marko61 Mr or Mrs. 500 Points: 559 More actions · Answer 6

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

Leo.Miller SSChampion Points: 13051 More actions · Answer 7

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.

marko61 Mr or Mrs. 500 Points: 559 More actions · Answer 8

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

marko61 Mr or Mrs. 500 Points: 559 More actions · Answer 9

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

LutzM SSC Guru Points: 107049 More actions · Answer 10

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]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

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)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

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)

marko61 Mr or Mrs. 500 Points: 559 More actions · Answer 13

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

kv.chandrasekhar Valued Member Points: 54 More actions · Answer 14

Hi,

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

Thanks

Chandra