Rules-Based & Cost-Based Optimisers*
There are two main types of SQL query optimisers.
- those that apply execution rules known as Rules Based Optimisers (RBO)
- those that seek to minimise the ‘cost’ of running a query known as Cost Based Optimisers (CBO)
In the CBO context, cost is usually expressed as time, which often equates to real money in today’s pay-as-you-go (PAYG) cloudy world.
RBO is very much the poor relation compared to CBO. A rules-based approach to the query optimisation challenge can only really be applied to the simplest of queries.
Fun fact : most SQL optimisers are cost-based (CBO) and not rules-based (RBO).
SQL Order of Execution
Discussing the order of execution of an SQL query seems to have become ‘a thing’ recently. The concept of an order of execution only applies in the RBO case.
Despite this, it is often claimed that we can benefit from knowing the SQL order of execution.
Fun fact : you can’t benefit from knowing the order of execution for a rules-based optimiser (RBO).
Cost-Based Optimisation (CBO)
Cost-based optimisation (CBO) is the norm for the majority of DBMS engines. Cost-based optimisers consider a wide-range of potential query plans before deciding which to compile & despatch for execution. In theory, the plan with the lowest execution ‘cost’ (time) is selected.
Fun fact : there is no pre-determined order of execution for rules-based optimisers (RBOs).
CBO Is Not Easy
As query complexity increases, so does the problem of finding the most cost-efficient query plan.
This is covered in previous VLDB blog articles:
Fun fact : the cost-based query optimisation challenge is a function of query complexity.
The Simpler The SQL The Better
As SQL query writers, we can help the optimiser but not writing overly-complex SQL queries. Do you really need all those joins, CTEs, subqueries, derived tables and unions in one query?
<hint>No you don’t.</hint>
The answer? Break your mahoosive complex SQL into smaller simpler chunks.
Fun fact : Multi-step simple/efficient queries > single-step complex/inefficient queries.
SQL Query Optimisers Need Metadata
The cost-based optimisation challenge depends on the optimiser knowing about the tables involved in a query.
This metadata consists of things like number of rows in the table, domain of values of a column, the min/max values, the % of nulls etc. This is especially important for columns used to join, filter & aggregate table data.
In the absence of table/column metadata, the optimiser is essentially “flying blind”. A lack of up-to-date metadata can result in highly sub-optimal query plans.
Fun fact : maintaining relevant & up to data statistics is crucial for cost-based SQL query optimisation.
The Explain Plan Is Our Friend
As most SQL jockeys are probably well aware, the SQL explain plan gives insight into how a query will be/was executed.
The clues that help us understand the difference between a ‘good’ (fast/low-cost) and ‘bad’ (slow/expensive) plan are contained therein. Red flags can include things like CPU-intensive nested joins, re-hashing or large fact tables etc.
Fun fact : understanding explain plans is the #1 way to gain insight into SQL query optimisation.
And Finally…
The SQL optimisation challenge can be likened to the sat-nav challenge.
On the surface, maybe not so difficult…but on closer inspection…maybe it’s a lot harder than you realised!
*Yes, ‘optimiser’ is spelled with an ‘s’ in the UK, and not a ‘z’ 😉
