Better control and performance for expression fields

Hi Mark,

Joining other tables inside expression fields will indeed result in performance loss. We will not parse every expression column to hopefully construct the most optimized query in the end. This is incredibly difficult.

Whilst the given situation may be straight forward, keep in mind that every expression field can be written differently. For example, joined table alias difference, sorting difference, top 1 statements, etc. Left joining another table usually results in more rows being added to the record set, therefore resulting in a PK error as now multiple rows have the same Primary key. 

The best alternative to the situation you describe is by creating a view. This will ensure that you have full control over how the joins are and you can add more conversions and columns as you desire. 

Hi Mark,

I know that parsing is difficult and that you can have a multitude of different expression fields, but my suggestion is not to have the SF parse the resulting query and optimise it, but to give this type of control to the developer. 
I would like to be able to create a join, the same way you would normally draw references. So this is not meant as a replacement for expression fields or a way to make the SF more intelligent, but as an additional method for us to create better performing query’s

A view is definitely a possible way to gain more control, but in our experience views also cost a lot of performance.

A possibility to show the result of a scalar function, as read only field only in the form would already be nice. 

The function will than only be executed for the current record.

For example to show full the full address of a company in a invoice form.

Note that expression columns are not only calculated on data selection but also during row editing. Having the expressions rely on one or more (left)-join statement(s) would require a different approach for this.

Is this idea really aimed at a different approach for including an expression column, or is this idea more focused on reusing the ‘from-part’ of the expression query for multiple expression column values?

To illustrate, there is barely a difference between the following queries (check the query plan on your SF database):

-- subquery
select
    t1.model_id,
    t1.branch_id,
    t1.tab_id,
    (select t2.work_model from model t2 where t2.model_id = t1.model_id)
from tab t1

-- left join
select
    t1.model_id,
    t1.branch_id,
    t1.tab_id,
    t2.work_model
from tab t1
left join model t2 
  on t2.model_id = t1.model_id

A major difference is that the left-join will result in record duplication where the subquery will simply cause an error like: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. which is a good thing - record duplication can lead to a lot of problems.

However, there are indeed performance differences when multiple expressions use the same source:

-- subquery
select
    t1.model_id,
    t1.branch_id,
    t1.tab_id,
    (select t2.work_model from model t2 where t2.model_id = t1.model_id),
    (select t2.rdbms_type from model t2 where t2.model_id = t1.model_id)
from tab t1

-- subquery via outer apply, to allow reusability of 't2'
select
    t1.model_id,
    t1.branch_id,
    t1.tab_id,
    t2.work_model,
    t2.rdbms_type
from tab t1
outer apply(
    select *
    from model t2 
    where t2.model_id = t1.model_id
) t2

-- left join
select
    t1.model_id,
    t1.branch_id,
    t1.tab_id,
    t2.work_model,
    t2.rdbms_type
from tab t1
left join model t2 
  on t2.model_id = t1.model_id

The outer apply allows reuse as well as aggregation, top (1) etc. But can also cause the beforementioned record duplication.

As I mentioned before it is primarily aimed at reusing the from part to obtain a better performance. It is often the case that when you want to see a field from another table that you also want see another field of the same table or even more. 

I do see the value in the error that warns about returning multiple values, but the performance of expression fields is definitely something that quickly becomes a problem.

We could try to approach this by dissecting expressions into a selection part and the source part. Expressions that use the same source part could all be selected at once from the same source part.

The two expression columns:

expression_1: select t2.fieldX from table2 t2 where t2.key = t1.key

expression_2: select t2.fieldY from table2 t2 where t2.key = t1.key

could be turned into:

select ..,
   n1.expression_1,
   n1.expression_2
from table t1
outer apply (
   select 
      t2.fieldX as expression_1,
      t2.fieldY as expression_2,
   from table2 t2 where t2.key = t1.key
) n1

The effectiveness of this would depend on how well the expression queries can be dissected by the runtime components into selection parts and source parts. For instance, the following queries would be a bit harder to dissect:

expression_1: select top (1) t2.fieldX from table2 t2 where t2.key = t1.key order by t2.fieldS

expression_2: select top (1) t2.fieldY from table2 t2 where t2.key = t1.key order by t2.fieldS

This solution would not require a different approach for creating expression fields by the developer.