Skip to main content
Version: 1.0 (Latest)

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.

.\FastTransfer.exe `
...
--parallelmethod Ntile `
...
--parallelmethoddefault None

--parallelmethod <method>

Available Methods​

PhysicalSQL Server, PostgreSQL, Oracle, Netezza

Physical location of the rows, chosen by FastTransfer for the source engine.

Key columnNot needed
DBPartitionSQL Server, PostgreSQL, Oracle, MySQL, Teradata, Db2

One stream per native partition of the source table.

Key columnNot needed
NoneAny source

No split, one stream.

Key columnNot needed
RangeIdAny source

Numeric ranges of the key.

Key column--distributekeycolumn
NtileAny source

Even slices of the key (number, date or string).

Key column--distributekeycolumn
RandomAny source

Modulo of the key.

Key column--distributekeycolumn
DataDrivenAny source

One stream per distinct value of the key (or of --datadrivenquery).

Key column--distributekeycolumn, optionally --datadrivenquery
TimepartitionAny source

One stream per year, month, day or week of a date column.

Key columnA date tuple in --distributekeycolumn
Which method to choose?
  • 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 --query source: Ntile, or Timepartition on 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.

tip

You can use an expression instead of a column name in the distribute key column.
Example: YEAR(o_orderdate)

Deprecated methods

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.

When to Use Parallelism

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.

Information

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.

Timepartition

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.

# Using a column name
.\FastTransfer.exe `
...
--distributekeycolumn order_date `
...

# Using an expression
.\FastTransfer.exe `
...
--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. 4

Uses 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.

0

Automatically aligns with the number of cores (or threads if Hyper-Threading is enabled) on the machine.

n < 0e.g. -2

Computed as number of cores / abs(dop). For example, if you have 16 cores and set DOP to -2, the actual DOP will be 8.

# 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 `
...

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>"

.\FastTransfer.exe `
...
--datadrivenquery "SELECT tagname FROM tags" `
...
.\FastTransfer.exe `
...
--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)PostgreSQL

Technique used on PostgreSQL.

Source typespgsql, pgcopy, adbc_pgsql
ROWID rangesOracle

Technique used on Oracle.

Source typesoraodp, adbc_oracle
%%physloc%%SQL Server

Technique used on SQL Server (rowstore and clustered columnstore).

Source typesmssql, msoledbsql, adbc_mssql
Data slicesNetezza

Technique used on Netezza.

Source typesnzsql, nzoledb, adbc_netezza

Other sources are refused: use another method.

.\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"
Filtering the table

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 Servermssql, msoledbsql, adbc_mssql

Partitions of the partition scheme.

Read withWHERE $PARTITION.<function>(<column>) = n
PostgreSQLpgsql, pgcopy, adbc_pgsql

Leaf partitions (sub-partitions included).

Read withThe leaf table is read directly
Oracleoraodp, adbc_oracle

Partitions and sub-partitions.

Read withPARTITION (...) / SUBPARTITION (...) clause
MySQLmysql

Partitions and sub-partitions.

Read withPARTITION (...) clause
Teradatateradata, adbc_teradata

Populated row partitions (PPI / MLPPI).

Read withWHERE PARTITION = n
Db2 LUWadbc_db2

Data partitions (range partitioning).

Read withA range predicate per partition
.\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"

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.

tip

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)
One stream per year
(date_column,year,month)
One stream per month
(date_column,year,month,day)
One stream per day
(date_column,year,week)
One stream per ISO 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-dd format. If your source needs yyyyMMdd, use the FASTTRANSFER_DATEFORMAT environment variable.
.\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"

This example transfers the AIR shipments month by month, with 8 parallel threads.

Choosing the granularity

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
SourceA SQL Server database on localhost, a custom query on the orders table
TargetA SQL Server database, loaded with BulkCopy
AuthenticationTrusted, for both source and target
MethodDataDriven, on the month of the order date (o_ordermonth column)
ValuesGenerated by a custom query, instead of a SELECT DISTINCT o_ordermonth from the source query
Threads10 parallel threads (even though there are 12 months to export)
.\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
Related
Copyright © 2026 Architecture & Performance.