Determine performance bottle necks

[philipp94831]

• [x] measure data query time
• [x] measure network transfer time
• [ ] find solution

[philipp94831]

Network transfer time is not relevant. Bottle neck seems to be the following query:

SELECT patient_num, concept_cd, count(*) AS count
FROM ( 
  SELECT patient_num, substring(concept_cd from 1 for 7) AS concept_cd
  FROM i2b2demodata.observation_fact
  WHERE concept_cd IN (
    SELECT concept_cd
    FROM i2b2demodata.concept_dimension
    WHERE (concept_cd LIKE 'ATC:%' OR concept_cd LIKE 'ICD:%'))
    AND (start_date >= '2010-01-01T00:00:00' AND start_date <= '2011-01-01T00:00:00')
    AND (FALSE OR patient_num IN (
      SELECT patient_num
      FROM i2b2demodata.qt_patient_set_collection WHERE result_instance_id = 1117))
) observations
GROUP BY patient_num, concept_cd

Runs locally in <2s, on server ~85s For 2007, times are almost equal