open cursor takes long time

Question

Post reply

open cursor takes long time

peleg

SSCrazy

Points: 2611
More actions
November 27, 2010 at 6:56 pm

#227208

i have an open cursor, on a table :
Set @TempTableCursor = Cursor Local FAST_FORWARD
For Select [Cmp],[App],[Cmd],[MsgID],[uid],[Date],[Sr],[Ct],[Rec],[E1],[Pr],[OP],[FDate],[R1],[U1],[CP],[Or],[udi2],[Se],[Se1],[Sp],[Cy],[Mt],[Ce],[Ida1],tblRec.Value
From TempTable
INNER JOIN tblRec ON TempTable.Rec = tblRec.Id
Order By [Date] ASC
the thing is, that
1)there are 60,000 rows in the table
2)most of the columns got index on them
the proble is that it takes the Cursor to open almost 2 minutes!!!!
i assume that the Order By has an impcat, can it be that the cursor has an impcat too?

Viewing 4 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What do want to do with the rows in your c.u.r.s.o.r.?

Most likely there is a solution where you don't need that kind of a loop at all.

An index on "almost every column" will only help, if there is one index starting with the [Date] column.

But as I said before: most likely you don't need it at all.

If you'd like to see a set-based solution, please provide table def and some sample data to play with together with your expected result and the business rules on how to get there. If the c.u.r.s.o.r. code is less than 200 lines, include that as well.

Regarding the best way to post sample data please read and follow the first link in my signature.

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]

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 2

I agree, more often than not cursors are the wrong approach to take when dealing with data.

Just so you can figure out what's happening though, elminate the cursor from the equation, run just the select statement and get the execution plan. See how long that takes and what the query is doing. That might offer a solution immediately. If not, you might have to start looking elsewhere, possibly your tempdb is under stress or something else. But I'd start with the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 3

Is it the cursor or the join? How long does it take to run the following?

Select [Cmp],[App],[Cmd],[MsgID],[uid],[Date],[Sr],[Ct],[Rec],[E1],[Pr],[OP],[FDate],[R1],[U1],[CP],[Or],[udi2],[Se],[Se1],[Sp],[Cy],[Mt],[Ce],[Ida1],tblRec.Value

From TempTable

INNER JOIN tblRec ON TempTable.Rec = tblRec.Id

Order By [Date] ASC

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