Query Time difference between Service and Desktop Reports, using same Dataset

[Anonymous]

Hi all

I have a Dataset hosted in a premium capacity on the Service. It is a composite model, with only one table DirectQuery. The DQ element is connecting to Redshift thorugh an EC2 gateway.

When I open a report In Desktop that is connected to the Dataset, the query load time is quick.

When I open the same report in Service, the query time is on average 5 times slower. The times are unacceptable!

I have monitored the query times on the dataset using DAX Studio, and the results are at the bottom of this post.

I am confused why the same queries on the same dataset have different results depending on where the report is hosted.

Time to Complete Query (ms)
Query #	Power BI Desktop - Live Connection	Power BI Service
1	3,676	19,000
2	4,035	17,342
3	4,224	7,806
4	6,274	20,606
5	4,349	19,870
6	4,646	22,642
7	3,221	19,541
8	12,694	173,731
9	12,263	143,050
10	14,022	206,633
11	13,278	155,887
12	5,506	20,934
13	6,227	22,580
14	0	16
15	3,000	16,873
16	16	16

[edhans · Accepted solution]

a whole host of issues can be impacting it - data latency depending on where the data is vs the source reading it, the memory and available capacity of the source reading it, etc. If you have a P1 premium capacity that will not perform as well as most PCs will with the same dataset. 

You should understand the performance implications of a composite model like that by reading this article. Relationships in Power BI and Tabular models - SQLBI

It may not be the best choice, or you need to rethink how it is designed and related to the imported data.

[Anonymous]

Thanks for the information. I'll check out the links and try explore the whole hosts of issues.

For now, I have removed the DQ element and it is a lot more performant and not dependent on gateways. Will read that article!