Skip to main content
Version: 0.18

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

Syntax:

  • Long form: --parallelmethod <method>

Available Methods​

None - No parallelism.

DataDriven - Use the distinct values of the --distributekeycolumn (which can be a column or an expression), or the values returned by --datadrivenquery, to distribute data. --distributekeycolumn is required.

tip

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

Ctid - Recommended and exclusive for PostgreSQL and PostgreSQL compatible databases. Use the Ctid pseudo column (for pgsql and pgcopy source only). Requires --sourceschema and --sourcetable, even if a --query is used.

note

Ctid parallel method is for PostgreSQL databases only.

Physloc - Uses an internal hidden field to retrieve chunks of data. Each parallel thread exports a portion based on the Physloc range.

note

Physloc parallel method is for SQL Server databases only.

Random - Use a modulo on the --distributekeycolumn to distribute data.

Rowid - For Oracle sources only: use rowid slices. Requires --sourceschema and --sourcetable, even if a --query is used.

note

Rowid parallel method is for Oracle databases only.

RangeId - Use a numeric range to distribute data (useful with an identity column or sequence without gaps. The column must be numerical).

Ntile - Use the ntile function to distribute data evenly. Data can be numerical, date, datetime or string.

NZDataSlice - Netezza source only. Use the data slices to distribute data retrieval. Requires --sourceschema and --sourcetable, even if a --query is used.

note

NZDataSlice parallel method is for Netezza databases only.

DBPartition - Use the native partitions of the source table: one transfer per partition, biggest partitions first. Requires --sourceschema and --sourcetable; --query and --fileinput are not allowed, and no --distributekeycolumn is needed. See DBPartition below.

note

DBPartition supports SQL Server (mssql, msoledbsql), PostgreSQL (pgsql, pgcopy), Oracle (oraodp), MySQL (mysql) and Teradata (teradata, row partitioning only) sources. If the table is not partitioned, has a single partition, or the source type is not supported, FastTransfer logs a warning and falls back to None.

Timepartition - Split 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)". Works with a source table or a --query. See Timepartition below.

Methods Comparison​

MethodParallelNeeds Distributed ColumnDatabase Source Type
NoneAny
DataDrivenAny
CtidPostgreSQL (pgsql/pgcopy)
PhyslocSQL Server (mssql)
RandomAny
RowidOracle (oraodp)
RangeIdAny
NtileAny
NZDataSliceNetezza
DBPartitionSQL Server, PostgreSQL, Oracle, MySQL, Teradata
Timepartition (date tuple)Any

Default Value: None

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)" `
...

Syntax:

  • Long form: --distributekeycolumn <column_name>

Degree of Parallelism​

Use the --degree parameter to control how many parallel threads will be used for the export.

DOP Values​

Positive value (e.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.

Negative value (e.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 `
...

Syntax:

  • Long form: --degree <value>

Default: -4

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.

.\FastTransfer.exe `
...
--datadrivenquery "SELECT tagname FROM tags" `
...
.\FastTransfer.exe `
...
--datadrivenquery "SELECT o_orderdate FROM dim_date where ref_date > getdate() - 30" `
...

Syntax:

  • Long form: --datadrivenquery "<query>"

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:

SourcePartitions usedHow a partition is read
SQL Server (mssql, msoledbsql)Partitions of the partition schemeWHERE $PARTITION.<function>(<column>) = n
PostgreSQL (pgsql, pgcopy)Leaf partitions (sub-partitions included)The leaf table is read directly
Oracle (oraodp)Partitions and sub-partitionsPARTITION (...) / SUBPARTITION (...) clause
MySQL (mysql)Partitions and sub-partitionsPARTITION (...) clause
Teradata (teradata)Populated row partitions (PPI / MLPPI)WHERE PARTITION = n
.\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, FastTransfer 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:

TupleOne stream per
(date_column,year)Year
(date_column,year,month)Month
(date_column,year,month,day)Day
(date_column,year,week)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:

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

This example:

  • Connects to a SQL Server source database on localhost
  • Transfers data to a SQL Server target database using BulkCopy
  • Uses trusted authentication for both source and target
  • Executes a custom query on the orders table
  • Uses the DataDriven distribution method
  • Distributes work based on the month of the order date (o_ordermonth column)
  • Uses a custom query to generate month values (instead of a SELECT DISTINCT o_ordermonth from the source query)
  • Uses 10 parallel threads (even though there are 12 months to export)
Copyright © 2026 Architecture & Performance.