This blog is the third in a series about misconceptions surrounding SQL tuning that are quite common. It covers the misconception that there is always some SQL syntax that is the best across different databases and hardware environments.

 

A lot of so-called SQL experts on the internet give you advice on how to write a better SQL or how to tune your SQL statements. I agree that most of them are skillful SQL coders, but it doesn’t mean that the advice that they are providing is always correct. Most of their experience was gained from a limited testing environment. These so-called (or maybe self-proclaimed) experts think that their SQL syntax or syntax transformation which is better in their testing environment will be applicable across the board to all environments. But they are seldom fully aware of the dynamics of today’s cost-based SQL optimizers. The same SQL syntax may not get the same execution plan in different database environments and therefore the performance will vary.

 

When you execute a SQL statement, the database optimizer evaluates different executions plans for the SQL statement and picks the plan that it determines has the lowest cost. You will find that in different database environments, the database SQL optimizer will likely determine that different execution plans will have the lowest cost to process your SQL statement.

Let me illustrate this point with the following diagram:

In this illustration database A selected “Plan 2” to process the “SQL A”, but in database B, “Plan 5” was used. So, the same syntax for “SQL A” may have a different execution plan in different databases.

So if you only tune your SQL statement using some general rule that certain syntax is the best way to write a SQL statement, at times you will end up using a less optimal execution plan.

 

Now let’s further narrow down this dynamic decision by applying a hint to the SQL.  For this example we are using the Oracle optimization hint, USE_MERGE, for a two-table-join SQL statement and we will assume that the database SQL optimizer will actually use this hint and use sort merge to process the two tables. Note: Even though you added a hint to a SQL statement, the database optimizer may not be able to apply that hint in all cases and will use an execution plan that ignores the hint.

Let’s take a look at another diagram to see how the SQL statement performed in different database environments using the Sort Merge Plan:

So, from this illustration you can see that even the same execution plan applied to different databases may result in different performance. In this case, Database C is most suitable for a Sort Merge join for these two tables.

Now let’s take a look at combining these two diagrams into one:

In reality, the cost-based SQL optimizer will use different database statistics to generate an execution plan. But in this diagram, I used human thinking to select a plan (in my case Plan 2) first and then assumed that it would be the best across the board. So, you may see that how dangerous it is for those so-called SQL experts to tell you which plan or SQL syntax is better without telling you under which conditions to use this syntax or plan.

This is why in my Blog; I seldom tell you how to write better SQL statements, although there are some best practices in the market that are good for most environments. But a lot of problematic SQL statements need special tuning which normally steps outside the bounds of those common rules that the so-called experts provide. Surprisingly, sometimes even a badly constructed SQL may have good performance in certain environments. For most developers, it is not problem for them to master learning those best practices. The problem is that those best practices limit developers’ thought process and they fail to take the next and important step to explore the relationship between SQL syntax and execution plan generation, because people are too bias to thinking that it is the syntax of the SQL statement that provides the better performance instead of realizing that it is the database optimizer’s selection of the execution plan that determines the performance of the SQL statement. So, next time you find some tips from a tuning book or the internet which tells you how to write better SQL, please always keep in mind that it is good only for certain environments and cannot be applied as an across the board rule!