Multi-Database Benchmarking: Old vs New Cohort Generation

Overview

This vignette describes how to benchmark CDMConnector::generateCohortSet (old, CIRCE-based) against atlasCohortGenerator::generateCohortSet2 (new, DAG-optimized batch) across multiple database platforms. The benchmarking script:

1. Runs both methods on each database with the same cohort set.
2. Records overall time for each method and writes results to a CSV.
3. Confirms that the two cohort tables have identical rows (order ignored) and writes per-database and per-cohort equivalence results to a second CSV.

Supported platforms include PostgreSQL, Redshift, Snowflake, Spark, and SQL Server. You provide a named list of CDM reference objects; the script handles timing, comparison, and CSV output.

Performance improvements with the new approach

The new approach (generateCohortSet2) uses a DAG-based batch optimizer that:

• Shares vocabulary expansion: Concept set expansion and codeset building are done once and reused across all cohorts, instead of once per cohort.
• Shares domain scans: Filtered domain tables (e.g. drug exposure, condition occurrence) are built once and read by every cohort that needs them.
• Reduces I/O: A single batch script writes to shared staging tables and finalizes in one pass, instead of N separate DELETE/INSERT cycles.

As a result, wall-clock time typically decreases as the number of cohorts and the overlap in concept sets increase. The ratio (new time / old time) is often below 1.0, with larger batches showing greater speedups. The exact improvement depends on:

• Number of cohorts and size of each definition
• Overlap in concept sets and domains across cohorts
• Database engine and hardware

The benchmarking script records time_old_sec, time_new_sec, and ratio_new_over_old per database so you can measure the speedup on your own data and platforms.

How to run the benchmark

source("extras/benchmark_cohort_generation.R")

cohort_set <- CDMConnector::readCohortSet("path/to/cohorts")
result <- benchmark_cohort_generation(cdm, cohort_set, cohort_path = "path/to/cohorts")

# Compare old vs new cohort tables (identical rows, order ignored)
cmp <- compare_cohort_tables(result$cdm, name_old = "cohort_bench_old", name_new = "cohort_bench_new")
cmp$identical   # TRUE if same set of rows
cmp$per_cohort  # Per-cohort row counts and match status

source("extras/benchmark_cohort_generation.R")
source("extras/benchmark_multi_database.R")

cohort_set <- CDMConnector::readCohortSet("path/to/cohorts")
cdms <- list(
  postgres   = cdm_postgres,
  redshift   = cdm_redshift,
  snowflake  = cdm_snowflake,
  spark      = cdm_spark,
  sql_server = cdm_sqlserver
)

run_benchmark_multi_database(
  cdms = cdms,
  cohort_set = cohort_set,
  cohort_path = "path/to/cohorts",
  results_csv = "benchmark_results.csv",
  equivalence_csv = "benchmark_equivalence.csv"
)

Benchmark results CSV (timing)

The timing CSV has one row per database. Example structure:

• ratio_new_over_old < 1 means the new method was faster.
• files_included lists the cohort definition files (or names) in the cohort set.

Equivalence CSV (same results)

The equivalence CSV confirms that the old and new cohort tables contain the same rows (order ignored). Each database has:

1. An overall row (with cohort_definition_id NA): total row counts and whether the full tables match.
2. Per-cohort rows: row counts from the old table (n_old), from the new table (n_new), and whether the set of rows for that cohort is identical (rows_identical).

Example:

When rows_identical is TRUE for all cohorts (and the overall row), the new approach produces exactly the same cohort membership and dates as the old CIRCE-based method; only execution strategy and performance differ.

Summary

Use the generated CSVs to document speedups and to confirm identical results across databases and between the two cohort generation methods.