Forum Discussion
Referenced Query and Caching question - explanation/confirmation required
v-piga-msft - thanks for your reply.
"Actually, if you reference the table from SQL Server in query editor, the underlying data source will be queryed in SQL Server."
If I have understood you correctly then yes I am aware that if you place a SQL query inside the 'SQL statement' part of a SQL Server connection (Import Mode) then the query itself will be executed upstream on the actual SQL Server database. This is my intention.
"As aworkaround, you could export your table in SQL Server to excel and then get data with Excel connector in power bi so that it will not query in the sql server."
This wouldn't be an option due to company specific reasons.
More importantly, I would like all steps to be handled in Power Query so I am keen to understand if what I have stated, in my first post, is to be expected and therefore I have mis-understood the query-folding aspect, or do I need to configure things differently so to get my desired intention of querying the SQL Server database just the once??
I think the essential part of the article in reference to your problem is here:
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.
In other words if you load 14 tables loaded to your model there will be at least 14 calls to SQL Server. If you now have more (e.g. you reference the original table multiple times in ne query) then you can try using Table.Buffer()
https://docs.microsoft.com/en-us/powerquery-m/table-buffer
and change all the references within a particular query to the buffered table
disabling the background data preview should also reduce the number of queries during development time, as described here:
Stachu thanks for your reply.
I'm glad you mentioned Table.Buffer() as this is something I am looking to implement.So if I can just confirm exactly how best to use it.
I have the below M code which is the only query that directly connects to the source SQL Server table.
The other 13 queries I have in Power Query all reference the K8 Extract query. An example of how these other queries are referencing the K8 Extract query is below. All the others queries are very similar and do reference the K8 Extract in the same manner.
When refereshing the data via Power BI I can see, at some point of the refresh, 14 queries hitting the source SQL Server database - one for each Power Query query I presume.With the intention of the K8 Extract query being the only query to connect/query/hit the source SQL Server database, what is the best way to use the Table.Buffer() function?
What I have done is amend the K8 Extract query to include the Table.Buffer() function (see below):
In implementing the above and then refreshing the data via Power BI, I notice the following:
1) Through viewing the Task Manager the CPU is 73%, RAM(16Gb) is 94%, Disk is 100%.
2) Without the Table.Buffer() the whole data refresh took 11 minutes, but with the Table.Buffer() bit added it's still refreshing 1 hour later (and hasn't finished yet).3) The number of queries hitting the source SQL Server database, at any point so far, is only 5. Now is this due to the Table.Buffer() reducing the number of hits on the source SQL Server database, or is it only 5 as the other queries haven't got round to hitting the source SQL Server as my PC is grinding to a halt?
The whole point of this is to only hit the source SQL Server database once but with this RAM/Disk hogging that's happening I'm wondering if I should put the load on the source SQL Server.
Should using Table.Buffer() have the impact (RAM and Disk massively consumed) that my PC is experiencing?Do you have any suggestions on how to achieve what I'm trying to in a good way?
Thanks.UPDATE: this is an update following on immediately from my last post (above).
Due to the data refresh, with the Table.Buffer() implement as shown in the above post, still refreshing after an hour, I left my PC on overnight to allow the refresh to complete. I unlocked my PC the following morning to find my PC still ground to a halt. Unresponsive. I had to hard-boot my PC.
With the PC booted up without any Power BI report running, I...1) Opened the Power BI report without the Table.Buffer() implemented, and then refreshed the data.
The full refresh took 11 minutes (again). At the half way point of the refresh I checked the Task Manager stats and they were:
CPU @ 3%
Memory @ 53%
Disk @ 4%
2) Opened the Power BI report with the Table.Buffer() implemented (as shown in the above post), and refreshed the data.
My PC came to a halt again. Unreponsive. I managed to open the Task Manager and the stats were:
CPU @ 50%
Memory @ 93%
Disk @ 100%
I used Task Manager to end the Power BI instance 17 minutes into the data refresh. I have no doubt this refresh would've continued for hours (a day, maybe more) if I hadn't ended the task.
What is going on?
Have I used the Table.Buffer() function incorrectly?
Are the results I'm seeing expected or is something wrong?
How do I ensure the source SQL Server database is only connected to (hit) once?
Stachu ImkeF Greg_Deckler MarcelBeugI've @ the above users as I see you are main contributors on Power Query, and as I also see my post as, not only, a question on the Table.Buffer() function but also a question on the possible impact of using such a function. No doubt a very useful post for others in the future. It will be much appreacited if you can help.
Thanks.- Bump.
Is anyone able to confirm/explain my experience when using the Table.Buffer() function?
Thanks.