Data extraction from a single SQL Server table into Power BI using the import method is very slow

[RAVI20035]

Hello Power BI Community,

I am facing a performance issue while extracting data from SQL Server into Power BI.
Here are the details:

Source: Microsoft SQL Server
Table: Single table (around [mention number of rows, e.g., 20 million rows])
Method: Import mode (not DirectQuery)

Problem:
When I try to import the table into Power BI, the data extraction process is very slow.
It extracts only 20–30 records at a time, and loading the entire table is taking a very long time.

Steps I have already tried:

Simplified the query (basic SELECT * FROM table) with no index .

Questions:

How can I improve the data extraction speed from SQL Server to Power BI?
Is there any optimization on Power BI settings or SQL Server side I should consider?

[V-yubandi-msft]

Hi @RAVI20035 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[V-yubandi-msft]

Hi @RAVI20035 ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

Thank You.

[V-yubandi-msft]

Hi @RAVI20035 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you.

[V-yubandi-msft · Accepted solution]

Hi @RAVI20035 ,

As mentioned @lbendlin  earlier, having many wide nvarchar columns, especially nvarchar or other large sized fields, can significantly slow down data transfer between SQL Server and Power BI.

To enhance performance, consider these best practices.

1. Import only necessary columns to reduce data volume.

2. Exclude or trim large text fields like nvarchar unless absolutely necessary.

3. Apply filters at the source using SQL views or Power Query to limit both rows and columns before loading into Power BI.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[lbendlin]

- what else is happening on that SQL server?

- what is the network performance?

- how many columns with nvarchar(max) ?

[RAVI20035]

Thanks for the quick replay. 

- what is the network performance?  ---   Permission was denied and is there any other way to check?

- how many columns with nvarchar(max) ?

DataTypeWithSizeNo of Columns

	89
nvarchar(4000)	9
nvarchar(2500)	1
nvarchar(1500)	1
nvarchar(1000)	1
nvarchar(522)	1
nvarchar(400)	12
varchar(400)	2
nvarchar(362)	1
nvarchar(300)	1
nvarchar(200)	38
nvarchar(150)	4
nvarchar(100)	18

[lbendlin]

That's a lot of wide columns you have there... All these bytes need to go over the wire. if your SQL server is weak and the network pipe is small and congested then there is no surprise it takes so long.