Hello! I was just wondering if anyone else has experienced very poor performance using Direct Query on an Azure SQL Database. I have limited the number of rows that I am using in my queries but this doesnt seem to make much of a difference. Even with > 100 DTU's allocated in the Azure SQL Database, any query made from power BI pushes the DTU percentage to 100% and power BI still seems to process quite slowely. On my largest table, I have < 500 thousand rows and < 20 columns, which I don't see as an unreasonable amount. Most of the other tables have < 100 thousand. How can I improve performance? I realize that importing is the ideal situation for power BI, but I believe direct query to be more scalable in terms of storage; I previously ran everything importing but I had too many records to actually schedule a refresh and power BI would break.
Solved! Go to Solution.
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.
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.
In addition to other's post, you can consider to use In-Memory technology in Azure SQL database to optimize performance.
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.
I started looking into this as it seemed like it might help lower my DTU usage. Unfortunately, you dont get any OLTP storage on the standard plan, and you need to spend more than I have to get an adequate amount of storage space on the Premium plan, if I understand properly.