Why no Query Folding?

[robarivas]

In Power BI Desktop I am connected to an IBM DB2 database for the first time and when I run a simple filter on a date column in the table I do not get any Query Folding. So the filter takes an indefinite amount of time to run, especially because the table is very large--over 800 million rows. Any ideas as to why such a simple filter would not be folded?

When I connect to the database I have a choice of using two different drivers: IBM or Microsoft. I can only connect with the Microsoft option.

[v-yuezhe-msft]

@robarivas,

I make a test to filter rows from IBM DB2 database in Power BI Desktop, when I check the code in Advanced Editor, I note that query folding is taking place. Could you please check if the query folding occurs following the instructions in the blog below?

https://blog.crossjoin.co.uk/2016/07/26/value-resourceexpression-and-query-folding-in-power-bi/

Regards,
Lydia Zhang

[robarivas]

Hello @v-yuezhe-msft. I confirmed that Query Folding occurs when filtering most fields, which is good. However, it is specifically with Date and Date/Time fields that the Query Folding stops. And even with Date & Date/Time fields it will work if I choose (checkmark) particular dates. But if I filter for a range of dates (using After, Between, Before, etc.) then the Query Folding stops working.

Oh and by the way I (so far) have only noticed this on one of my largest tables in my database (i.e., my "sales" table). On other tables (so far) date filtering does not shut down query folding.  Very strange.

[v-yuezhe-msft]

@robarivas,

I suspect the issue is caused by that there is large amount of data in your table. Could you please filter your data by writing specific statement during the connection and check if the issue persists?



Regards,
Lydia Zhang

[robarivas]

Hello @v-yuezhe-msft

Per your suggestion I generated a small subset of the data using a SQL statement but now no folding takes place at all. Please help. Our transaction table is our most important table. Not being able to query it quickly is a major issue. Thank you.