"Cursors Run Just Fine"

This is a controversial topic, but I'll go on the record to agree with you 100% Adam. Can a cursor be used and still have decent performance? Yes. Is there a faster way to perform the same function using set-based logic or a SQLCLR cursor? Absolutely.

The things that troubles me with debates over cursors vs set-based logic are:

1) Often the debate crosses platforms. Other database platforms have focused on tuning the cursor engine in ways that SQL Server just never did.

2) A lack of understanding of how set-based logic works (or how powerful it is)

3) Cursor vs Set is only a discussion based on performance, often in an artificial context (such as the TPC tests - yes, I believe that they artificial and not representative of real-world complexities).

4) "The default result set" (I still find myself referring to it as the 'firehouse cursor') is a fast forward read-only cursor anyway. Not quite true. Actually declaring a cursor itself creates a minor performance cost in and of itself.

I would much rather discuss the use/misuse of cursors or the benefits/complexities of set-based operations within a very specific business context. In my opinion, every benchmark is dependant on the very system it is run on.

There might be very fine-grain differences between placement of data into cursor variable memory addresses & memory addresses used during set based processing, but I doubt these will amount to the differences you're seeing. You mentioned context switching also, but how exactly does using a cursor create more context switching than using a set based expression? I've never measured that specifically but I can't think of any reason why one would create more context switching than the other.

Whilst I agree that cursors are measurably slower than set based processing for simple statements such as the example you've provided, the game changes as the complexity increases. This is because the more complex an individual set based expression is, the more subject it is to optimisation errors. Cursors provide a useful technique to control internal processing steps without relying on optimisation. Some developers take the approach of breaking down complex statements into multiple statements, employing temp tables etc, but cursors have the advantage of not having to spool to temp tables etc.

I suspect this might explain why cursors are being used in tpc-e - the overhead associated with the cursor is insignificant compared to the improved physical processing determinism, especially as underlying data / statistics are changing during transaction processing.

So to sum up my thoughts, cursors do have a slight overhead which can be exaggerated by comparing a simple scenario such as your demo, but there are more significant issues that only come to light in more complex real world scenarios. The real penalty most people bump into with cursors isn't the use of the cursor per se but the inefficient business rules or unindexed commands within the cursor (or even the cursor expression itself)

I'm sure you'll appreciate setting up examples that demonstrate optimiser quirks on blogs is a bit too hit & miss to warrant investing much time in. Plus, it's also fairly common knowledge that complex queries are subject to optimisation errors & that using cursors or breaking queries into discrete expressions are time-tested solutions so not much would be gained by prooving this again here. Plus, I'm currently enjoying some really interesting SQL DownUnder Code Camp presentations today (c:

The SQL Server community is unfortunately full of self-perpetuating myths & cursors being bad is just another one imo, so the fact that something has been said in a conference doesn't really carry much weight. The fact that cursors are used extensively in the Oracle community & now that they're also being used in high end benchmarking by Microsoft are pretty good signs that the gods must be crazy on this topic..

Hi Greg,

I have worked for several years with applications working on both oracle and SQL Server. I think that while in Oracle there is no performance penalty for using cursors, in SQL server there is. I completely agree that complex queries may all of a sudden become the subject of an optimization error. In some cases that might happen after an upgrade, in other cases after some statistics reached some threshold. Yet I think in SQL Server we need a stronger incentive to use cursors than in Oracle, just because they are less performant.

Adam;

I don't think your example is a fair one in comparing the set-based approach and the cursor-based approach. There is no question you should use a single SELECT in this particular case. You would be using a single SELECT even with Oracle.  Using a T-SQL cursor in this case just adds more work for no reason, and is of course slower.

In my past discussions with the Oracle folks that I know on the use of cursors, my sense is that they are implciitly assuming a more complex business logic. A typical example is the processing of an order and its order details. Say if you have a web page that can accept several orders and each order has one or more line items, and once submitted you want to perform rather complex validation business logic inside the database. Because the validation business logic can be rather complex, the solution to the problem is naturally expressed as a loop through the orders and for each order you conduct various validation checks. In this case, there may be no set-based solution for many reasons (for instance, a check may require calling other stored proced procedures).

So it seems to me there is a continum of problems and solutions with the set-based approach optimal on one extreme and the cursor-based approach optimal on the other extreme. For problems that fall into the two extreme areas, there is little argument as to which approach should be used. But I suspect a large number of problems fall somewhere in the middle. And this is where caution needs to be exercised and where broad stroke statements favoring either approach would be wrong more often than not.

I certainly didn't say there was "no" overhead, just that they are fine for most purposes. I should explain that my use of "most purposes" really meant "most purposes where they are used" rather than "for most queries".

btw, your example performs a reporting-style summation over a large number of rows which isn't exactly the most common type of transaction that typically occurs in OLTPs. The time difference when using cursors for smaller, more typical OLTP queries would be substantially less, & most systems aren't heavily affected by the loss of the few milliseconds a cursor adds. Hence, I'm not surprised they're being used in TPC-E - the overhead just isn't as substantial as you've described.

The biggest problem with cursor code often isn't just the fact that a cursor has been used, it's usually an inefficiency within the business rules that are applied within the iterative section of the cursor program. Very commonly, simply adding an index to an internal query which runs within a cursor will solve a performance problem which many would blindly have blamed the cursor for. We see this kind of thing on a daily basis at work & we always try to tune the queries within cursors first before engaging the customer in far more intrusive code re-writes (& the consequential testing).

Just one more point re Alex's comments - it sounds like the implementation of cursors in SQL Server isn't as good as it could be & maybe MS could improve the implementation further. I knew that Oracle cursors were more efficient than SQL Server cursors & that they are far more widely used, but is the overhead really zero with Oracle cursors?

Cheers,

Greg

An Oracle REF CURSOR isn't really similar to a T-SQL cursor. A ref cursor is more like a SQL Server API server cursor. I think the context of discussions here is more on using cursors for implementing business logic inside a database than on using cursors where a client app processes the resultset.

For complex business logic, a common alternative to avoid T-SQL cursors is to use temp tables. The boundary between 'set-based' and 'procedure-based' can become rather blurred when there is a lot of use of temp tables.

I discussed this with one of my business partners tonight who is highly experienced with Oracle & he assures me there is a measurable difference in cursors vs set based & that Oracle cursors certainly don't come without performance degradation. It might be interesting to run a plsql version of your tsql script Adam to verify this. If I can get near an Oracle instance in the near future I'll give it a go too.

Apparently Oracle devs are encouraged to write set based expressions where possible but they're much more relaxed about writing cursors, especially when relatively few rows are being accessed. SQL Server devs on the other hand tend to be outright paranoid about using cursors, for no better reason than the gods pronounce that cursors are always evil.

Like so many things in IT, I think cursors have a useful purpose & they are fine when used appropriately (when implementing relatively complex business rules within relatively small to medium data sets). The virtually universal bias against using cursors in the SQL Server community is a unwarranted imo.

Hi,

I appriciate every on for saying that "set based" operations are better than cursors.  I have a question.  If I have to get the data from 10 different tables having say 50 million rows each, as of now I am using a cursor within a cursor to get data from 10 different tables.  However, if I need to use set based operation can you give me an example as to how this should be achieved. It will be great if it can be mailed to ramanadk@gmail.com

Thanks