0

Although I'm using SQL Server, as this is a question of whether an optimiser will re-write a query to remove redundant columns, I'm pitching this at all data servers: RDBMS, NoSQL, MPP, anything capable of holding and querying stored data using SQL that shall optimise the query before running it.

I kinda feel that they would, or at least it would seem logical that they would especially as it'd be crazy to fill a cache unnecessarily, but I can't find any evidence to say they would.

I don't want to get bogged-down on how environments, network, server, table, and cache loads, and table size and performance will alter the selected plans; this is just a very high-level question of: would the server rewrite a query to remove redundant columns and/or joins, but mostly columns, that are not in any way used to generate the result.

On my isolated dev server, I have this test query running against a tiny test 290k row table, it has a pk on an identity field, and a composite index which whilst two of the fields from the derived table are covered as part of the index, the primary field under test is not

The derived table in this instance has 7 redundant columns, and I'm executing these three DBCC commands before each run so as to start with a cold cache : DBCC FreeProcCache; DBCC DropCleanBuffers; DBCC FreeSystemCache('sql plans'):

Select
a.provider_type
,Count(1) As count_of_provider_type

From (
    Select
    customer_id
    ,access_plan
    ,provider_type
    ,ap_postcode
    ,browser
    ,session_start_date
    ,session_end_date
    ,payment_method

    From adhoc..datacentre

)a

Group By
a.provider_type;

Returns this actual plan: enter image description here

And from the profiler, CPU: 92, Reads: 23703

And then, having re-ran the three DBCC commands to return the cache to cold, running this re-expressed query:

Select
provider_type
,Count(1) As count_of_provider_type

From adhoc..datacentre

Group By provider_type;

Gives me this actual plan: enter image description here

And profiler, CPU: 78, Reads: 23476

Notice any similarities? Which given the batch count, io and cpu, leads me to wonder that the optimiser did rewrite the first query to remove the derived table and the redundant columns.

But how can I prove it. I can't find anything at learn.microsoft under the Query Processing Architecture to suggest that the optimiser would rewrite the query, neither can I find a way of seeing what was transferred to cache. Does anything exist that can definitively say exactly what was read and cached.

Remember - although I'm using SQL Server, I'd be interested to know how other RDBMS / MPP such as GBQ, Redshift, Athena, Snowflake etc would handle this

And finally, the why. What nutjob would write the first query without having realised it could be re-expressed?

This is twofold: Firstly views, and secondly and more prominently: SQL from visualisation and reporting tools capable of accepting an SQL script, which is often functionally equivalent to a non-materialised view.

As we all know, views can be abused. They shouldn't, and in an ideal world, users would create views as isolated models to spit-out a result-set ready for ingestion by the tool that the model was designed for, which is also the same direction for visualisation and reporting tools.

But we all know this never happens. Just like a doctor who has a cream for that, so too have engineering built a view that includes "what you're after" in its output, "so you don't need to go and create a new query, just query that view". And if the view is basic enough, maybe it's being used to replace a table, but includes scd:2 logic, and maybe its selecting non-engineering and/or PII data; or maybe it is a basic model with a couple of non-complex joins. But if a user were to query this view for only a couple of fields, would the optimiser rewrite the query to remove redundant columns of a single-table view, and possibly remove redundant joins from a multi-table view?

As I said at the beginning, I feel the optimizer would, but I need to be able to evidence this beyond conjecture or theory.

1 Answer 1

0

The answer is yes. Typically, an optimiser will drop unnecessary columns, and will also drop joins if possible.

The most obvious case of dropping columns is where * is used to select all columns at an earlier stage (in a derived table, or CTE), but not all columns are retained at the final output.

In some cases, the inclusion of unused columns may be a small programming error, but unused columns also frequently remain in intermediary results for the purpose of debugging, or (as you've already said) because the same intermediary results are shared between a number of outputs, some of which use certain columns that others don't.

Also worth remembering that SQL is designed to support ad-hoc use, where a programmer may be iterating quickly through several forms of a query with minor variations, quickly re-adapting existing skeletons, or otherwise dealing with scraps of code which aren't intended to become permanent fixtures, so the fact that the optimiser makes the easy optimisations rather than complaining that an unnecessary column is an error, is a desirable feature.

12
  • I thought this to be the case, but where is this evidenced? Is there any way the optimised query / algebrized version can be seen, or any books/whitepaper's where this is discussed. The honesty box behind this is that I'm also a Tableau developer. Tableau have a function for dropping a query into a "Custom SQL" object, and depending on what you are doing, will send the top query. The common consensus is that cSQL should be avoided as it is underperforming due to the presence of the derived table. I've called this out having tested and demonstrated this to be factually incorrect but... Commented Mar 3 at 9:51
  • ... despite this, there are those including engineers within Tableau that refuse to listen, arguing using untested ideas that the entirety of the derived table is always executed. One such example comes from this whitepaper who is using Select * which I suspect to be an effort to force the theory to fit a conclusion: snowflake.com/wp-content/uploads/2021/06/… ... Commented Mar 3 at 10:01
  • ... Page 8-12 for example. Which in turn references a tiny unevidenced entry on pg 39 of the "Designing Efficient Workbooks" by Interworks - I have a copy here: 1drv.ms/b/s!AljLJsyUbXmPhIwEMmT2oFK1p-Tfqg?e=4WLxSx So in order to back this up I need to identify hard-proof that this is the case as right now, there is far too much confusion, but most of these articles seem to be written by those who assume queries will be executed exactly as they are written, and just like a book, the longer the query, the more time and resource it'll take to process Commented Mar 3 at 10:03
  • @SteveMartin, I must admit I've spent a few minutes searching and it's difficult to find an authoritative statement that the disregard of unused columns is an optimisation SQL Server is capable of. The most straightforward kind of proof however is exactly what you've already done: show that the query plan or execution time changes depending on whether a column contributes to the final result or not (using two queries that select multiple columns in an intermediary step, but differ only by whether the output selects all columns from the intermediary step or not).
    – Steve
    Commented Mar 3 at 10:44
  • Thanks @Steve, I guessed as much, although is this the case with other vendors? I was thinking Oracle and Postgres more than anything, especially Oracle given its wide-range of optimisation options, but still I'm drawing a blank. It would seem illogical that despite the query enhancements, that an optimiser would leave redundant items in and then pull those through only to be ditched a second or so later, the waste on resource would be objectional Commented Mar 3 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.