Forum Discussion
Poor Performance using Azure SQL Database and Direct Query
supton,
I am afraid that there is no way around this. Generally speaking, import models will be faster, and 500 thousand rows is not a large dataset, it is recommended that you use “Import” mode to import data to Power BI.
Regards,
Lydia
Have you checked that your database is properly indexed? I mean, if you have no indexes and its trying to join 100k records in 1 table to 500k records in another table... that *is* gonna leave a mark.
Also, maybe a question for a different thread but does power BI load the tables per page of a report? I only ask because several pages seem to load just fine for me, usually those that only reference 2 or 3 tables. But the one I am referring too being extremely slow references many different tables. I even have some of the exact same visualizations on this and other tables and they seem to load much more quickly on the other pages.
I'm not well versed on how Power BI makes its magic SQL queries, but... at any rate, I would certainly treat this as a straight SQL perf problem. Take a profiler trace on SQL or maybe use sp_whoisactive ... while the performance is making you sad, and see what is going on w/ the query plans, etc.
It's possible you need to optimize your database a bit for the types of queries Power BI is sending.
I would want to agree, but I can run straight sql queries against the database and they run without a problem, much more quickly than power BI seems to handle them and with much less effort. The bottleneck here appears to be Power BI.