open cursor takes long time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

open cursor takes long time

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-11-27 : 20:56:21

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?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-27 : 22:17:38

Why are you using a cursor, and why is it a variable...

With only 60K records, and presuming indexes..there absolutely must be a better way to do whatever it is you intend to do.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 02:25:56

you might be better off exploring set based options for achieving your requirement. If you can post some sample DDL and explain what you want, somebody here might be able to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-29 : 05:27:04

You need to explain what you are trying to do with the resultset. There must some easy methods that you think

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources