topic Re: Shortening sql server import times in Pipelines

Shortening sql server import times

ebjim — Tue, 02 Apr 2024 19:03:12 GMT

I am looking for any suggestions to shorten the import time of one large table, given that in the near term, I'll be using a single node gateway and importing a binary file is not an option.

The table has about 53 million rows and 97 columns of various data types (datetime, decimal, varchar, boolean, smallint, tinyint, int). The destination is a lakehouse. Currently, I am using 5 degrees of copy parallelism, balanced intelligent throughput optimization and dynamic range partition option (the primary key is the partition column, with specified lower and upper bounds).

Are there settings, properties, etc. that I should change? Suggestions? Thanks in advance.

Re: Shortening sql server import times

Anonymous — Wed, 03 Apr 2024 16:27:07 GMT

Hi ebjim
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks

Re: Shortening sql server import times

dbrowne — Wed, 03 Apr 2024 16:32:26 GMT

It's impossible to suggest anything without more information. Eg what are the gateway VM specs? what is the CPU, Disk, and Network utilization percentage? What what is the table DDL? Is there a bottleneck on SQL Server?

Re: Shortening sql server import times

Anonymous — Wed, 03 Apr 2024 16:49:59 GMT

Hi ebjim
If you are importing this into a semantic model, you could look at incremental refresh, create partitions, review data types, only include columns needed (used for relationships, analysis, measures), verify if you have auto date/time turned off if not being used (this can potentially create very large hidden date time tables for each date time column they are importing), etc.

Is this historical or incremental load or truncate and load every time? If this is incremental, copy data to files section as LH (sink) does not support merge operation today. A copy activity followed by notebook or Dataflow gen2 is an option.

Hope this helps. Please let me know if you have any further queries.

Re: Shortening sql server import times

ebjim — Wed, 03 Apr 2024 17:04:11 GMT

We're using the latest Power BI Gateway version, but I don't have the resource stats available. No bottlenecks with the SQL Server as far as I know. I am more concerned with getting the parallelism and throughput optimization settings correct. As for DDL, I can send it to you separately if you are open to that.