Forum Discussion
Advocate IOdbc.Query triggering only one query
Currently the Odbc.Query function is triggering 2-4 identical queries against the source DB engine instead of just one.
This is happening even after disabling all functionalities and lowering the settings to minimum in PowerBI Desktop app as noted in this guide: https://learn.microsoft.com/en-us/power-query/multiple-queries
The only solution is to put Odbc.Query function inside the Table.Buffer function and then only one query is triggered.
It is not clear to me the reasoning for Odbc.Query to trigger multiple identical queries if Table.Buffer is able to load the data by only triggering one query.
And therefore, I suggest that for this improvement to be done to Odbc.Query function to trigger only one query.
The reasoning is that each query triggered (processed) by the source DB engine comes with a cost. By lowering the number of queries executed at the source DB we are lowering the cost too.
Here is example usage of both functions with sample query:
| Scenario A | Scenario B |
| Odbc.Query (triggering multiple queries) | Table.Buffer (triggering one query) |
| let Source = Odbc.Query("dsn=My_ODBC_Source", "SELECT 1 AS col_a") in Source | let Source = Table.Buffer(Odbc.Query("dsn=My_ODBC_Source", "SELECT 2 AS col_a")) in Source |
And here are the queries executed at the source DB engine as reported by it:
For Scenario A: 4 identical queries
For Scenario B: one query
Hi iviglious ,
Thank you for your detailed analysis.
Since you have already raised a support ticket and closed, creating an idea and upvoting it to increase visibilty would be the next best approach. Thanks for doing that.
Thanks for analyzing deeper into (Value.NativeQuery(StarburstPresto.Contents) and confirming it generates two identical queries.
While Table.Buffer is a workaround, we understand that it is not an ideal solution due to its impact on total processing time.
Now that the idea is created, thank you for your patience as the issue is being addressed.
Best Regards,
Vinay.
7 Replies
v-veshwara-msftCommunity Support
Hi iviglious ,
Thanks for posting in Microsoft Fabric Community,
The reason for multiple identical queries might result from:- Power Query processes transformations only when necessary. If a query is referenced multiple times for things like validation, previews, or transformations -it runs the query again, leading to duplicates.
- Additionally, Power Query tries to push as many transformations as possible back to the data source (query folding). During this process, it may send extra queries to fetch metadata or preview data, even if they’re identical.
- Features like data privacy checks and parallel loading can also trigger additional queries.
When you use Table.Buffer, Power Query executes the query immediately and stores the data in memory. All subsequent steps or references to this data will use the in-memory dataset instead of re-querying the database.
In addition to disabling all functionalities and lowering the settings to minimum in PowerBI Desktop as given in the document, also disable Parallel Loading of Tables in Power BI Desktop to prevent concurrent executions that contribute to multiple queries.
Hope it helps. Please reach out for further assistance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.
Best Regards.- iviglious
Thank you for your reply.
I tried to keep my example to the minimum as you can see.
- There is no query folding - no transformations
- Parallel loading - yes, looked into that and it is also set to minimum (one).
I did various tests comparing Scenario A and Scenario B and Scenario B comes slower.
It still appears to me that Odbc.Query function executes unecessary second query, because it essentially truncates the result of the second query. As if it is executed just to see if it's running (validation) and once that's done it is not needed, the first query is the one that actually is used to return the data and it is not truncated.
Additional evidence of the "strange" behaviour of Odbc.Query function is that when using the native function of the souce DB engine (in my case Starburst): Value.NativeQuery(StarburstPresto.Contents(…), …) only one query is triggered. So, seems like it is possible to load data into PBI using only one query, but for some reason Odbc.Query triggers two.
So is it at all not possible to look into what Odbc.Query function is doing and try to optimize it?
- v-veshwara-msft
Hi iviglious ,
Thanks for the update.
This is a known issue experienced by many users and has already been posted as an idea in the Ideas Portal:
Here is the link:Microsoft Idea
You can upvote it to increase visibilty.
Additionally, a similar discussion can be found here:Solved: Multiple duplicate queries while report refresh - Microsoft Fabric Community
Possible reasons why Odbc.Query triggers two queries:From your findings, it looks like Power Query is running an additional validation query before retrieving the actual data. This is likely due to:
- Schema validation: Power Query may run an initial query to check schema consistency before executing the main query.
- Connection validation: Some ODBC drivers issue test queries to confirm connectivity.
- Internal processing differences: Unlike Value.NativeQuery, which directly passes the query to the source, Odbc.Query might involve additional internal steps before final execution.
While Table.Buffer ensures only one query execution, it forces Power Query to load the entire dataset into memory, which can slow down performance, especially for large datasets.
Your tests confirm that Value.NativeQuery(StarburstPresto.Contents(…), …) avoids unnecessary queries and performs efficiently. Using this approach where possible would be the workaround.
If this issue significantly impacts performance or costs, consider raising a support ticket with Microsoft. How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.
Best Regards,
Vinay Kumar.
- v-veshwara-msft
Hi iviglious ,
We wanted to follow up and check if the solution provided addressed your needs. If so, kindly mark it as a solution to assist others. If you require further assistance, please don't hesitate to reach out.
Thank you!
- v-veshwara-msft
Hi iviglious ,
Just following up as we haven’t heard from you in a while. Since you've already created and upvoted the Idea, we hope it gains enough traction and visibility for Microsoft to consider it in a future release.
If you have any further questions or need assistance, please feel free to start a new thread in the Microsoft Fabric Community Forum, we’ll be happy to help.
We may consider closing this thread for now.
Thank you for being part of the Microsoft Fabric Community.
