Reducing select fields hurts query performance

Question

Question

Andy Caranicas · Apr 27, 2016

#Caché

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?

Thanks.

Discussion (5)1

Log in or sign up to continue

David Van De Griek · Apr 27, 2016

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?

1 0

score 0 · Answer 1 · 2016-04-28T04:41:28-04:00

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.

score 0 · Answer 2 · 2016-04-28T06:59:05-04:00

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.

score 0 · Answer 3 · 2016-04-28T14:11:15-04:00

Thanks for the responses. These all make sense to me but unfortunately, this database lives underneath an application layer (Artiva from Ontario) so we don't have control even to look at execution plans. We are using DBeaver to run sql but that option is not available. I also assume we don't have access to tune the db either.

score 0 · Answer 4 · 2016-04-29T02:00:43-04:00

Can you post the original SQL? Maybe I can find some joins that missbehaviours. In some cases a select to a specific field in joins let the query-optimizer run the false index. My experience is that the query-opt. somehow decides to use the extend-index instead of an property-index.