What is a "reasonable" length of time to keep a SQL cursor open?

Question

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?

Answer 1

transactions: minutes.

Cursors: 0seconds maximum, if you use a cursor we fire you.

This is not ridiculous when you consider we are in a high availability web environment, that has to run sql server, and we don't even allow stored procs because of inability to accurately version and maintain them. If we were using oracle maybe.

Answer 2

I'm probably going to get flamed for this, but you really should try and avoid using cursors as they incur a serious performance hit. If you must use it, you should keep it open the absolute minimum amount of time possible so that you free up the resources being blocked by the cursor ASAP.

Answer 3

Generally I agree with the other answers: Avoid cursors when possible (in most cases) and close them as fast as possible.

However: It all depends on the environment you're working in.

• If it is a production website environment with lots of users, make sure that the cursor goes away before someone gets a timeout.
• If you're - for example - writing a "log analyzing stored procedure" (or whatever) on a proprietary machine that does nothing else: feel free to do whatever you want to do. You'll be the only person who has to wait. It's not as if the database server is going to die because you use cursors. You should consider, though, that maybe usage behaviour will change over time and at some point there might be 10 people using that application. So try to find another way ;)

Answer 4

@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.

Answer 5

@ninesided: performance issues aside, it's also about using the right tool for the job. Given the choice to move the cursor out of your query into code, I would think 99 times out of 100 it would be better to put that looping logic into some sort of managed code. Doing so allows you to get the advantages of using a debugger, compile time error checking, type saftey etc.

My answer to the question is still the same, if you're using a cursor, close it ASAP, in oracle I'd also be trying to use explicit cursors.