Querying 2 different SQL Servers with direct query works in Desktop App but not in Power BI Service

[TallPolishAdam]

I'm querying two different databases across two different SQL Servers in direct query mode. When doing so in the Power BI Desktop app I see no indication that my report is running slow. However, when I publish to the Power BI Service the refresh times become very slow and eventually all the visuals stop working.

The two servers are accessed using two different gateways. Does this only work in the desktop Power BI app? If not, why am I having so many more problems in the web-version of my report? I should mention I am only grabbing a single row (the latest row) from each table so I don't think the queries themselves should be an issue.

[edhans · Accepted solution]

You will probably need to investigatge the network performance between the service, your gateway, and then your servers. The service doesn't perform better or worse than the desktop. it performs differently as it isn't behind your firewall, and it has to use a gateway, something Power BI Desktop doesn't need. An improperly configured machine hosting a gateway can lead to bad performance.  Monitor and optimize on-premises data gateway performance | Microsoft Learn can give you more info on what to investigate there.

[TallPolishAdam]

That makes sense, but just to be clear connecting to multiple servers/databases with direct query should not cause an issue.

[edhans]

Well, true. That should not cause an issue. But understand that DQ performs in order of magnitudes worse than import models. So it is never going to be snappy compared to what the built in vertipaq engine can do. 

Think of using a cached system or an uncached system. DQ is uncached.