CodeSOD: Just a Few Updates

Misha has a co-worker who has unusual ideas about how database performance works. This co-worker, Ted, has a vague understanding that a SQL query optimizer will attempt to find the best execution path for a given query. Unfortunately, Ted has just enough knowledge to be dangerous; he believes that the job of a developer is to write SQL queries that will “trick” the optimizer into doing an even better job, somehow.

This means that Ted loves subqueries.

For example, let’s say you had a table called tbl_updater, which is used to store pending changes for a batch operation that will later get applied. Each change in updater has a unique change key that identifies it. For reasons best not looked into too deeply, at some point in the lifecycle of a record in this table, the application needs to null out several key fields based on the change value.

If you or I were writing this, we might do something like this:

<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> id <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span>, <span class="hljs-type">date</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span>, location <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span>, type <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span>, type_id <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span>
<span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>

And this is how you know that you and I are fools, because we didn’t use a single subquery.

<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> id <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span> <span class="hljs-keyword">where</span> updater <span class="hljs-keyword">in</span>
        (<span class="hljs-keyword">select</span> updater <span class="hljs-keyword">from</span> tbl_updater <span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>)

<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> <span class="hljs-type">date</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span> <span class="hljs-keyword">where</span> updater <span class="hljs-keyword">in</span>
        (<span class="hljs-keyword">select</span> updater <span class="hljs-keyword">from</span> tbl_updater <span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>)

<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> location <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span> <span class="hljs-keyword">where</span> updater <span class="hljs-keyword">in</span>
        (<span class="hljs-keyword">select</span> updater <span class="hljs-keyword">from</span> tbl_updater <span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>)
       
<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> type <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span> <span class="hljs-keyword">where</span> updater <span class="hljs-keyword">in</span>
        (<span class="hljs-keyword">select</span> updater <span class="hljs-keyword">from</span> tbl_updater <span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>)
       
<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> <span class="hljs-type">date</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span> <span class="hljs-keyword">where</span> updater <span class="hljs-keyword">in</span>
        (<span class="hljs-keyword">select</span> updater <span class="hljs-keyword">from</span> tbl_updater <span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>)
       
<span class="hljs-keyword">update</span> tbl_updater <span class="hljs-keyword">set</span> type_id <span class="hljs-operator">=</span> <span class="hljs-keyword">null</span> <span class="hljs-keyword">where</span> updater <span class="hljs-keyword">in</span>
        (<span class="hljs-keyword">select</span> updater <span class="hljs-keyword">from</span> tbl_updater <span class="hljs-keyword">where</span> change <span class="hljs-operator">=</span> <span class="hljs-variable">@change</span>)

So here, Ted uses where updater in (subquery) which is certainly annoying and awkward, given that we know that change is a unique key. Maybe Ted didn’t know that? Of course, one of the great powers of relational databases is that they offer data dictionaries so you can review the structure of tables before writing queries, so it’s very easy to find out that the key is unique.

But that simple ignorance doesn’t explain why Ted broke it out into multiple updates. If insanity is doing the same thing again and again expecting different results, what does it mean when you actually do get different results but also could have just done all this once?

Misha asked Ted why he took this approach. “It’s faster,” he replied. When Misha showed benchmarks that proved it emphatically wasn’t faster, he just shook his head. “It’s still faster this way.”

Faster than what? Misha wondered.

Source: Read More