OHDSI Home | Forums | Wiki | Github

Cohort generation 100x slower in Atlas than in SQL - advice on how to debug?

General
#1

I’ve been running into an issue where cohort generation in Atlas takes several hours (6+ hours is common). But if I run the query manually after exporting SQL, it takes a minute or two.

Steps to reproduce behavior

  1. Create a cohort for delivery hospitalizations using the JSON exported from Atlas (attached as cohort.json).
  2. Generate the cohort; it takes several hours:
    image
  3. Use the cohort.sql OHDSI SQL template - plug in the details in https://data.ohdsi.org/SqlDeveloper/ and paste it into Microsoft SQL Server Management Studio or another IDE. The SQL template is incomplete so needs these lines added to it:
CREATE TABLE target_database_schema.target_cohort_table (
	cohort_definition_id INT,
	cohort_start_date DATE,
	cohort_end_date DATE,
	subject_id BIGINT
);
  1. Run the SQL query. It takes 49 seconds as opposed to 1h 38 min, which is over 100x faster. (And there are the same number of rows/persons in the cohort.)

This causes several days’ worth of delays regularly: every slight change in a cohort definition (including or excluding a concept set, etc) means many hours of waiting to rerun characterizations and tweak the cohort definition.

Any idea how to debug the source of this slowdown?

It has blocking several analyses for months unfortunately.

The SQL and JSON cohort definitions exported from Atlas are here: https://github.com/OHDSI/Atlas/files/7735469/sql-and-json.zip

It looks like this issue is database-dependent. @karthik and @t_abdul_basser are helping me debug this, and ran the query against an older version of the database on Atlas, which ran quickly. But running it on a newer version of the same database takes very long, and understanding this discrepancy will likely help solve this bug.

Curious if anyone else has run into this! My hunch is that it might be related to the way inpatient visits are coded (that was a key step when the cohort generation started taking 5+ hours), but I am not sure how to investigate and don’t have access to the logs.

Open to any advice on how to debug this!

t