What is a “reasonable” amount of time to keep the SQL cursor open?

Question

In your applications, what is the “long time” for a transaction to open before committing or retreating? Minutes? Seconds? Clock?

and on which database?

Answer 1

@lomaxx, @ChanChan: as far as I know, cursors are a problem only for SQL Server and Sybase (T-SQL variants). If your database of choice is Oracle, then cursors are your friend. I have seen a number of cases where using cursors actually improved performance. Cursors are an incredibly useful mechanism and tbh, saying things like “if you use the cursor that we launch you” are a bit ridiculous.

, , . .

Answer 2

, , , , . , , , ASAP.

Answer 3

: .

: 0, , .

, , - , SQL-, - . , .

Answer 4

, : , ( ) .

: , .

• - , , , - -.
• , , " " ( - ) , : , . , . , , . , , , , - 10 , . ;)

Answer 5

@ninesided: performance issues, as well as using the right tool for the job. Given the choice to move the cursor from your request to the code, I would think that 99 times out of 100 it would be better to put this loop logic in some kind of managed code. This allows you to take advantage of using the debugger, compile time error checking, type saftey, etc.

My answer to the question is the same, if you use the cursor, close it as soon as possible, in oracle I will also try to use explicit cursors.