MySQL Data - "Evaluating" taking excessive time

[Kletzy]

We are connecting to a MySql database sitting in Azure - I am troubleshooting by connecting a clean Power BI file to only 1 small table in the database containing only 14 rows (and around 10 columns) - there are no other connections or tables in the file - it is completely empty apart from this query. 

I am using the latest version Power BI desktop to test (we have the same issue when using dataflows). The refresh takes 1 minute, the majority of which is spent "Evaluating" (this is a small table the larger tables take much longer). There are no other connections to the server or DB and there is sufficient memory and processing power.

Are there any suggestions as to what we can do to speed this up please?

There are no transformations-below is the query:

let
Source = MySQL.Database("xxxxxxxx.mysql.database.azure.com", "xxxxxxx", [ReturnSingleDatabase=true]),
xxxxxxx_candidate_project_statuses = Source{[Schema="xxxxxx",Item="candidate_project_statuses"]}[Data]
in
xxxxxxx_candidate_project_statuses

Data load settings are below - no gateway is required:

Thank you for any thoughts.

[Anonymous · Accepted solution]

Hi @Kletzy 

The issue you're facing is slow refresh times in Power BI when connecting to a small table in a MySQL database hosted on Azure especially when the database and the query seem simple,There are several potential factors that could be causing this delay. 

Network Latency:

• Since your database is in Azure and you're likely connecting from a different location, network latency can be a significant factor.
• Test the network latency between your Power BI environment and the Azure server.

Database Server Performance:

• Check the performance metrics of your MySQL database server in Azure.
  Use a performance analysis tool for MySQL, such as MySQL Workbench or other third-party tools, to analyze the performance of your queries.
• Ensure that the server is not under heavy load and has adequate resources.

Query Performance:

• Although your query is simple, it's worth running it directly against the database to see how long it takes.
• Use MySQL’s 'EXPLAIN' statement to see if there are any inefficiencies in how the query is executed.
  

Azure Configuration:

• If you're using a lower tier of Azure's database service, you might experience slower performance. Consider scaling up the database service tier.
• Check if there are any throttling or resource limitation settings in Azure that might affect the performance.

Debugging Approach:

• Try connecting to a different MySQL database (maybe even a local one) and see if the issue persists. This can help determine if the problem is specific to the Azure environment or a broader issue with Power BI’s handling of MySQL databases.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amustafa]

Run a test like this first and see how your query performs first. 

let
Source = MySQL.Database("serverhost", "databasename", [ReturnSingleDatabase=true, Query="select COUNT(*) FROM schema.table;"])
in
Source