As already discussed, SQL query optimisation is a non-trivial challenge. SQL code has to quickly be assessed by the optimiser and turned into an efficient query plan that can be executed by the DBMS.
Part of the challenge the optimiser faces is that relational databases, unsurprisingly, can contain lots and lots and lots of relations (tables to you and me) which SQL writers have a tendency to want to join together en masse.
What’s the problem with that, I hear you ask? Well, the problem is that the ‘result space’ the optimiser has to search increases exponentially as extra tables are added to the query.
What this means is that the number of possible ways a query can be executed quickly becomes a very large number as we add more tables to the query.
In a nutshell, adding tables makes the job harder for the optimiser to consider all of the available ways in which the query can be executed. This, in turn, reduces the chances of the most efficient plan being generated.
Although it’s Teradata-centric, a previous VLDB blog article discusses this point in more detail, and can be applied to any SQL database: https://lnkd.in/dqsW3fBg
The takeaway here is quite simple: don’t just add more and more tables to your SQL query just because you can.
Give the optimiser a fighting chance of generating an optimal plan. You know it makes sense!
