Bug: Query folding not working with non-SQL datasource

Status: New
by ImkeF Super Contributor on ‎08-03-2017 11:16 AM

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.

Comments
by Moderator v-qiuyu-MSFT
on ‎08-03-2017 11:09 PM

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

by ImkeF Super Contributor
on ‎08-03-2017 11:24 PM

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

by Moderator v-qiuyu-MSFT
on ‎08-04-2017 12:56 AM

Hi @ImkeF,

 

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

 

Best Regards,

Qiuyun Yu

by Moderator v-qiuyu-MSFT
on ‎08-06-2017 07:49 PM

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

Idea Statuses