Forum Discussion
Advocate IOdbc.Query triggering only 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.
Advocate IHi Anonymous
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?
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.
- iviglious
Hi
I had opened a Service Request #: 2501130040006322, but I couldn't convince them that it impacts performance or cost, so we closed the ticket with the only solution of creating an idea here.
Thanks for searching. I upvoted the idea you posted. The solved discussion doesn't work for me as I already disabled the background data preview option.
I don't think the duplication of queries is caused by the ODBC driver.
When using Tableau and their ODBC connection with the same ODBC driver, only one query is triggered. So, it seems to me a PowerBI design/bug of the Odbc.Query function, because other analytical products don't exhibit such a behaviour when using ODBC.
We have 100 reports which refresh daily. Each query executed at our source DB engine costs us $0.01 Because each report’s refresh triggers 2 queries: 100 x $0.01 x 2 = $2 per day. If we use Tableau these report’s refreshing will cost us $1 per day (50% more cost effective).
I analysed deeper and Scenario C (Value.NativeQuery(StarburstPresto.Contents) also triggered two identical queries + 3 checking queries (which we can ignore as they took less than a second).
Using Table.Buffer only triggers one query, but that is not a solution for the “inefficient” Odbc.Query function, but rather a cover-up for it.I have a case where I have parallel processing of 15 partitions and tested both functions: Odbc.Query and Table.Buffer. The partitions with Odbc.Query function triggered each 2 queries (instead of 1), so in total I saw 30 queries running for about 5-6 minutes and then it was needed 1-2 more minutes for the refresh to finish. Total of 7-8 minutes. When using partitions with Table.Buffer(Odbc.Query only 15 queries were running (one per partition) and they finished faster 2-3 minutes, but then the refresh was continuing for further 7-8 minutes. Total of 10-11 minutes.
Using Table.Buffer is cheaper as the queries are less and run faster, but then it takes more total time for the processing of the data than Odbc.Query. If only Odbc.Query can get optimized to not execute that second query it will be the best solution (middle point between cost and total processing time).I hope you agree that Odbc.Query and Value.NativeQuery functions trigger more queries than needed and this idea of trying to improve them is valid. It's your product that I suggest to improve to make it more competitive. It is your choice to accept it and work on it or not.
From my side, I can't accept Table.Buffer as solution for this idea.
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.