Which parameters affects most on executing query using PowerBI? What is important on server side? Do HDD disks slows very much and choosing SSD is much better? Is any standards that mysql DB should fulfill to to make Power Bi run faster? Is there options/settings that I can turn on/off on user site to speed up downloading data? Which functions should I avoid in writing queries?
Asked
Active
Viewed 181 times
0
-
The best speedup for BI (in my opinion) is to build and maintain [_Summary tables_](http://mysql.rjweb.org/doc.php/summarytables) . (I do not know anything about "PowerBI", so I can't relate to it.) – Rick James Oct 15 '21 at 19:57
-
Are you using direct query or importing your data? The answer to this question can eliminate some things that you might otherwise need to check. – trenton-ftw Oct 15 '21 at 20:48
1 Answers
0
Below are few suggestions from my experience
You can use indexing on database columns in mysql side
I used CTE format for things speedup, It reduces 5,6 minutes execution/power bi loading time to 6,7 seconds.
Used mysql views instead of stored procedures and direct queries.
Upgraded database processor.
Reduces inner joins on queries.
Manage most suitable conditions for queries and select only imported ones.
-
I read about CTE "They simplify and improve the clarity of SQL code. In this respect, their use does not affect the query performance, only its readability. In addition to the purely aesthetic function, they have one more special property - their structure allows for recursion." Source is here (but it is Polish) https://www.sqlpedia.pl/cte-common-table-expressions/ – Mateusz P Oct 15 '21 at 10:42
-
A MySQL `VIEW` do not speed up the query. If you have an example where it does, I would love to see it. – Rick James Oct 15 '21 at 19:55
-
Instead of using stored procedures you can us views. (without parameters) – Mr Robot Oct 17 '21 at 01:53