Advanced Scenarios

Advanced use cases, partition functions, and troubleshooting examples.

SQL Server Partition Functions

Use SQL Server partition functions for efficient parallel exports.

Using Partition Function with DataDriven

Leverage existing partition functions to distribute work:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "tpch10_collation_bin2" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "orders_part" `
  --directory "D:\temp" `
  --fileoutput "orders_partitioned.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "DataDriven" `
  --distributekeycolumn "`$partition.PF_DATE(o_orderdate)" `
  --paralleldegree 10 `
  --merge "False"

Partition Function Syntax

Use `$partition.FunctionName(ColumnName) to reference a partition function. The backtick escapes the dollar sign in PowerShell.

Date-based Partitioning

Partition by date ranges for time-series data:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "TimeSeriesDB" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "SensorReadings_Part" `
  --directory "C:\temp" `
  --fileoutput "sensor_readings.parquet" `
  --parallelmethod "DataDriven" `
  --distributekeycolumn "`$partition.PF_Monthly(reading_date)" `
  --paralleldegree 12 `
  --merge "False"

Complex Queries

Multi-table Joins

Export data from complex joins:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "SalesDB" `
  --trusted `
  --query "SELECT o.order_id, o.order_date, c.customer_name, od.product_id, od.quantity, od.unit_price FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id INNER JOIN order_details od ON o.order_id = od.order_id WHERE o.order_date >= '2024-01-01'" `
  --directory "C:\temp" `
  --fileoutput "sales_detail.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Ntile" `
  --distributekeycolumn "o.order_id" `
  --paralleldegree 8 `
  --merge "False"

Aggregated Data

Export pre-aggregated data:

.\FastBCP.exe `
  --connectiontype "pgsql" `
  --server "localhost" `
  --port "5432" `
  --database "analytics" `
  --user "postgres" `
  --password "postgres" `
  --query "SELECT date_trunc('day', order_date) as order_day, customer_region, COUNT(*) as order_count, SUM(total_amount) as total_sales FROM orders GROUP BY date_trunc('day', order_date), customer_region" `
  --directory "C:\temp" `
  --fileoutput "daily_sales_by_region.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd" `
  --encoding "UTF-8" `
  --parallelmethod "None"

Performance Tuning

Optimal Parallel Degree

Auto-detect

Use negative values to auto-calculate based on CPU cores:

# Use all cores minus 2
.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "LargeDB" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "BigTable" `
  --directory "C:\temp" `
  --fileoutput "bigtable.parquet" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "id" `
  --paralleldegree -2 `
  --merge "False"

Manual

Specify exact thread count:

# Use exactly 16 threads
.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "LargeDB" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "BigTable" `
  --directory "C:\temp" `
  --fileoutput "bigtable.parquet" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "id" `
  --paralleldegree 16 `
  --merge "False"

Merge vs No-Merge

With Merge

Combine parallel outputs into single file:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "DataDB" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "Data" `
  --directory "C:\temp" `
  --fileoutput "data_merged.parquet" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "id" `
  --paralleldegree 8 `
  --merge "True"

Result: Single file data_merged.parquet

Without Merge

Keep separate files for parallel processing:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "DataDB" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "Data" `
  --directory "C:\temp" `
  --fileoutput "data_part.parquet" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "id" `
  --paralleldegree 8 `
  --merge "False"

Result: Multiple files data_part_Chunk000.parquet, data_part_Chunk001.parquet, etc.

Enable Debug Logging

Get detailed execution information:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost" `
  --database "TestDB" `
  --trusted `
  --sourceschema "dbo" `
  --sourcetable "TestTable" `
  --directory "C:\temp" `
  --fileoutput "debug_export.csv" `
  --parallelmethod "Random" `
  --distributekeycolumn "id" `
  --paralleldegree 4 `
  --merge "False" `
  --loglevel "Debug" `
  --runid "debug_test_run"

Debug Mode

Use --loglevel "Debug" to see detailed execution steps, connection strings (without passwords), and query generation details.

tip

Build your command with the Wizard