Odbc.Query triggering only one query

[iviglious]

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

[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 ,
Thanks for posting in Microsoft Fabric Community,
The reason for multiple identical queries might result from:

1. 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.
2. 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.
3. 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]

Hi @v-veshwara-msft 

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.

[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.

[v-veshwara-msft]

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.