Improving Software

Although some of us might not like the association, programmers are essentially just another class of end-user. Hidden beneath our masochistic penchant for vi and a command line, programmers are just as thirsty for cushy features that simplify their tasks and bend their tools around their own aesthetic preferences.

I’m not so much talking about GUI programming tools or code generators, Lord knows we hate those. I’m referring to those handy language features that don’t necessarily add any extra capabilities, but provide a way to write code that is more readable and usually more succinct.

For example C#’s using statement that not only lets you wrap your code in concise blocks around your IDisposable resources, it also cleans up after you just like your mother did back in the day.

 using (SqlConnection conn=new SqlConnection(connectionString))
  {
     conn.Open();
 string MyNewPet= cmd.ExecuteScalar("SELECT TOP 1 Animal
                                         FROM Zoo"); } //thanks mom!

Of course you can accomplish the same objectives in your code without these handy shortcuts, but once you learn them you probably wouldn’t want to. Peter Landin, who put the Lambda in Lambda-Lambda-Lambda, coined the term ‘Syntactic Sugar‘ to describe these elements of programming languages.

A construct in a language is called syntactic sugar if it can be removed from the language without any effect on what the language can do: functionality and expressive power will remain the same.
-Wikipedia (Omnibus source of all human knowledge)

Great, now I have a name for something I already knew all about.
Thanks John, but I’ve got work to do…

A Rose By Any Other name

Not so fast, I’m just (finally) getting to the point of this article. Syntactic sugar isn’t always just another entry point for the same functionality. As is true with most things, you can’t delegate the work without giving up some measure of control. Further it is dangerous to assume that the architect of the platform you use must be smart to have that job and probably is better suited to make those calls anyway.

They may know the platform better than you, but remember that they can never understand as well as you what you are ultimately trying to accomplish. Optimizing for an unknown problem requires making assumptions that may not hold in your specific usage.

By no means am I saying that you should avoid using syntactic sugar, just that it is prudent to understand to some degree what is happening under the covers to avoid nasty surprises.

Syntactic sugar can be more fattening than it looks

If you have been following this blog, you know that I recently completed my certification for SQL 2008. While brushing up on the newer features of t-SQL I came across a bit of  previously unfamiliar syntactic sugar, the WHERE CURRENT OF syntax.

In the context of an open updatable Cursor, it is a shorthand way to reference the current row

UPDATE MyTable SET TargetField=2 WHERECURRENTOF myCursor

instead of

UPDATE MyTable SET TargetField=2WHERE MyTable.ID =@FetchedIDOfCurrentRow

Rock on!  I mean, if you n00b enough to be using cursors in the first place, at least you can do it with style, right?

Well, yes. Until my profiling turned up some shocking results. As it happens, the WHERE CURRENT OF syntax performed consistently and considerably slower than the same code using an ‘equivalent’ WHERE clause.

If anything, I expected it to outperform the legacy syntax because it had the row ready and waiting for an update because I was explicitly communicating my intentions about which row would be updated in advance. So SQL should be holding that row at the ready for the update. Bzzzt! Wrong. Fundamentally Wrong. Couldn’t be wrong-er if my name was Wrong Wrongerstien.

Despite anecdotal evidence supporting my initial findings, I was still incredulous that my assumptions could be smashed so soundly. So I set out to get some hard data using the simplest version of  an update inside a cursor that I could muster:

<snip> 
WHILE@@FETCH_STATUS=0  BEGIN UPDATE MyTable
 SET TargetField=@FetchSource+2 WHERECURRENTOF myCursor
     --WHERE ID=@FetchID  FETCHNEXTFROM myCursor
 INTO@FetchID, @FetchSource END<snip>

I tried a number of cursor types and indexing strategies, but in every case the WHERE CURRENT OF syntax was demonstrably slower, and the margin increased in a near linear amount as the  source data got larger.

The Query Execution Plans

I’m still dissecting the execution plans and will come back and post more once I understand the discrepancy, but the high level perspective is pretty telling.

Where ID=@FetchID Plan:
Clustered Index Update -> Update

Where CURRENT OF Plan:
9 additional steps including another Clustered Index Update, and 2 Clustered Index Seeks.

That’s all for now. Be careful out there and watch out for those empty code calories from syntactic sugar!

Filed under: Technology Tips | Tagged: tsql SQL Performance |