Choosing the Right FastBCP Parallel Method

FastBCP can split one export into several queries that run at the same time. How it splits the table is set by --parallelmethod, and FastBCP 1.4 accepts eight values: None plus seven ways of cutting the data. More will come until we find goodmode.

Picking one is not obvious. The fastest method depends on how big the table is, how it is indexed, whether it is partitioned, and what you want the output folder to look like. This article lists the methods, then gives a size-based rule of thumb, using SQL Server to Parquet as the running example.

The parallel methods in FastBCP 1.4

Each method below is a value of --parallelmethod. The number of concurrent queries comes from --paralleldegree (default -2, which means half the cores). Some methods need a split column: they take it from --distributekeycolumn.

NoneNothing: one query, one streamno key column

Sequential export. No setup, no split overhead.

• Zero configuration
• One output file
• No extra queries to compute boundaries
• Uses a single core on the source and on the writer side

Best for Small and very small tables, up to about 20M cells.

PhysicalPhysical row location (Physloc on SQL Server)no key column

Splits on where rows live on disk. FastBCP picks a strategy depending on the connection type.

• No need to know the table structure
• No distribution key to choose
• Works on heaps and on clustered columnstore tables
• Rarely the fastest option
• On very large SQL Server tables, RangeId usually wins
• Needs --sourceschema and --sourcetable
• Not yet available on Teradata, MySQL, HANA, ClickHouse, ODBC, OLE DB

Best for Mid-size tables when you do not want to study the schema.

RangeIdEqual value ranges between MIN and MAX of a columnneeds --distributekeycolumnneeds int/bigint as distribute key column

Reads MIN and MAX of the key column, then gives each thread one range.

• Very fast when the column is the leading key of the clustered index
• Cheap to plan: two aggregates
• Unbalanced chunks when values have big gaps
• Not optimal if the column is not the leading column of a clustered index

Best for Tables with a clustered index whose first column has many distinct values. Excellent choice for very large SQL Server tables with an identity column.

NtileEven chunks computed with NTILE over a columnneeds --distributekeycolumn

Builds chunks with the same number of rows each, whatever the value distribution.

• Balanced chunks even with skewed data
• Key column can be an integer, a date, a string or a UID, and does not need to be unique
• Computing the tiles costs a pass over the column
• Needs an index on the column, or a columnstore, to stay cheap

Best for Tables with a well-indexed integer, date or string column and an uneven distribution.

DBPartitionNative table partitionsno key column

One extraction per partition, largest first. CHUNK or HIVE output layout.

• No distribution column
• Partition elimination: each query reads only its partition
• Can mirror partitions as Hive folders (FASTBCP_DBPARTITION_TARGET_MODE=HIVE)
• Only as balanced as the partitions themselves
• Falls back to None on a non-partitioned table

Best for Partitioned tables with partitions of similar size.

TimepartitionYear, month, week or day of a date columnneeds --distributekeycolumn

Exports one period per query and writes a Hive tree such as year=1998/month=06.

• Hive layout ready for a data lake
• Works whether the table is partitioned or not
• Natural fit for later incremental (delta) exports
• Periods can be very uneven
• Needs an index or partitioning on the date column to avoid repeated scans

Best for Fact tables with a date column, when the target layout should follow time.

DataDrivenDistinct values of a column or expressionneeds --distributekeycolumn

One query per value, taken from the column or from --datadrivenquery.

• Full control over the split, expressions allowed (YEAR(o_orderdate))
• --datadrivenquery lets you export only some values
• Fits per-entity extraction, such as one tag at a time on industrial historians
• Many values means many queries, throttled by the degree
• Unbalanced if a few values hold most of the rows

Best for Business-driven splits, partial or incremental extracts, per-tag exports.

RandomModulo-style split on an integer columnneeds --distributekeycolumnneeds int/bigint as distribute key column

Kept for backward compatibility and for comparisons.

• Balanced chunks on an integer column
• Often the slowest parallel option
• Needs at least as many distinct values as the parallel degree

Best for Benchmarks against the other methods. Avoid in production.

FastBCP 1.4 still accepts the engine-specific names Physloc, Ctid, Rowid and NZDataSlice, but logs a warning: they will be removed in FastBCP 2.0. Use Physical instead.

Measure the table in cells, not rows

Row count alone says little about how long an export takes. A 10-million-row table with 4 columns is a small job. The same row count with 120 columns is not.

FastBCP reasons in cells: rows × columns. The thresholds in the rest of this article use that unit. The queries below list the cell count of every table, using the row counts kept in the catalog statistics, so no table is scanned. Those counts are estimates: refresh the statistics first if they are old.

SQL Server

SELECT s.name                    AS schema_name,
       t.name                    AS table_name,
       SUM(p.rows)               AS row_count,
       c.col_count,
       SUM(p.rows) * c.col_count AS cells
FROM sys.tables t
JOIN sys.schemas s    ON s.schema_id = t.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
CROSS APPLY (SELECT COUNT(*) AS col_count
             FROM sys.columns col
             WHERE col.object_id = t.object_id) c
GROUP BY s.name, t.name, c.col_count
ORDER BY cells DESC;

Oracle

-- num_rows comes from optimizer statistics (DBMS_STATS)
SELECT t.owner,
       t.table_name,
       t.num_rows                AS row_count,
       c.col_count,
       t.num_rows * c.col_count  AS cells
FROM all_tables t
JOIN (SELECT owner, table_name, COUNT(*) AS col_count
      FROM all_tab_columns
      GROUP BY owner, table_name) c
  ON c.owner = t.owner AND c.table_name = t.table_name
WHERE t.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY cells DESC NULLS LAST;

PostgreSQL

-- reltuples is updated by VACUUM / ANALYZE; partitions are listed as leaf tables
SELECT n.nspname                                   AS schema_name,
       c.relname                                   AS table_name,
       GREATEST(c.reltuples, 0)::bigint            AS row_count,
       a.col_count,
       GREATEST(c.reltuples, 0)::bigint * a.col_count AS cells
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (SELECT COUNT(*) AS col_count
                    FROM pg_attribute
                    WHERE attrelid = c.oid
                      AND attnum > 0
                      AND NOT attisdropped) a
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY cells DESC;

MySQL

-- table_rows is an estimate for InnoDB tables
SELECT t.table_schema,
       t.table_name,
       t.table_rows                          AS row_count,
       COUNT(c.column_name)                  AS col_count,
       t.table_rows * COUNT(c.column_name)   AS cells
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
GROUP BY t.table_schema, t.table_name, t.table_rows
ORDER BY cells DESC;

Netezza

-- RELTUPLES is refreshed by GENERATE STATISTICS
SELECT t.SCHEMA,
       t.TABLENAME,
       t.RELTUPLES                     AS row_count,
       COUNT(c.ATTNAME)                AS col_count,
       t.RELTUPLES * COUNT(c.ATTNAME)  AS cells
FROM _V_TABLE t
JOIN _V_RELATION_COLUMN c ON c.OBJID = t.OBJID
WHERE t.OBJTYPE = 'TABLE'
GROUP BY t.SCHEMA, t.TABLENAME, t.RELTUPLES
ORDER BY cells DESC;

Teradata

-- RowCount is only known for tables with collected statistics
SELECT t.DatabaseName,
       t.TableName,
       s.row_count,
       c.col_count,
       s.row_count * c.col_count AS cells
FROM DBC.TablesV t
JOIN (SELECT DatabaseName, TableName, COUNT(*) AS col_count
      FROM DBC.ColumnsV
      GROUP BY 1, 2) c
  ON c.DatabaseName = t.DatabaseName AND c.TableName = t.TableName
LEFT JOIN (SELECT DatabaseName, TableName, MAX(RowCount) AS row_count
           FROM DBC.StatsV
           GROUP BY 1, 2) s
  ON s.DatabaseName = t.DatabaseName AND s.TableName = t.TableName
WHERE t.TableKind IN ('T', 'O')
ORDER BY cells DESC;

SAP HANA

-- RECORD_COUNT covers tables currently loaded in memory
SELECT t.SCHEMA_NAME,
       t.TABLE_NAME,
       t.RECORD_COUNT                         AS row_count,
       COUNT(c.COLUMN_NAME)                   AS col_count,
       t.RECORD_COUNT * COUNT(c.COLUMN_NAME)  AS cells
FROM SYS.M_TABLES t
JOIN SYS.TABLE_COLUMNS c
  ON c.SCHEMA_NAME = t.SCHEMA_NAME AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.SCHEMA_NAME <> 'SYS'
  AND t.SCHEMA_NAME NOT LIKE '_SYS%'
GROUP BY t.SCHEMA_NAME, t.TABLE_NAME, t.RECORD_COUNT
ORDER BY cells DESC;

ODBC

-- Generic ODBC source: no common catalog, so measure one table at a time.
-- Exact row count (scans the table)
SELECT COUNT(*) AS row_count FROM my_schema.my_table;

-- Column count, on engines that expose INFORMATION_SCHEMA
SELECT COUNT(*) AS col_count
FROM information_schema.columns
WHERE table_schema = 'my_schema'
  AND table_name   = 'my_table';

Cell size matters too. The thresholds assume ordinary cells: numbers, dates, short strings. A table full of NVARCHAR(MAX), XML or VARBINARY columns moves far more bytes per cell. Treat such a table as one size class larger than its cell count suggests.

For scale, TPC-H orders at scale factor 10 has 15 million rows and 9 columns, so 135 million cells. lineitem at the same scale has 60 million rows and 16 columns, close to a billion cells.

Example: SQL Server to Parquet

First, use the ADBC connection type (with Parquet output)

ARPE.IO ADBC drivers

Before tuning parallelism, check the connection type. For databases that have an ARPE.IO ADBC driver, use the adbc_* connection type:

• adbc_mssql
• adbc_oracle
• adbc_pgsql
• adbc_netezza
• adbc_teradata

DB2 and MySQL drivers are on the way. For other sources, use the native client types or ODBC / OLE DB.

On SQL Server, the ADBC benchmark exported orders (135M cells) in 14.3 s on one thread with adbc_mssql. With 8 threads (Ntile on o_orderkey) it took 6.4 s.

Then walk the decision tree

Turn on Hive partitioning? if the output must be a Hive folder tree (key=value directories) for a data lake. The layout then decides, not the speed: DBPartition with FASTBCP_DBPARTITION_TARGET_MODE=HIVE mirrors the native partitions, and Timepartition builds year=…/month=… folders from a date column.

Without that constraint, the order of the questions matters. Size comes first: under 20M cells, splitting costs more than it saves. Then the tree looks at the clustered index, which gives the fastest splits. A selective integer key means RangeId, unless the numbering has big gaps, where Ntile keeps the chunks even. A date or string key also goes to Ntile. Partitions of similar size mean DBPartition, with no key column to choose. On tables of 500M cells and more, a date column gives Timepartition, otherwise Physical. Below that size, Physical is the simple default where the engine supports it.

Random never comes out of the tree: it stays in FastBCP for backward compatibility and comparisons.

Physical is not available on every source

Physical only exists for SQL Server, Oracle, PostgreSQL and Netezza. On Teradata (teradata, adbc_teradata), MySQL, SAP HANA, ClickHouse, and generic ODBC or OLE DB connections, FastBCP stops with an error if you ask for it. On those sources, split on a column instead: RangeId or Ntile on an indexed key, Timepartition on a date column, or DataDriven on a business value. DBPartition also works on partitioned MySQL and Teradata tables.

Very small tables: under 100,000 cells

Use None. Splitting costs more than it saves: each parallel query has to open a connection, plan, and write its own file.

Small tables: under 20 million cells

None is very often the fastest and is enough. Try parallelism only if the cells are large (see above) or if the network is the bottleneck.

Intermediate tables: 20 to 500 million cells

Here parallelism pays off, and several methods are reasonable.

Physical is not the fastest, but it is the simplest. You do not need to know anything about the table: no key column, no index check. FastBCP writes one file per thread, so with --paralleldegree 8 you get 8 Parquet files unless you add --merge true.

.\FastBCP.exe `
  --connectiontype adbc_mssql `
  --server "localhost,11433" `
  --database tpch10 `
  --trusted `
  --sourceschema dbo `
  --sourcetable orders `
  --directory "D:\export\orders" `
  --fileoutput "orders.parquet" `
  --parallelmethod Physical `
  --paralleldegree 8

RangeId is almost always a good fit if the table has a clustered index and the first column of that index has many distinct values. Each thread then reads a contiguous slice of the clustered index.

The counter-example is a multi-column clustered index whose first column has only 5 or 6 values while the second has thousands, for example (region_id, customer_id). RangeId on region_id cannot produce more than a handful of useful chunks, and RangeId on customer_id scans the whole index for each chunk. If you control the schema, consider putting the most selective column first in the clustered index. That is a heavy change on a production table, so weigh it against the other methods.

To see the leading column of the clustered index and how selective it is:

SELECT col.name AS leading_column
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id
                         AND ic.index_id  = i.index_id
                         AND ic.key_ordinal = 1
JOIN sys.columns col      ON col.object_id = ic.object_id
                         AND col.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID('dbo.orders')
  AND i.type = 1;  -- clustered

SELECT COUNT(DISTINCT o_orderkey) FROM dbo.orders;

.\FastBCP.exe `
  --connectiontype adbc_mssql `
  --server "localhost,11433" `
  --database tpch10 `
  --trusted `
  --sourceschema dbo `
  --sourcetable orders `
  --directory "D:\export\orders" `
  --fileoutput "orders.parquet" `
  --parallelmethod RangeId `
  --distributekeycolumn o_orderkey `
  --paralleldegree 8

Ntile works when you have an integer, date or string column that cuts the table well. Ideally that column is indexed, or the table has a columnstore index, so computing the tiles stays cheap.

DBPartition and Timepartition are the right tools when the table is partitioned or indexed on a date column and you want the partitions, existing or not, to show up as a Hive tree on the output side. See the Timepartition tutorial for the layout it produces.

DataDriven exports one chunk per value of a column. It also lets some industrial databases be extracted tag by tag. A dedicated article on that is coming.

Random is usually a poor choice. It stays in FastBCP for backward compatibility and as a point of comparison.

Large tables: 500 million cells and more

At this size the clustered index decides first. A clustered index led by a selective integer column, such as an identity, makes RangeId the fastest option, including on SQL Server. If the numbering has big gaps, use Ntile instead so the chunks stay even. A clustered index led by a date or string column also points to Ntile.

If the table is partitioned with partitions of similar size, DBPartition is the easy option: no distribution column to choose.

Otherwise, look for a date column. Timepartition splits on it and prepares the ground for incremental exports. Without a date column, Physical is the fallback on the engines that support it. On SQL Server it is rarely the fastest method at this size, but it needs no key column and still spreads the work. On engines without Physical, use DataDriven on a business column.

Summary

Size (cells)	First choice on SQL Server	Alternatives
< 100K	None	none
100K – 20M	None	parallel only for wide cells
20M – 500M	RangeId with a good clustered index	Ntile (gaps, date or string key), DBPartition, Physical (simplest)
≥ 500M	RangeId with a good clustered index	Ntile (gaps, date or string key), DBPartition on even partitions, Timepartition, then Physical

Start with the connection type, measure the table in cells, then check the clustered index. Those three steps settle most cases. The full parameter reference is in the FastBCP 1.4 documentation.

Try FastBCP Now

Free 30-day trial