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

cursorssql

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.

Related Solutions

Sql – the best way to paginate results in SQL Server

Finally, Microsoft SQL Server 2012 was released, I really like its simplicity for a pagination, you don't have to use complex queries like answered here.

For getting the next 10 rows just run this query:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned

Key points to consider when using it:

ORDER BY is mandatory to use OFFSET ... FETCH clause.
OFFSET clause is mandatory with FETCH. You cannot use ORDER BY ... FETCH.
TOP cannot be combined with OFFSET and FETCH in the same query expression.

Sql – the difference between SQL, PL-SQL and T-SQL

SQL is a query language to operate on sets.

It is more or less standardized, and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.
PL/SQL is a proprietary procedural language used by Oracle
PL/pgSQL is a procedural language used by PostgreSQL
TSQL is a proprietary procedural language used by Microsoft in SQL Server.

Procedural languages are designed to extend SQL's abilities while being able to integrate well with SQL. Several features such as local variables and string/data processing are added. These features make the language Turing-complete.

They are also used to write stored procedures: pieces of code residing on the server to manage complex business rules that are hard or impossible to manage with pure set-based operations.

Best Solution

Related Solutions

Sql – the best way to paginate results in SQL Server

Sql – the difference between SQL, PL-SQL and T-SQL

Related Question