Parallel Parameters
FastTransfer can parallelize data export to significantly improve performance. This section covers the parameters that control parallel execution.
Parallel Method
Use the --parallelmethod parameter to specify how data will be split across parallel threads.
- Windows
- Linux
.\FastTransfer.exe `
...
--parallelmethod Ntile `
...
./FastTransfer \
...
--parallelmethod Ntile \
...
--parallelmethoddefault None--parallelmethod <method>
Available Methods
PhysicalSQL Server, PostgreSQL, Oracle, NetezzaPhysical location of the rows, chosen by FastTransfer for the source engine.
DBPartitionSQL Server, PostgreSQL, Oracle, MySQL, Teradata, Db2One stream per native partition of the source table.
NoneAny sourceNo split, one stream.
RangeIdAny sourceNumeric ranges of the key.
NtileAny sourceEven slices of the key (number, date or string).
RandomAny sourceModulo of the key.
DataDrivenAny sourceOne stream per distinct value of the key (or of --datadrivenquery).
TimepartitionAny sourceOne stream per year, month, day or week of a date column.
- A large table on SQL Server, PostgreSQL, Oracle or Netezza: start with
Physical. No key column to choose. - A partitioned table:
DBPartition. - An integer key without big gaps (identity, sequence):
RangeId. - Any other key, or a
--querysource:Ntile, orTimepartitionon a date column.
Each method in detail
Physical - Uses the physical location of the rows. FastTransfer picks the right technique for the source: Ctid on PostgreSQL, Rowid on Oracle, Physloc on SQL Server (rowstore and clustered columnstore tables), data slices on Netezza. Requires --sourceschema and --sourcetable (not on Netezza). A --query can be added to filter the table. See Physical below.
DBPartition - Uses the native partitions of the source table: one transfer per partition, biggest partitions first. Requires --sourceschema and --sourcetable; --query is not allowed. See DBPartition below.
RangeId - Splits a numeric key into ranges. Best with an identity column or a sequence without big gaps.
Ntile - Splits the key into slices of the same number of rows. The key can be a number, a date or a string. Rows with a NULL key are transferred in a stream of their own.
Random - Uses a modulo on the key.
DataDriven - One transfer per distinct value of the key, or per value returned by --datadrivenquery. The NULL value is one of them.
Timepartition - Splits the data on the time parts of a date column: one transfer per year, month, day or ISO week. --distributekeycolumn uses a tuple such as "(order_date,year,month)". See Timepartition below.
You can use an expression instead of a column name in the distribute key column.
Example: YEAR(o_orderdate)
Ctid, Rowid, Physloc and NZDataSlice are replaced by Physical. They still work in 1.0, with a warning, and will be removed in 2.0.
If you want to use a parallel export/import use other than None. Try to use Parallelism only if you have a large amount of data to transfer (more than 1M cells).
Distribute Key Column
Use the --distributekeycolumn parameter to define the column (or computation) on the data source that will be used to split the data into several parts.
FastTransfer will use SQL queries that run in parallel against the source. Each query will have a WHERE clause that retrieves a part of the total data.
This parameter is mandatory when using methods that require a distributed column: Random, DataDriven, RangeId, Ntile or Timepartition. FastTransfer stops with an error if it is missing.
With the Timepartition method, --distributekeycolumn is a tuple made of a date column and time parts: "(order_date,year)", "(order_date,year,month)", "(order_date,year,month,day)" or "(order_date,year,week)". See Timepartition.
- Windows
- Linux
# Using a column name
.\FastTransfer.exe `
...
--distributekeycolumn order_date `
...
# Using an expression
.\FastTransfer.exe `
...
--distributekeycolumn "YEAR(order_date)" `
...
# Using a column name
./FastTransfer \
...
--distributekeycolumn order_date \
...
# Using an expression
./FastTransfer \
...
--distributekeycolumn "YEAR(order_date)" \
...
--distributekeycolumn--distributekeycolumn <column_name>
Degree of Parallelism
Use the --degree parameter to control how many parallel threads will be used for the export.
--degreedefault -4--degree <value>
DOP Values
n > 0e.g. 4Uses exactly that number of parallel threads. If greater than twice the number of CPU cores/threads, it will be downscaled to the number of available cores.
0Automatically aligns with the number of cores (or threads if Hyper-Threading is enabled) on the machine.
n < 0e.g. -2Computed as number of cores / abs(dop). For example, if you have 16 cores and set DOP to -2, the actual DOP will be 8.
- Windows
- Linux
# Use 8 parallel threads
.\FastTransfer.exe `
...
--degree 8 `
...
# Auto-detect based on CPU cores
.\FastTransfer.exe `
...
--degree 0 `
...
# Use half of available cores
.\FastTransfer.exe `
...
--degree -2 `
...
# Use 8 parallel threads
./FastTransfer \
...
--degree 8 \
...
# Auto-detect based on CPU cores
./FastTransfer \
...
--degree 0 \
...
# Use half of available cores
./FastTransfer \
...
--degree -2 \
...
Data Driven Query
Use the --datadrivenquery parameter when using the DataDriven method to provide a query that returns the list of values that will be used to split the data. This allows you to filter the values that will be exported and used to split the data.
--datadrivenquery--datadrivenquery "<query>"
- Windows
- Linux
.\FastTransfer.exe `
...
--datadrivenquery "SELECT tagname FROM tags" `
...
.\FastTransfer.exe `
...
--datadrivenquery "SELECT o_orderdate FROM dim_date where ref_date > getdate() - 30" `
...
./FastTransfer \
...
--datadrivenquery "SELECT tagname FROM tags" \
...
./FastTransfer \
...
--datadrivenquery "SELECT o_orderdate FROM dim_date where ref_date > getdate() - 30" \
...
Physical
The Physical method splits the source table on the physical location of its rows. It needs no key column, and each stream reads its own part of the table.
ctid (page ranges)Technique used on PostgreSQL.
ROWID rangesTechnique used on Oracle.
%%physloc%%Technique used on SQL Server (rowstore and clustered columnstore).
Data slicesTechnique used on Netezza.
Other sources are refused: use another method.
- Windows
- Linux
.\FastTransfer.exe `
--sourceconnectiontype "pgsql" `
--sourceserver "localhost:5432" `
--sourceuser "postgres" `
--sourcepassword "mypassword" `
--sourcedatabase "tpch" `
--sourceschema "public" `
--sourcetable "lineitem" `
--targetconnectiontype "msbulk" `
--targetserver "localhost" `
--targettrusted `
--targetdatabase "tpch" `
--targetschema "dbo" `
--parallelmethod "Physical" `
--degree 8 `
--targettable "lineitem" `
--loadmode "Truncate"
./FastTransfer --sourceconnectiontype "pgsql" --sourceserver "localhost:5432" --sourceuser "postgres" --sourcepassword "mypassword" --sourcedatabase "tpch" --sourceschema "public" --sourcetable "lineitem" --targetconnectiontype "msbulk" --targetserver "localhost" --targettrusted --targetdatabase "tpch" --targetschema "dbo" --parallelmethod "Physical" --degree 8 --targettable "lineitem" --loadmode "Truncate"
On PostgreSQL, Oracle and SQL Server, add a --query to filter the table, for example --query "SELECT * FROM public.lineitem WHERE l_shipdate >= '2025-01-01'". --sourceschema and --sourcetable are still required.
DBPartition
The DBPartition method reads the partitions of the source table from the database catalog and transfers each partition in its own stream. The biggest partitions are transferred first, so the parallel threads stay busy until the end.
How each partition is read depends on the source:
SQL ServerPartitions of the partition scheme.
PostgreSQLLeaf partitions (sub-partitions included).
OraclePartitions and sub-partitions.
MySQLPartitions and sub-partitions.
TeradataPopulated row partitions (PPI / MLPPI).
Db2 LUWData partitions (range partitioning).
- Windows
- Linux
.\FastTransfer.exe `
--sourceconnectiontype "mssql" `
--sourceserver "localhost" `
--sourcedatabase "tpch" `
--sourcetrusted `
--sourceschema "dbo" `
--sourcetable "lineitem_part" `
--targetconnectiontype "pgcopy" `
--targetserver "localhost:5432" `
--targetuser "postgres" `
--targetpassword "mypassword" `
--targetdatabase "tpch" `
--parallelmethod "DBPartition" `
--degree 8 `
--targetschema "public" `
--targettable "lineitem" `
--loadmode "Truncate"
./FastTransfer \
--sourceconnectiontype "mssql" \
--sourceserver "localhost" \
--sourcedatabase "tpch" \
--sourcetrusted \
--sourceschema "dbo" \
--sourcetable "lineitem_part" \
--targetconnectiontype "pgcopy" \
--targetserver "localhost:5432" \
--targetuser "postgres" \
--targetpassword "mypassword" \
--targetdatabase "tpch" \
--parallelmethod "DBPartition" \
--degree 8 \
--targetschema "public" \
--targettable "lineitem" \
--loadmode "Truncate"
This example transfers a SQL Server table partitioned by month on l_shipdate: each monthly partition is loaded into PostgreSQL by one of the 8 parallel threads.
DBPartition works best when the source partitions have similar sizes. If the table is not partitioned, or the source type is not supported, FastTransfer logs a warning and falls back to None (a single stream): use another method, such as Timepartition or Ntile, instead.
Timepartition
The Timepartition method splits the data on the time parts of a date column. FastTransfer first runs a query that groups the source by these time parts and counts the rows of each group. Then it transfers each group in its own stream, biggest first.
--distributekeycolumn is a tuple made of the date column and the time parts:
(date_column,year)(date_column,year,month)(date_column,year,month,day)(date_column,year,week)- The source can be a table (
--sourceschema/--sourcetable) or a--query. With a query, the date column must be in the query output. - Rows with a NULL date are transferred in a dedicated stream.
- The date bounds are sent to the source in the
yyyy-MM-ddformat. If your source needsyyyyMMdd, use the FASTTRANSFER_DATEFORMAT environment variable.
- Windows
- Linux
.\FastTransfer.exe `
--sourceconnectiontype "mssql" `
--sourceserver "localhost" `
--sourcedatabase "tpch" `
--sourcetrusted `
--targetconnectiontype "pgcopy" `
--targetserver "localhost:5432" `
--targetuser "postgres" `
--targetpassword "mypassword" `
--targetdatabase "tpch" `
--query "SELECT * FROM dbo.lineitem WHERE l_shipmode = 'AIR'" `
--parallelmethod "Timepartition" `
--distributekeycolumn "(l_shipdate,year,month)" `
--degree 8 `
--targetschema "public" `
--targettable "lineitem_air" `
--loadmode "Truncate"
./FastTransfer \
--sourceconnectiontype "mssql" \
--sourceserver "localhost" \
--sourcedatabase "tpch" \
--sourcetrusted \
--targetconnectiontype "pgcopy" \
--targetserver "localhost:5432" \
--targetuser "postgres" \
--targetpassword "mypassword" \
--targetdatabase "tpch" \
--query "SELECT * FROM dbo.lineitem WHERE l_shipmode = 'AIR'" \
--parallelmethod "Timepartition" \
--distributekeycolumn "(l_shipdate,year,month)" \
--degree 8 \
--targetschema "public" \
--targettable "lineitem_air" \
--loadmode "Truncate"
This example transfers the AIR shipments month by month, with 8 parallel threads.
Pick a granularity that gives several times more groups than the degree of parallelism, but not tens of thousands of small groups. For 7 years of data, (col,year,month) gives about 84 streams, which is a good fit for 8 to 16 threads.
SQL Server target and sorted hint
With a partitioned SQL Server target (msbulk), the DataDriven, DBPartition and Timepartition methods send a sorted hint on the target partition column to the bulk copy. This hint is not sent when the target table has a rowstore clustered index: SQL Server would check the order of the rows and reject the load. Set FASTTRANSFER_SORTEDINPUT to true to force the hint when your source data is really sorted.
Complete Example
Here's a complete example using parallel parameters with the DataDriven method:
| This example | |
|---|---|
| Source | A SQL Server database on localhost, a custom query on the orders table |
| Target | A SQL Server database, loaded with BulkCopy |
| Authentication | Trusted, for both source and target |
| Method | DataDriven, on the month of the order date (o_ordermonth column) |
| Values | Generated by a custom query, instead of a SELECT DISTINCT o_ordermonth from the source query |
| Threads | 10 parallel threads (even though there are 12 months to export) |
- Windows
- Linux
.\FastTransfer.exe `
--sourceconnectiontype "mssql" `
--sourceserver "localhost" `
--sourcedatabase "tpch" `
--sourcetrusted `
--targetconnectiontype "msbulk" `
--targetserver "localhost" `
--targetdatabase "tpch_copy" `
--targettrusted `
--targetschema "dbo" `
--targettable "orders" `
--query "select * from orders" `
--parallelmethod "DataDriven" `
--distributekeycolumn "o_ordermonth" `
--datadrivenquery "SELECT to_char(d, 'YYYYMM') AS month FROM generate_series(DATE '1998-01-01', DATE '1998-12-01',INTERVAL '1 month') AS d" `
--degree 10
./FastTransfer \
--sourceconnectiontype "mssql" \
--sourceserver "localhost" \
--sourcedatabase "tpch" \
--sourcetrusted \
--targetconnectiontype "msbulk" \
--targetserver "localhost" \
--targetdatabase "tpch_copy" \
--targettrusted \
--targetschema "dbo" \
--targettable "orders" \
--query "select * from orders" \
--parallelmethod "DataDriven" \
--distributekeycolumn "o_ordermonth" \
--datadrivenquery "SELECT to_char(d, 'YYYYMM') AS month FROM generate_series(DATE '1998-01-01', DATE '1998-12-01',INTERVAL '1 month') AS d" \
--degree 10
- Transfer Parameters
--sourceschema,--sourcetableand--query - ADBC Connectionsthe parallel methods with
adbc_*sources - Environment variables
FASTTRANSFER_DATEFORMATandFASTTRANSFER_SORTEDINPUT