Slow upload into PBi Service via data gateway (personal mode)

[HendrixSpirit]

I made this question on Service forum, but it seems more appropiate to do it here. So i erase it from the other forum

I'm using PowerBi Service connected to an in premises Microsoft SQL Server 2012 via a data gateway (personal mode).

The database of SQL Server is more than 50.000.000 lines and 78 columns, but what I import into PBI  is only one table with aprox 3.500.000 rows and 37 columns.  That table increases around 80.000 rows daily.

After using PowerQuery in PBI Desktop to filter and delete some rows, my Power Bi Desktop file weights 248 MB. I also do one small dax calculation for some new columns (only concatenating 1 row to create a hyperlink direct to google, nothing fancy)

The first publishing (PBI) of that PBI desktop file takes a while (250 mb) takes around 1 hour to upload... Slow, but I can manage it for being the first upload.

The problem is when refreshing the dataset daily by Data gateway (P, it takes 1 hour and 40 minutes to upload the 3.500.000 rows + the "new" 80.000 rows. 1 hour and 40 minutes shows a rate of 0,041 MB per second... 

As far as I know, there is not a possibility of incremental load of only the the new 80.000 rows..

So my question is:

¿I am doing something wrong? It really takes that long (almost two hours) to upload 3.500.000 rows of 37 columns, with the PBI Desktop file equivalent is 250 mb?

I checked the native query for the table, it was folding on the Sql Server,, and all looked ok. 

Refreshing the 3.500.000 rows in PBI Desktop takes only 1 minute, so I don't think the problem is on the query...

I'm using the pro version on test mode (50 days remaining), to do this tests before going to propose PBI as a solution to my bosses...

[HendrixSpirit]

I'm using PowerBi Service connected to an in premises Microsoft SQL Server 2012 via a data gateway (personal mode).

The database of SQL Server is more than 50.000.000 lines and 78 columns, but what I import into PBI  is only one table with aprox 3.500.000 rows and 37 columns.  That table increases around 80.000 rows daily.

After using PowerQuery in PBI Desktop to filter and delete some rows, my Power Bi Desktop file weights 248 MB. I also do one small dax calculation for some new columns (only concatenating 1 row to create a hyperlink direct to google, nothing fancy)

The first publishing (PBI) of that PBI desktop file takes a while (250 mb) takes around 1 hour to upload... Slow, but I can manage it for being the first upload.

The problem is when refreshing the dataset daily by Data gateway (P, it takes 1 hour and 40 minutes to upload the 3.500.000 rows + the "new" 80.000 rows. 1 hour and 40 minutes shows a rate of 0,041 MB per second... 

As far as I know, there is not a possibility of incremental load of only the the new 80.000 rows..

So my question is:

¿I am doing something wrong? It really takes that long (almost two hours) to upload 3.500.000 rows of 37 columns, with the PBI Desktop file equivalent is 250 mb?

I checked the native query for the table, it was folding on the Sql Server,, and all looked ok. 

Refreshing the 3.500.000 rows in PBI Desktop takes only 1 minute, so I don't think the problem is on the query...

I'm using the pro version on test mode (50 days remaining), to do this tests before going to propose PBI as a solution to my bosses...

[v-yuezhe-msft]

@HendrixSpirit，

In Power BI Service, it depends on several factors that how long a refresh will take.

1. The size of data model in the dataset.
2. The complexity of data model.
3. The network connection between Gateway and data source.
4. The network connection between the Gateway and Power BI Service.
4. CPU & Memory resource in the machine that installing gateway.

In your scenario, I would recommend you install gateway on the machine that installing SQL Server, check if there are resource bottlenecks, network bottlenecks. And you can  also use DirectQuery mode instead to connect to the SQL database.

There is also a blog for your reference.
http://www.fourmoo.com/2017/03/21/power-bi-gateway-infographic/


Regards,
Lydia