Power BI load from SQL Server extremely slow

[Anonymous]

Hi all,

I am trying to load a SQL Server table which is ~45M rows (no transformations, just a table load), but it's slow to the point of unusable. Does anyone have experience dealing with this? Is 45M simply too much? Note that this is not Azure SQL, so I don't have the option of increasing the DTUs.

[Anonymous]

Hi @Anonymous

If the amount of data is too large, it will really affect the performance of the load.

It is generally recommended that you first choose transform data, do a cleaning modification of the data, you can delete some useless, redundant data or other operations, and then import into the power bi desktop, which will effectively improve performance.

Related link:

Optimization guide for Power BI - Power BI | Microsoft Docs

Best regards, 

Community Support Team Selina zhu 

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

[Anonymous]

Hi there. Thank you for your reply, Of course, I know I can do transformations, which I have already done to reduce the size significantly. I was mainly wondering if I can do anything to improve performance other than that, and in general what the Power BI limitations are.

[selinaz]

Hi @Anonymous ,

The main factors affecting power bi performance are these：

related link:

Optimization guide for Power BI - Power BI | Microsoft Docs

You can optimize these to improve performance.

If you are dealing with reports or visualization processes, you can refer to this link to find out the reasons for the impact：Monitor report performance in Power BI - Power BI | Microsoft Docs

You also mentioned that you want to know the limitations of power bi, I don't know what you want to know, if you just mean the performance, the above document you can learn more about.
If it's something else you can tell me what other limitations you want to know.

Best regards, 

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

[Anonymous]

Hi selinaz,

Appreciate your reply, but maybe I wasn't clear in my question. This was more a "tips and tricks" question specific to SQL Server or other traditional DBs. It was not a general performance question, of which I am familar with general performance techniques. Again, I appreciate the reply, and sorry for the confusion.

[Anonymous]

Hi @Anonymous ,

You can write specific SQL statements to get specific data you want to import into power bi desktop when connecting to the data source, which not only reduces useless data, but also reduces the amount of data to improve the import speed.

Best regards, 

Community Support Team Selina zhu 

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

[Anonymous]

hey mengzhu, thanks for the reply, but again, this does not solve my issue

The data set is already as small as it can be. The question is simply whether or not there is a trick to load the data faster. That's it. If there is no solution, that's fine, but that was the question.

[Anonymous · Accepted solution]

Hi @Anonymous ,

A common way to optimize data loading speed in general is to use the two methods mentioned above: "transform first, then load" and using query statements to extract specific data. Other methods, as far as I know, are related to the network environment, the speed of the computer itself and other hard settings. I am not aware of any settings to make the data faster as you said, maybe you can suggest your ideas in this link:Ideas (powerbi.com).

Best regards, 

Community Support Team Selina zhu 

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

[Anonymous]

got it thank you

[amitchandak]

@Anonymous , I have loaded and it takes time. So I have setup incremental to load only limited data in power bi desktop and rest load in power bi service.

[Anonymous]

Hi, thank you for your reply, but I don't understand completely. Do you mean you have a separate test data source, or you import a limited set from the main data source, then publish and the rest is loaded? If the latter, can you expand a little on how you do this?