init_sqls take too long to execute

[SunahY1011]

When updating our CDM data, I run Achilles and connect its results to ATLAS. However, the Achilles results alone are not sufficient to register a new data source in ATLAS, since ATLAS requires additional tables that Achilles does not create.

So I attempted to create the missing tables using the SQL scripts provided in WebAPI (src/main/resources/ddl/results). After creating the tables, I inserted some initial data using the "init_%" sql files in the same directory.

However, when executing init_concept_hierarchy.sql, the script takes an excessively long time to complete.

From the logs, I found that queries for certain domains are especially slow:
• condition: 7m 30s
• measurement: 8m 59s
• observation: 8m 17s
• procedure: 9m 15s
• drug_exposure: 20h 31m 54s (still running)
• drug_era: 20h 31m 54s (still running)

In particular, the JOIN operations (e.g., atc5_to_atc3, atc3_to_atc1) appear to be the main bottleneck.

Could you please take a look into this issue?

[konstjar]

I assume the problem is in SQL queries. Yes, execution will take some time, minutes, not hours. Also it depends on amount of data in your OMOP CDM.

Here are things to check:

1. What database do you use?
2. Do you have all the indexes applied to CDM tables?
3. I would consider building additional indexes on drug_* tables to improve the performance.

[SunahY1011]

Thank you for your very quick response.

1. What database do you use?
   → I’m using PostgreSQL version 15.

2. Do you have all the indexes applied to the CDM tables?
   → Perhaps not… (I hadn’t realized it and just checked now.) But I’m still curious why the MEASUREMENT table, which has significantly more rows (2.6 billion) compared to DRUG_EXPOSURE (240 million), takes less time to process.

3. I would consider building additional indexes on drug_ tables to improve the performance.
   → Thank you — I’ll look into adding more indexes to improve performance.

[chrisknoll]

However, when executing init_concept_hierarchy.sql, the script takes an excessively long time to complete.

Just to clarify some context: if we are talking about init_concept_hierarchy.sql:

This table is exclusively a query building off the vocabulary tables, so the size of the cdm isn't a factor here. The main work of the query is finding relationships between concept_ancestor and children, looking for the level 1, 2, 3, 4 that can be constructed out of the data in concept_ancestor. So, to optimize that query, you want to make sure you have indexes set up on concept, and concept_ancestor. If you have the means, running those long-running sub-queries through a query analyzer may reveal some configuration (ie: indexes or table partitioning) that might be causing a bottleneck.