odbc-scanner-benchmark.ps1

# -- https://github.com/duckdb/odbc-scanner/discussions/115

# 
# Configuration
#
$odbcConnectionString = 'Driver={Firebird ODBC Driver};Database=/temp/test.fdb;UID=SYSDBA;PWD=masterkey;CHARSET=UTF8'

$sourceQueryFile = '/temp/odbc-scanner-benchmark/source.sql'
$destQueryFile = '/temp/odbc-scanner-benchmark/dest.sql'

# If installed via Chocolatey, don’t use "duckdb.exe" from the PATH: it's a shim that forwards to the real executable and always reports constant memory usage.
$realDuckDb = 'C:\ProgramData\chocolatey\lib\duckdb\files\duckdb.exe'

$rowsPerTransaction = 10000


#
# Main
#
$sqlFile = New-TemporaryFile
@"
INSTALL 'http://nightly-extensions.duckdb.org/v1.2.0/windows_amd64/odbc_scanner.duckdb_extension.gz';
LOAD odbc_scanner;

SET VARIABLE conn = odbc_connect('$odbcConnectionString');

SET VARIABLE source_query = (SELECT content FROM read_text('$sourceQueryFile'));
SET VARIABLE dest_query = (SELECT content FROM read_text('$destQueryFile'));

FROM odbc_copy(getvariable('conn'),
  batch_size=1,
  max_records_in_transaction = $rowsPerTransaction,
  source_queries=[getvariable('source_query')],
  dest_query=getvariable('dest_query')
);
"@ | Out-File $sqlFile

Write-Host "Running DuckDB..."
$p = Start-Process -FilePath $realDuckDb -ArgumentList '-f', $sqlFile -PassThru

$startDate = Get-Date
while (!$p.HasExited) {
    [PSCustomObject]@{
        elapsed = (Get-Date) - $startDate
        workingSetKb = (Get-Process -Id $p.Id).WorkingSet / 1KB
    } | Write-Output

    Start-Sleep -Seconds 5
}
Remove-Item $sqlFile -ErrorAction SilentlyContinue