:::: MENU ::::

Cursor performance

Cursors, DBA Interview questions, dbDigger, Performance Tunning and Optimization

Cursor performance

Question: Of course, we all know that cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can’t be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.

So, suppose you find yourself in a situation where you do need a cursor – which cursor option must be specified in order to assure maximum performance?

Answer: STATIC

Explanation: The default options are actually the slowest, since they create a dynamic cursor. In spite of its name, FAST_FORWARD does not result in the fastest performance; STATIC has been shown to be faster. Specifying FORWARD_ONLY or READ_ONLY on a static cursor will not affect performance.

Ref: DECLARE CURSOR

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Consult us to explore the Databases. Contact us