On reusability and SQL Server

In my past life as a VB developer, I, like my peers, followed the mantra of reusability. Generic, we said, was good. Loosely coupled, parameter-driven code that made sound use of control flow was our goal.

When I crossed the Rubicon and became an SQL developer, I brought that mindset along with me, as did many (if not most) of my peers who likewise made the transition from the procedural to the declaritive. Many of us are still motivated by that reusability carrot, as evinced by the repeated use of (for example) optional parameters and the COALESCE keyword. The front-end folks say, “Make us a procedure that will take a date range, but let us pass empty parameters, and deliver to us ALL the rows if we pass NULL values.”

Easy for them to say. And easy to implement as well: just add that handy-dandy COALESCE in there (or CASE if you’re an SQL Server developer and you like to translate your own ISO keywords, thanks) and voila! We’re done. Next?

Not so fast. Sure, COALESCE will let you do this:

WHERE last_update >= COALESCE(@my_date, last_update)

but at what cost? A clustered index scan, that’s what, regardless of the number of rows you expect to return. Yes, the optimizer will take one look at this arrangement and elect to punt, and never mind what down it is. Two million rows in the table? Not my problem, bub.

That’s because in the declaritive world, generic does not necessarily equal good. SQL Server wants code that is specific to the task, and not particularly good for anything other than that task. The optimizer loves code tailored to an index. May I sniff your parameters, sir? You bet you can! And remember them the next time I want this data, will you?

We tweak our indexes, and ponder our primary keys, and agonize over the right way to cluster a table. But indexes and keys are not generic: they are specific to the table, and to the data, and ultimately to the goal of extracting information from that data. The same ought to be true of our queries and stored procedures: not to aim at reusability, or portability, or other wills-o’-the-wisp, but to craft our code carefully, to tailor it to the task, to remember that performance is our real goal, the real measure of our success.

Advertisements

About bigpurr

Pursuer of knowledge, maker of music and poetry, bird watcher, cat lover, maven of literature and fine beer, and champion of reason...
This entry was posted in SQL Server Development and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s