Bug: Query folding not working with non-SQL datasource

[ImkeF]

When combined with non-SQL-datasources query-folding will often not work: http://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquer... 

This is really nasty. As performance optimizations is such a black art still unfortunately, many people will probably just throw the towel here, so I expect a high number of unreported cases here, so please fix.

[v-qiuyu-msft]

Hi @ImkeF,

Before I report this issue internally, I would like to confirm the reproduce steps from you:

Based on my understanding from your thread, the steps should be:

1. The table get data from the SQL database and inner join with other two tables: ZRIdFilter, Datumsfilter.

2. "So when I add additional lines to this filter-table, at a certain lenght the query will stop folding."?

Do you mean you add additional rows in ZRIdFilter or Datumsfilter table?

3. The profiler capture the T-sql like below:

select [$Ordered].[DBTabId],
    [$Ordered].[ZRId],
    [$Ordered].[Datum],
    [$Ordered].[Index],
    [$Ordered].[IsValid],
    [$Ordered].[Wert],
    [$Ordered].[Qualitaet],
    [$Ordered].[VWert]
from
(
    select [_].[DBTabId],
        [_].[ZRId],
        [_].[Datum],
        [_].[Index],
        [_].[IsValid],
        [_].[Wert],
        [_].[Qualitaet],
        [_].[VWert]
    from [dbo].[Daten] as [_]
    where ((((((((((((((((((((((([_].[ZRId] = 1069176 or [_].[ZRId] = 1069182) or [_].[ZRId] = 1069191) or [_].[ZRId] = 1069197) or [_].[ZRId] = 1069202) or [_].[ZRId] = 1075509) or [_].[ZRId] = 1075510) or [_].[ZRId] = 1075511) or [_].[ZRId] = 1075512) or [_].[ZRId] = 1075515) or [_].[ZRId] = 1075516) or [_].[ZRId] = 1075517) or [_].[ZRId] = 1075518) or [_].[ZRId] = 1243781) or [_].[ZRId] = 1243799) or [_].[ZRId] = 1243841) or [_].[ZRId] = 1649145) or [_].[ZRId] = 1649146) or [_].[ZRId] = 1649152) or [_].[ZRId] = 1652117) or [_].[ZRId] = 1652119) or [_].[ZRId] = 1652121) or [_].[ZRId] = 1652123) or [_].[ZRId] = 1652125) or [_].[ZRId] = 1652127
) as [$Ordered]
order by [$Ordered].[DBTabId],
        [$Ordered].[ZRId],
        [$Ordered].[Datum],
        [$Ordered].[Index]

If the steps above is not very clear and correct, would you please share detail steps?

Best Regards,
Qiuyun Yu

[ImkeF]

Hi @v-qiuyu-msft,

thank you very much for your quick answer.

1) correct

2) ZRIdFilter is the critical one (but the effect would be the same with Datumsfilter)

3) Yes, thats the profiler trace if query folding happens. But as soon as the number of items (ZRIds) is higher than 200, Profiler will not list these items individually any more, but load the whole table.

Ehren must have circled this back to the dev-team already where he got the confirmation and description of how the algorithm works and why it bugs here. So I would expect that this issue itself will be known or at least reproducable by the dev-team.

Thanks, Imke

[v-qiuyu-msft]

Hi @ImkeF,

I have reported this issue internally CRI 43917192. Will update here whatever the response I receive.

Best Regards,

Qiuyun Yu

[v-qiuyu-msft]

Hi @ImkeF,

Please see below response from PG:

This is currently by design. We have heuristics around how to perform heterogeneous joins which we have no interest in changing at this time. Consider filing a suggestion at ideas.powerbi.com for generic improvements to and/or control of heterogeneous joins.

Best Regards,
Qiuyun Yu