SQL2K -> SQL 2008, EXTREMELY slow cursor fetch

Question

Post reply

SQL2K -> SQL 2008, EXTREMELY slow cursor fetch

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

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

Johan Bijnens SSC Guru Points: 135591 More actions · Answer 1

Typical questions on migration:

- did you perform post migration actions like:

-- DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;

---- kb http://support.microsoft.com/kb/923247

-- rebuild all indexes

-- update all statistics

-- dbcc updateusage

- were you able to migrate the dblevel to 100 ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bkuhnke SSC Eights! Points: 883 More actions · Answer 2

Yep, did all of that.

What's really perplexing is that a new database containing just one table with just 3500 rows, on a local sql server express instance, exhibits the same slow results when fetching a cursor. I know cursors aren't the most efficient, but this behavior seems crazy to me. Even if I don't "select" the result to the screen after each fetch, it still runs forever.

bkuhnke SSC Eights! Points: 883 More actions · Answer 3

bkuhnke

SSC Eights!

Points: 883

December 14, 2010 at 12:38 pm

#1262425

And yes, was able to go to 100.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Can you change your 2008 c.u.r.s.o.r. * cough* declaration to use TOP 1 so you have just on path and post the actual execution plan of it together with the complete table def including index definition?

Will surely help....

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]

bkuhnke SSC Eights! Points: 883 More actions · Answer 5

Here are the files. I included the plan for the query without the top 1 to show how different the plan is than when top 1 is used. Isn't it strange that it does a table scan for each fetch?

Thanks so much for your help!

Breck.

bkuhnke SSC Eights! Points: 883 More actions · Answer 6

Also, remember there are only 3500 rows in this table. It takes 14 seconds to fetch the 207 rows for salesman 15. Running the select statement on it's own of course takes less than a second.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 7

One of the things I've found is that 2K8 is a whole lot less tolerant of implicit data conversions than 2K or 2K5 was. Make sure that any joins or criteria precisely match datatypes with the datatypes in the table that you're joing to or looking up in.

--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)

Johan Bijnens SSC Guru Points: 135591 More actions · Answer 8

your table is a heap, so rebuilding your indexes may not give you the result you aim for.

It isn't using the index [ixcustsalesman] your created for column [id_salesman].

Can you define a clustered index for that table ?

e.g. on column [tmst_created] because that's a datetime column that - gut feeling - is sequential .

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

LutzM SSC Guru Points: 107049 More actions · Answer 9

Two more issues from my side:

#1: Can you post the code from inside the c.u.r.s.o.r. *cough*? In most cases such a loop can be replaced with a set based solution performing waaayyy better than RBAR.

#2: If you have to use a loop, make it at least STATIC or FAST_FORWARD (I would have expected the loop using a temp table for the fetch...).

Other than that I second Johan and Jeff.

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]

Johan Bijnens SSC Guru Points: 135591 More actions · Answer 10

There are a number of great articles on

- cursor avoidance:

-- http://www.sqlservercentral.com/articles/T-SQL/66097/

- RBAR avoidance:

--http://www.sqlservercentral.com/articles/Performance+Tuning/62278/

attached you'll find on one slide of the presentation I gave yesterday.

It was supported by the long story of what I learned from Barry and Jeff. 😉

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bkuhnke SSC Eights! Points: 883 More actions · Answer 11

The stored procedure where this problem initially came to lite has a much more involved declare, and performs about ten pages of logic for each record fetched. I realized that if I commented out all of the logic, the procedure was still dog slow and the explain showed the craziest operations on each fetch. I also of course executed the select statement from the declare on it's own and the plan looked correct and it was fast. This entire procedure runs on demand from a vb window on the sql2k instance and there has never been an issue.

I simplified it even further for posting here by declaring a single table cursor, fetching the results, and doing nothing else. 14 seconds to fetch the ~200 records and do nothing with the results. When I run this same query on the sql2k instance, it runs instantly.

Something is seriously crazy with the fetch...almost like it thinks it doesn't have enough memory to build the result set. That of course doesn't explain why it scans the table each time when an index exists on the column used in thh where clause. It uses the index (and returns the result immediately) if you execute the select statement from QA. Heck, with 3500 rows it should easily be able to scan the table 3500 times in 14 seconds, especially since there is plenty of memory and nothing else running.

bkuhnke SSC Eights! Points: 883 More actions · Answer 12

There are certain cases where cursors make a lot of sense, but I would agree never for purposes of returning a single result or a result set. They are used in our applications for things like getting a list of part numbers that may need to be orderd from a vendor, then applying 1000 lines of code to each to see how how much and from where. The overhead of the cursor is miniscule compared to the amount of logic performed for each.

But at the end of the day, even if our applications were using cursors for "bad" reasons, I wasn't expecting to have to examine and potentially re-code and test over a thousand stored procedures just to upgrade to sql 2008. Something is broken with the environment when a brand new two processor box with 16G of memory takes 14 seconds to itterate 200 records. My guess is that whatever is causing this particular behavior is also what is causing most of the other procedures to perform poorly as well.

LutzM SSC Guru Points: 107049 More actions · Answer 13

So I'll stop asking to replace the RBAR then 😉

Did you try to declare the cursors as STATIC or FAST_FORWARD like I mentionend earlier?

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]

Johan Bijnens SSC Guru Points: 135591 More actions · Answer 14

- did you prepare your migration using "Microsoft SQL Server 2008 Upgrade Advisor" http://www.microsoft.com/downloads/en/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en

It may point you to potential issues.

- I didn't find a gap today to actually test your case, but my gut feeling for the replies says it might even be a configuration issue.

-- can you pull IO-stats for stalls, ... ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bkuhnke SSC Eights! Points: 883 More actions · Answer 15

Yes, I've tried all of the cursor options to no avail. If you write a simple cursor such as mine in your sql 2008 environment and fetch through it, do you see crazy operations on each fetch, or is the overhead as on the open (as it has been in every environment I've ever worked in from DB2 to Oracle to SQL2k)?