Forum Discussion
Using Function.InvokeAfter() function to stop the SQL connection query being executed multiple times
the first query is obviously running on the server, but as it's a custom SQL Power BI doesn't create it's own 'native query'
now imagine following scenario:Query1 - connection to the SQL database, select a table
Query2 - reference Query1, select 2 columns, filter and group by with sum (all done using the UI)Query3 - reference Query1, select 3 different columns, group by and count (again, only using the UI)
In this scenario actions in Query2 and Query3 can both be performed by PowerBI and SQL. As long as the query folding is active the actions are happening on the server - that means that PowerBI 'writes' the actual SQL that is executed on the server (the 'native query'), as there was no SQL provided in the beginning. Does this clarify?
Hi Anonymous,
please read this thread about queries evaluation chain: https://social.technet.microsoft.com/Forums/en-US/8d5ee632-fdff-4ba2-b150-bb3591f955fb/queries-evaluation-chain?forum=powerquery.
Thanks for your response.
I have read the link you've provided and I have questions.
"So if Q1 queries a SQL Server database called "MyServer/MyDatabase" and returns a single unfiltered table called "MyTable", the query sent to the server might be "select [Col1] from [MyTable]". In this case, the persistent cache will now know the result of sending "select [Col1] from [MyTable]" to "MyServer/MyDatabase". If another M query (whether through referencing Q1, or by querying the same table directly) needs the same result, the persistent cache can provide it, and the result won't have to be fetched a second time from the SQL Server."
My Query1 is a SQL connection uses a complex SQL statement (sub query, joins, multiple where clauses, Group By, Order By)
Q1 - does this complex SQL statement mean it is too complex to stored as "persistent cache"?
My Query1 has the following 'APPLIED STEPS':
Sql.Database (connection to the SQL Server via the complex SQL Statement.
Table.TransformColumnTypes (simply changing same columns types)
Table.AddKey (simply adding an Index Column)
Q2 - Can/Will Query1 with the applied steps be stored as "persistent" cache?
Q3 - Do I need to ensure that Query1 is fully complete, so the data is stored in "persistent cache", before Query2 starts to calculate, as Query2 references Query1? How can I enforce that Query2 does not start its calculation before Query1 completes? Is the Function.InvokeAfter() function required and should I be placing this function as the very first Applied Step in the referencing function?
Q4 - To confirm, is a SQL Server source connection stored in "persistent cache"?
"However, because buffering happens in memory and is not persisted on disk, buffering during the load of one query does not affect the load of another query. If Q1 is buffered when Q2 is loaded, Q1 will be re-buffered when Q3 is loaded."
I do not understand this. I'm reading the above as Q1 is stored in persistent cache and therefore Q2 does not query the source system, it only queries Q1.
Q5 - Why does Q1 need to be re-buffered when Q3 is loaded?
Q6 - Where is the Table.Buffer() function actually to be placed?
For instance, Query1 is the query that will be referenced by multiple queries.
Should the Table.Buffer() function be placed as the very last Applied Step in Query1, so all in Query1 is placed in memory so the referencing queries, and by then referencing Query1 you are taking the data from memory?
Unfortunately for me, I still do not know if what I am trying to achieve, explained via this post and the linked post, is possible with my particular scenario?
Please advise? Thanks.
Hi Anonymous,
some answers of mine how I understand the topic.
Q1:
Nope, the complexity shouldn't be a problem. But the engine decides if it will be stored or not AND if it will be used again or not.
Chris Webb says: As far as I can see, sometimes Power Query reads data from a data source many times for a single query execution. (Source: https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/)Q2:
Nope, because only the raw data (fetched from the SQL Server) are stored in the persistent cache.
Q3:
Function.InvokeAfter won’t help at this place. It is used for delayed invocation of a function within a query – I use it like Thread.Sleep.
The PowerQuery engine creates an execution plan but we don’t see it anywhere. Therefore we don’t know in what order the data sources are requested.Q4:
Table.Buffer is a function which scope is limited to a query in which it is executed. If you have Q1 (let x = Table.Buffer(SQLSERVER_TABLE) in x) and Q2 where Q2 references Q1, the Table.Buffer doesn’t have any effect.
The answer to your Q4 question is therefore NO.
More to this topic:
https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buffer/Q5:
Because of the scope which is limited to one query.
Q6:
Table.Buffer won’t help at this place.
To your initial question “Is there any way, all from within Power Query, to have the source SQL Server only be executed against once?” – I don’t think so.
But if you have some intermediate queries like for ETL, you can allow loading only of the last query. The rest won’t be loaded and therefore won’t send any requests to the DB.
Nolock thanks for your response once again. Just some follow up thoughts.
Regarding:
Q2 - Based on your comment "because only the raw data (fetched from the SQL Server) are stored in the persistent cache" I decided to do the following to see if the SQL statement would execute/hit the source SQL Server only the once.
i) Create Query1 using a SQL Statement in the connection. The only Applied Step in this query is 'Source' to get the source data. No other steps have been applied as I want to try and have this data in the 'persistent cache'.ii) Create Query2 which references Query1. This Query2 has the applied steps. Query2 will now be referenced by the other queries that do their own unique thing.
I was hoping that Query1, as just being the connection executing the SQL Statement, will persistent cache the data. Query2 will apply some additional steps, and all other queries will just re-execute/re-evaluate Query2 which at worse will re-calculate/re-evaluate the Query1 which is in the persistent cache and therefore won't hit the SQL Server again.
Unfortunately, having tried the above I can still see all the derived queries being executed against the SQL Server.The reason why I am keen to understand if it's possible to restrict the source SQL Server being executed against once is because the DBAs will start questioning why I have multple instances of the same SQL Statement being executed against the SQL Server at the same time. Trying to do it all inside Power Query, I guess that it cannot be done. I hope to be proved wrong!!
Q3 - Based on your comment "The PowerQuery engine creates an execution plan but we don’t see it anywhere. Therefore we don’t know in what order the data sources are requested." I guess using the reference functionality will control what order the data sources are requested. If Query2 references Query1 then the Power Query execution plan will make it so that Query1 completes before Query2.
Q4 - Okay, thanks for clarifying this for me. So the Table.Buffer() only works for all processes within the same query (the same advanced editor section of code). Query1's table buffer cannot be called/utilised from another query (ex, Query2).
I do have a question on this though.
If the advanced editor code had something like this:Source = <connect to the source and retrieve some data>
-------
Remove Columns = ( Source, <remove some columns>)
-------
Group By = (Source, <group by a column> )
In the above very rough example, the Source dataset is being used in two separate processes. By calling 'Source' in each of these lines of code, isn't this in effect having Source as buffered? How would using Table.Buffer( Source ) make it any different?
Thank you again. Having your explanations to my specific questions helps. BTW, I have read all the links you've listed prior to asking my questions but I still have the need to ask them.Hi Anonymous,
Q2:
Unfortunately, you can push the PowerQuery engine to send a select to a DB only once – there are no hints or something else.
Q3:
Yes, you are right. Q1 will be executed before Q2. But it doesn’t mean that Q1 will be executed only once. I can happen that there are 2 “threads”: Q1 and Q1->Q2 which are executed in any order or in parallel.
Q4:
It depends on the data source as mentioned in the Technet Forum.
Table.Buffer in this case does the following:
Source = <connect to the source and retrieve some data> BufferedSource = Table.Buffer(Sourcer) // data are cached in RAM till the end of the query ------- Remove Columns = ( BufferedSource, <remove some columns>) ------- Group By = (BufferedSource, <group by a column> )
Without:
Source = <connect to the source and retrieve some data> // depending on source of data it will or will not be cached ------- Remove Columns = ( Source, <remove some columns>) // this step can use query folding if supported by data source ------- Group By = (Source, <group by a column> ) // and I hope (not 100 % sure) this step can also use query folding if supported by data source
When I develop I use as Table.Buffer as List.Buffer, try using query folding but I gave up optimizing a count of database requests. My datasets are refreshed late in the night and therefore not so critical for the database infrastructure.