EXPLAIN EXTENDED

When I was in the elementary school, I once visited my friend.

His mother greeted us, invited us inside and told us to wash our hands and go to the kitchen. Then she turned on a giant electrical samovar to make us some tea.

When it boiled, she switched it off and the friend took the teapot to fill it with hot water. Wait, said his mother. Didn't you forget something? Sorry, mum, said he, took a long steel spoon and stirred the water inside the samovar with it.

What's that you are doing? asked I. His mom replied: It's good for the samovar. It will live longer if you stir water with a metal spoon after it boiled, the manual says so.

Never knew about it, but OK, good to know.

About a year later my parents and I went to visit my grandmother. We kissed hello and she put exactly same electrical samovar on to boil. When it boiled, I turned it off and took a spoon to stir water. Wait! the granny shouted. What are you going to do, kill yourself? You don't put metal things inside the electric appliances!

I was quite confused. My friend's mom adviced to do this, and the friend did, and everything is OK. On the other had, granny's statement also made sense: metal is a bad thing to have between you and electricity.

I was 10 or so that time so I forgot that case quickly.

But when the grandma died, she left some of her belongings to me. I went back to her old house and found the box with the samovar. I recalled the spoon and got curious. The box had a manual inside. I opened the page with the warnings and among other things found the following: With this new model, it is safe to stir water using metal objects.

As you may know, people never read manuals, and when they do, they get them wrong.

My friend's mom must have read the manual and made conclusion that stirring was safe for the device, not for the user. And then a lateral shift occured if her mind, transforming safe for the device into good for the device. Hence the obligatory stirring after each use. This is OK for an end user that has no idea on how the device works and trusts the manual.

Grandma, on the other hand, most probably didn't read the manual and just relied on her common sense (of which she had plenty). And the common sense says that you don't put metal things into electrical kettles.

But what was the point of including that string into manual, after all? Who on Earth stirs the boiling water?

The factory that made the samovar must have designed two models. The first model was not insulated well, or, more probably, the insulation was not tested well, and there was a minor possibility of receiving electrical shock when putting a metal spoon into the water.

The manual of course warned against doing this.

The factory then improved the model, and among other improvements they made the better insulation and tested it for safety. Then they decided to present this as a feature: of course, it's better to have a safe samovar that doesn't kill you when you put a metal thing into it.

They were so proud of it that they even included that into the manual. Look, it's now safe to stir the boiling water with a metal spoon!

When users read through the manual, they see the following words: metal, stir, safe. They don't know the history of the model, they didn't read the previous manuals and of course they cannot figure out that this improvement only adds to safety, not usability, and it's only for the new model, not the old one. And when the manual says of such an uncommon thing to do, it's natural for users to think of it as of some kind of a maintenance procedure, or maybe of something that makes tea taste better, or something else. The very idea of stirring boiling water would not suggest itself into their mind if not for the fact it was printed in the manual.

There even exists a slight possibility that someone was injured due to this line in the manual: they could have read the manual for the new model and tried that spoon thing on the old model.

The manual writer should just have omitted that warning line from the manual as soon as they improved their thing.

One more example: a manual for the plastic baby bathtub I purchased some time ago said: Made of non-slippery plastic. You don't have to the place the safety mat into the tub.

The manual author should just have omitted the last sentence, really. Non-slippery plastic is fine, but don't place the safety mat into the tub part belongs on the Island of Misfit Mascots.

I know there is difference between don't and don't have to, but people cannot read, you know, when it comes to manuals. This phrase can actually lead to an injury when used out of context.

Same it true for advises against using cursors in SQL.

Every documentation, every blog, every database book, every SQL 101 shouts: don't use cursors! Cursors are bad!

Well, what's a cursor, in fact?

When you open a query and don't have time, memory or other resources to fetch and store the resultset all at once, you can ask the server to build the resultset for you and keep it hot for some time while you fetch and process all the results you need.

You can do it in lots of ways: depending on your database system, you can make a cursor read-only or not, static (which doesn't change once being open) or dynamic (which reflects changes when re-read from), visible globally or in transaction scope, scrollable in both directions or not, etc.

Having a stable resultset implies having some resources on the server side to keep this resultset stable. This resources can be memory or disk space to cache the resultset or locks to prevent the resultset from being updated.

Or course, different type of cursors require different resources and behave differently.

For instance, a STATIC cursor in SQL Server just makes a copy of the resultset in the tempdb, while a DYNAMIC cursor makes sure that the changes to the table are reflected with the cursor.

They even use different execution plans: a DYNAMIC cursor makes a couple of extra OUTER JOIN's with the source table to make sure the changes are reflected.

A STATIC cursor is also always READ ONLY in SQL Server and it doesn't lock the table except for that very moment it fetches the values. The rows are locked neither before nor after the fetch.

A DYNAMIC cursor, on the other hand, can lock the rows.

So, depending on your application needs, you can outsource the task of keeping the resultset to the server, wisely choose the cursor type you need and making sure you don't waste resources.

But the cursors have one more use: with almost any procedural extension to SQL (like PL/SQL, T-SQL, pl/PGSQL etc.) the cursors can serve as foreach substitutes and add some procedural abilities to a set-based language (SQL).

Programmers not experienced with SQL tend to abuse this feature. They are not accustomed to the set-based paradigma of SQL and they try to do what they were taught to do: open a cycle, loop through it, do something with a variable, close the loop, test, debug, check in, logoff, go home.

This programming pattern is called RBAR (Row By Agonizing Row) and generally should be avoided.

The RDBMS vendors know that and advise against both misused cursor types and RBAR programming.

But let's see what wording they choose:

Not all applications need to use cursors to access or update data. Some queries simply do not require direct row updating by using a cursor. Cursors should be one of the last techniques you choose to retrieve data—and then you should choose the lowest impact cursor possible. When you create a result set by using a stored procedure, the result set is not updateable using cursor edit or update methods.

What is a Cursor?

When read carefully, the article says cursors type should be chosen carefully.

But let's remember the samovar manual and the bathtub manual and how the users read the manuals and try to guess what message does this article convey?

It's simple: cursors are bad

One more example:

In general, if an operation can be performed with a simple, set-based query, performance will be faster than using cursors. This is true regardless of the relative speeds of cursor operations in SQL Server 7.0 and SQL Server 6.x.

KB 197800

How many developers that are new to SQL will note the words in general and simple?

Et cetera, et cetera.

This leads to a bitterly amusing thing: people that are able to use cursors efficiently are often ashamed to do this and revert to less efficient set-based decisions only because they are set-based.

You can even read a story of an SQL specialist who gave the negative points to DBA's who had the misfortune of remembering the exact syntax of DECLARE CURSOR while being interviewed. Don't know what did he give the positive points for, but I probably wouldn't be very proud to get them.

As almost any other language feature, the cursors can be extremely useful, like for the following things:

• Keeping a stable resultset and fetching data in chunks (the thing the cursors were designed for)
• Calculating running aggregates, like SUM and AVG. As of version 2005, SQL Server lacks an efficient way to do it set-based.
• Performing complex recursive queries. Recursive CTE's don't allow aggregates and constructs like TOP.

Almost any does not mean any, and can be does not mean will be, but there are numerous cases when a properly written cursor is nice and efficient.

A good developer is never reluctant to use a tool only because it's often misused by others.

P. S. Place a safety mat into your bathtub and never, never put metal things into electrical water heaters.