Forum Discussion
Odbc.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.
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.