Sql – a “reasonable” length of time to keep a SQL cursor open


In your applications, what's a "long time" to keep a transaction open before committing or rolling back? Minutes? Seconds? Hours?

and on which database?

Best Solution

@lomaxx, @ChanChan: to the best of my knowledge cursors are only a problem on SQL Server and Sybase (T-SQL variants). If your database of choice is Oracle, then cursors are your friend. I've seen a number of cases where the use of cursors has actually improved performance. Cursors are an incredibly useful mechanism and tbh, saying things like "if you use a cursor we fire you" is a little ridiculous.

Having said that, you only want to keep a cursor open for the absolute minimum that is required. Specifying a maximum time would be arbitrary and pointless without understanding the problem domain.