Cursor order by Problem

Question

Cursor order by Problem

Snehal-162112

SSC Enthusiast

Points: 144
More actions
September 22, 2004 at 12:13 am

#89371

Hi All,
I have a select Statement with multiple table joins. The statement works fine, but when i convert the statement to a cursor. And when i loop through the coursor it takes considerable time.
I have a order by clause in the select statement. If I remove that order by clause and create a cursor on the statement, and then when i loop through the cursor, it runs as fast as the select statement.
So it seems order by clause is creating the problem. The order by clause is not on a primary key.
Can anyone explain me why the select statement with order by clause works fine. but the same statement when converted to cursor and then loop through the cursor take considerable time.
Thanks and Regards,
Snehal

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

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

Bert De Haes Hall of Fame Points: 3078 More actions · Answer 1

Can you tell us what type of cursor you use ? global/local, read-only, .... I suppose a cursor that is NOT read-only has more performance problems with an order-by then a read-only one ...

Snehal-162112 SSC Enthusiast Points: 144 More actions · Answer 2

Hello i am not mentioning any type while defining the cursor. So i suppose it will be default type i.e. read only

Bert De Haes Hall of Fame Points: 3078 More actions · Answer 3

The default is not read-only. In Bol, you will find for the read-only option :

"READ ONLY

Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated."

I would propose to try the read only option, or even a FAST_FORWARD cursor ...