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 \
...
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.
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.
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.
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.
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.
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.
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
| Method | Parallel | Needs Distributed Column | Database Source Type |
|---|---|---|---|
None | Any | ||
DataDriven | Any | ||
Ctid | PostgreSQL (pgsql/pgcopy) | ||
Physloc | SQL Server (mssql) | ||
Random | Any | ||
Rowid | Oracle (oraodp) | ||
RangeId | Any | ||
Ntile | Any | ||
NZDataSlice | Netezza | ||
DBPartition | SQL Server, PostgreSQL, Oracle, MySQL, Teradata | ||
Timepartition | (date tuple) | Any |
Default Value: None
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)" \
...
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.
- 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 \
...
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.
- Windows
- Linux
.\FastTransfer.exe `
...
--datadrivenquery "SELECT tagname FROM tags" `
...
./FastTransfer \
...
--datadrivenquery "SELECT tagname FROM tags" \
...
- Windows
- Linux
.\FastTransfer.exe `
...
--datadrivenquery "SELECT o_orderdate FROM dim_date where ref_date > getdate() - 30" `
...
./FastTransfer \
...
--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:
| Source | Partitions used | How a partition is read |
|---|---|---|
SQL Server (mssql, msoledbsql) | Partitions of the partition scheme | WHERE $PARTITION.<function>(<column>) = n |
PostgreSQL (pgsql, pgcopy) | Leaf partitions (sub-partitions included) | The leaf table is read directly |
Oracle (oraodp) | Partitions and sub-partitions | PARTITION (...) / SUBPARTITION (...) clause |
MySQL (mysql) | Partitions and sub-partitions | PARTITION (...) clause |
Teradata (teradata) | Populated row partitions (PPI / MLPPI) | WHERE PARTITION = n |
- 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, 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:
| Tuple | One 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-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:
- 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
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_ordermonthfrom the source query) - Uses 10 parallel threads (even though there are 12 months to export)