Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tdenbow
Frequent Visitor

Dataflow Gen2 execution of chained queries and caching

In DFGen2, if i have a query (call it Query1) that is used by several other queries, will Query1 be executed each time the other queries are run or will the results from the first call be reused by the other queries? Does the behaviour change if Staging is enabled/disabled?

1 ACCEPTED SOLUTION

It depends. For the staged queries, once the query is staged the subsequent queries will simply reference the staged data as mentioned in the articles shared.

 

For other queries, it relies on how they're being evaluated as well as the privacy / firewall partitions set by the privacy levels. The best articles that I can share to answer the question on how things are being evaluated by the Power Query engine are the ones below:

Particularly the query plan is the one that will help you the most to understand how your query will be evaluated. It could be that you have 10 queries, but all of them result into a single native query to your data source due to the query folding of the data source and the connector taking place, so looking at each of them separately would not be the best way to look at things. Its because of this that the answer really is "it depends" and I hope that the articles mentioned above help you understand better how your queries are being evaluated.

If you want to force a query to be evaluated once and subsequent queries to simply reference it and take advantage of the query that was computed, the most explicit way to do so is to take advantage of the "staging" mechanism described in previous articles shared. There are other ways to do so, but the one recommended by the Product Group is to leverage the Staging mechanism.

View solution in original post

6 REPLIES 6
v-cboorla-msft
Community Support
Community Support

Hi @tdenbow 

 

Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

v-cboorla-msft
Community Support
Community Support

Hi @tdenbow 

 

Thanks for using Fabric Community.

 

In order to understand how does queries works in Dataflow Gen2 during staging is enabled/disabled.

You can refer to this documents that might help you. Link1, Link2.

 

I hope this information is helpful. Please do let us know if you have any queries.

Thanks for those and i had seen them previously. What they do not answer is my question of "if i have a query (call it Query1) that is used by several other queries, will Query1 be executed each time the other queries are run or will the results from the first call be reused by the other queries?" I do understand if the data is staged, then it "probably?" wont be run each time...but how about for unstaged?  

It depends. For the staged queries, once the query is staged the subsequent queries will simply reference the staged data as mentioned in the articles shared.

 

For other queries, it relies on how they're being evaluated as well as the privacy / firewall partitions set by the privacy levels. The best articles that I can share to answer the question on how things are being evaluated by the Power Query engine are the ones below:

Particularly the query plan is the one that will help you the most to understand how your query will be evaluated. It could be that you have 10 queries, but all of them result into a single native query to your data source due to the query folding of the data source and the connector taking place, so looking at each of them separately would not be the best way to look at things. Its because of this that the answer really is "it depends" and I hope that the articles mentioned above help you understand better how your queries are being evaluated.

If you want to force a query to be evaluated once and subsequent queries to simply reference it and take advantage of the query that was computed, the most explicit way to do so is to take advantage of the "staging" mechanism described in previous articles shared. There are other ways to do so, but the one recommended by the Product Group is to leverage the Staging mechanism.

thanks for the info. I cannot take advantage of staging this query because it is a list of files, and their contents, from a sharepoint query therefore the contents column cannot be staged. this query is called dozens of times and since sharepoint is already an expensive call, we were wondering if it is going to be called dozens of times. Since SharePoint is a streaming query, query folding will not be occuring so i assume it is going to be called/refreshed each time.  

What do you do with the list of files?  What does the dataflow do and what are its final or output queries ?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 FBC Gallery Image

Fabric Monthly Update - March 2024

Check out the March 2024 Fabric update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.