PowerBI/Query are suddenly (!) extremely slow when trying to access ODBC database (PBI Desktop)

[Haruo_online]

Hi, everyone,

firstly many thanks for all the support and ideas, which are shared in this community. I was a silent reader for nwo two years and learnded a lot from this.

Now, comming to our problem and hope, someone has at least an idea where the problem my come from.

We are running a PowerBI project since more than a year using data from an database (InterSystems) using ODBC drivers. All worked well until last Monday. Since then, the data access became incredibly slow, even for just showing the preview in the navigator windows (where you select the ODBC classes you need for your project). Just the preview of a class can (for a larger class including several million lines) last several hours.

We are just talking about PBI desktop accessing ODBC classes on our internal servers!

As the only thing, which changed bevor we had the problem, was upgrading of PBI Desktop to May version, we deleted May version and reinstalled April version - with no success.

We checked our both severs which are involved: No failure.

Then we looked into the database as such and found an initial SQL statement coming from Power Query including a command to sort the table - which makes the access so slow.

But:

When we looked into PBI Deskton April version - that sent the same comand. So even this sorting command can not be to reason for our trouble - beside the fact, that such a sorting command would let many other users working with SQL-based databses scream as well.

So we are lost....

Does anyone have any suggestion, where we should continue our search?

Many thanks for any idea...
Heino

[ImkeF]

Hello @Haruo_online ,
2 things come to my mind that could cause query folding to break although you didn't change the M-code:

1. Mismatching privacy levels: If you combine different sources in your SQL query you must ensure that they have the same privacy level settings. I've experienced some weird behaviour where privacy levels in the service changed seemingly automatically. Could be because one of the sources was used in a different dataset that used that different pricacy setting. But it's definitely worth checking.
2. When you are using non-SQL sources in an inner join, that could bite you as well: SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel – The BIccountant
   
   
   

[Haruo_online]

Dear Imke,

thanks for your considerations - I am afraid they don't meet the source of the problem thow:

• I am starting with a blanc, fully new PBI report 
• then, I try to open an ODBC table - one which I am frequently using in other PBI reports
• and then it can take between minutes and up to several hours, just to see the preview of that file.

The SQL-statement I was talking about is a statement created automatocally by PBI/PQuery when I try to open this table and the only way to see it is looking to what arives at the database.

☹️

Heino