· Apr 27, 2016

Reducing select fields hurts query performance

We have a query that looks something like:


select a, b, c, d, e, f

from tbla inner join tblb on tbla.....

This query runs in the expected 200-400 ms range.

If we run the same query but reduce the select to just:

select a

from tbla inner join tblb on tbla.....

then the query runs for 30 minutes or more. 

The rest of the query is identical.  Is there a logical explanation for how this can happen?


Discussion (5)1
Log in or sign up to continue

Hi Andy,

Have the tables used by the query been tuned?  If not, I would suggest tuning the tables.

In the Management Portal, System Explorer -> SQL, switch to the proper namespace and then enter the query text in the Execute Query tab and press the Show Plan button.  Now do the same with the query that selects just a single column and compare the query plans.  Are they different? 

If they are different, post the query plans here so we can have a look.

Possible other issues:

- Are any of the fields in the multi-field select list computed, and could the computation be taking a long time?

- Is the table using default storage, or %CacheSQLStorage?  If %CacheSQLStorage, do any of the fields in the multi-select field list have expensive data retrieval logic?

- Are the tables in the from clause all base tables, or are views involved? 

- Do the fields in the select list come from the same table?

Hi Andy! The logical explanation for this behavior is that changing the select list causes the query optimizer to choose another execution plan\access path. Unfortunately the new plan is slower than the first one.

Like David describe I think the first step is to check that all tables has selectivity information. These infos are very important and used by the optimzer to find the "right"\fastest plan. You can evaluate this by compare the used plans.

So: -> execute TuneTable and check again.

If you can share the Show Plan information for each query that will probably add some insight.   Given that the second query has only one column it may be that there are additional adjustments to the query that would yield better performance, although this does not directly address your specific question as to why it is much slower.  At the same time a query that is taking well over 30 minutes seems like there is something not quite right.