OHDSI Home | Forums | Wiki | Github

Achilles results help

Developers
#1

Hi,

I am currently running Achilles against a postgresql database and once executing the Achilles query it stops at
“Analysis 424 (Number of co-occurring condition_occurrence condition_concept_id pairs) – START”
and no Achilles results table is generated.
I have given it more than 4 hours to execute the quarries with the same output below.

this is my first attempt to use Achilles and not sure what i am doing is correct or not.

I have included a screen shot below

achillies
achillies1024×768 59.7 KB

thank you !

#2

This is the query that produces the 424 analysis results:

You can execute it into a PG session (after replacing the tokens with the correct values, like CDM database schema…In the cases where you see a tokenized table name for a SELECT … INTO statement, just make up a table name, and delete it after you are done.

What you can do in your PG session is check to see if any indexes are missing, perhaps there’s something related to your tables which makes this query underperform (like many NULL values).

Tagging @AnthonyMolinaro for his guidance.

#3

Thank you Chris for your valuable response :slight_smile:
I really appreciate it.

I am attempting to run the scrip however, what should be replaced instead of the following values :

1- @scratchDatabaseSchema@schemaDelim@tempAchillesPrefix_424
2- #unique_pairs_424;

#4

It is the postgresql and NOT IN operator performance issue.
For example the condition_occurrence table has about 2M records and has indexes for condition_concept_id and person_id fields.
Execution time of following query will be about 20 minutes.

select distinct person_id,condition_concept_id
  from condition_occurrence
 where condition_concept_id != 0
   and person_id not in ( select person_id 
                            from condition_occurrence
						   where condition_concept_id != 0
                           group by person_id
                          having count(distinct condition_concept_id) = 1 )

If we will use left join and CTE instead of NOT IN, like this:

with a as
(
select person_id 
from condition_occurrence
where condition_concept_id != 0
group by person_id
having count(distinct condition_concept_id) = 1
)
select distinct co.person_id,condition_concept_id
from condition_occurrence co
left join a on a.person_id = co.person_id
where condition_concept_id != 0 and a.person_id is null

execution time of following will be about 1 sec.
so NOT IN - 20 minute, CTE and left join - 1 sec.

Tagging @AnthonyMolinaro @fatemah

1 Like
t