Forum Discussion
Using Function.InvokeAfter() function to stop the SQL connection query being executed multiple times
Hi,
This is a bit of a follow on from another post of mine on the same parent requirement. The other post (see below for link) hasn't lead me to an answer so, in attempting to achieve what I need to do, I tried the method of using the Function.InvokeAfter() function. Unfortunately, it hasn't worked either.
https://community.powerbi.com/t5/Power-Query/Referenced-Query-and-Caching-question-explanation-confirmation/m-p/738754#M24552
In my other post (link above) I was pursuing the Table.Buffer() function but it hasn't resolved my objective - I would still like to know if Table.Buffer() should work and if I am implementing it correctly.
This time, this is what I've done to try and resolve my objective by using the Function.InvokeAfter().
1) Create a SQL connection named Query1. This query has a SQL statement which is folded back to the SQL Server.
2) Create a query named Query1_Referenced which references Query1. It uses the Function.InvokeAfter() function like this:
let
Source = Function.InvokeAfter(
()=>#"Query1",
#duration(0,0,0,60)
),
3) The derived queries, about 10 of them, all now reference the Query1_Referenced query (I thought this extra reference query step will allow an additional Function.InvokeAfter() function to help). Each of the derived queries reference the Query1_Referenced query as follows:
let
Source = Function.InvokeAfter(
()=>#"Query1_Referenced",
#duration(0,0,0,120)
),
Of course, each of the derived queries have their opening statement as above but then go on to do their own unqiue logic - hence the need for each derived query.
When testing this, not only have I placed the Function.InvokeAfter() function where stated, but I have also unchecked the 'Allow data preview to download in the background' box. I have also tested my method with the 'Enable parallel loading of tables' checked AND unchecked.
My thinking here is to have the Query1 query, which connects to the SQL Server, complete ensuring the data is fully cached. Then have the Query1_Referenced query get the dataset from Query1 (not getting it's data directly from the SQL Server). Then all the derived queries reference the Query1_Referenced query (again, not getting it's data directly from the SQL Server).
When running this test, I can see the source SQL Server is being executed against multiple times with the same SQL statement. I do believe the SQL Statements are executing against the SQL Server once their Function.InvokeAfter() delayed times (so either 60 or 120 seconds) have been met.
Is what I'm trying to acheive possible?
Have I implemented the Function.InvokeAfter() function correctly?
Should I provide enough duration lag (so above the 120 seconds) to ensure that Query1 had fully completed for I kick off Query1_Referenced (and the same for the derived queries)?
Is there any way, all from within Power Query, to have the source SQL Server only be executed against once?
Thanks.
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?
11 Replies
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.
