Times Reported Time consuming queries #1

x Hide

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence, g.nm AS InferenceGeneSymbol, -- get rid of this cdr.network_score AS InferenceScore, -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm, g.nm, cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:26:51
Duration: 4m21s

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: load - Total duration: 3m2s - Times executed: 6 ]

x Hide

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, db.CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from load.TERM t, load.TERM_LABEL l, edit.DB db where t.object_type_id = 2 and t.id = l.term_id and t.acc_db_id = db.id;

Date: 2025-07-16 08:52:54
Duration: 35s355ms
Database: ctddev51
User: load
Bind query: yes

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, db.CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from load.TERM t, load.TERM_LABEL l, edit.DB db where t.object_type_id = 2 and t.id = l.term_id and t.acc_db_id = db.id;

Date: 2025-07-16 11:18:25
Duration: 31s594ms
Database: ctddev51
User: load
Bind query: yes

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, db.CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from load.TERM t, load.TERM_LABEL l, edit.DB db where t.object_type_id = 2 and t.id = l.term_id and t.acc_db_id = db.id;

Date: 2025-07-16 11:08:51
Duration: 31s42ms
Database: ctddev51
User: load
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

SELECT i.nm as ChemicalName, i.acc as ChemicalID, i.cas_rn as CasRN, ( SELECT t.description FROM term t WHERE t.id = i.term_id) as Definition, STRING_AGG(DISTINCT p.acc_db_cd || ':' || p.object_acc_txt, '|' ORDER BY p.acc_db_cd || ':' || p.object_acc_txt) as ParentIDs, STRING_AGG(DISTINCT i.tree_num, '|' ORDER BY i.tree_num) as TreeNumbers, STRING_AGG(DISTINCT p.acc_txt, '|' ORDER BY p.acc_txt) as ParentTreeNumbers, ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = i.term_id AND tl.nm != ( SELECT tn.nm FROM term tn where tn.id = tl.term_id) AND tlt.nm IN ('CHEMICAL SYNONYM', 'EQUIVALENT TERM', 'CAS TYPE 1 NAME')) as Synonyms FROM ( SELECT DISTINCT n.nm, n.parent_id, n.acc_txt tree_num, n.acc_db_cd || ':' || n.object_acc_txt acc, n.secondary_nm cas_rn, n.object_acc_txt, n.object_id term_id FROM dag_node n WHERE n.dag_id = ( SELECT id FROM dag WHERE cd = 'chem') ORDER BY n.object_acc_txt) i LEFT OUTER JOIN dag_node p ON i.parent_id = p.id GROUP BY i.nm, i.acc, i.cas_rn, i.term_id ORDER BY i.nm;

Date: 2025-07-16 14:38:01
Duration: 22s581ms

x Hide

Times Reported Time consuming queries #4

x Hide

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence -- ,g.nm AS InferenceGeneSymbol -- get rid of this , -- ,cdr.network_score AS InferenceScore -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE cdr.source_cd = 'I' -- add this in to only get curated AND cdr.source_acc_txt is NOT null GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm -- ,g.nm -- ,cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:48:59
Duration: 13s62ms

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 9s534ms - Times executed: 3 ]

x Hide

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2025-07-16 15:23:30
Duration: 3s295ms
Database: ctddev51
User: pubeu
Bind query: yes

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2025-07-16 12:46:53
Duration: 3s152ms
Database: ctddev51
User: pubeu
Bind query: yes

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2025-07-16 13:54:23
Duration: 3s86ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

SELECT -- All Chem query - need to add in to filter for only curated i.nm as ChemicalName, i.acc as ChemicalID, i.cas_rn as CasRN, ( SELECT t.description FROM term t WHERE t.id = i.term_id) as Definition, STRING_AGG(DISTINCT p.acc_db_cd || ':' || p.object_acc_txt, '|' ORDER BY p.acc_db_cd || ':' || p.object_acc_txt) as ParentIDs, STRING_AGG(DISTINCT i.tree_num, '|' ORDER BY i.tree_num) as TreeNumbers, STRING_AGG(DISTINCT p.acc_txt, '|' ORDER BY p.acc_txt) as ParentTreeNumbers, ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = i.term_id AND tl.nm != ( SELECT tn.nm FROM term tn where tn.id = tl.term_id) AND tlt.nm IN ('CHEMICAL SYNONYM', 'EQUIVALENT TERM', 'CAS TYPE 1 NAME')) as Synonyms FROM ( SELECT DISTINCT n.nm, n.parent_id, n.acc_txt tree_num, n.acc_db_cd || ':' || n.object_acc_txt acc, n.secondary_nm cas_rn, n.object_acc_txt, n.object_id term_id FROM dag_node n WHERE n.dag_id = ( SELECT id FROM dag WHERE cd = 'chem') AND n.object_id IN ( SELECT gcr.chem_id FROM gene_chem_reference gcr UNION SELECT cdr.chem_id FROM chem_disease_reference cdr WHERE source_cd = 'C') ORDER BY n.object_acc_txt) i LEFT OUTER JOIN dag_node p ON i.parent_id = p.id GROUP BY i.nm, i.acc, i.cas_rn, i.term_id ORDER BY i.nm;

Date: 2025-07-16 14:56:30
Duration: 5s419ms

SELECT -- All Chem query - need to add in to filter for only curated i.nm as ChemicalName, i.acc as ChemicalID, i.cas_rn as CasRN, ( SELECT t.description FROM term t WHERE t.id = i.term_id) as Definition, STRING_AGG(DISTINCT p.acc_db_cd || ':' || p.object_acc_txt, '|' ORDER BY p.acc_db_cd || ':' || p.object_acc_txt) as ParentIDs, STRING_AGG(DISTINCT i.tree_num, '|' ORDER BY i.tree_num) as TreeNumbers, STRING_AGG(DISTINCT p.acc_txt, '|' ORDER BY p.acc_txt) as ParentTreeNumbers, ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = i.term_id AND tl.nm != ( SELECT tn.nm FROM term tn where tn.id = tl.term_id) AND tlt.nm IN ('CHEMICAL SYNONYM', 'EQUIVALENT TERM', 'CAS TYPE 1 NAME')) as Synonyms FROM ( SELECT DISTINCT n.nm, n.parent_id, n.acc_txt tree_num, n.acc_db_cd || ':' || n.object_acc_txt acc, n.secondary_nm cas_rn, n.object_acc_txt, n.object_id term_id FROM dag_node n WHERE n.dag_id = ( SELECT id FROM dag WHERE cd = 'chem') AND n.object_id IN ( SELECT gcr.chem_id FROM gene_chem_reference gcr UNION SELECT cdr.chem_id FROM chem_disease_reference cdr WHERE source_cd = 'C') ORDER BY n.object_acc_txt) i LEFT OUTER JOIN dag_node p ON i.parent_id = p.id GROUP BY i.nm, i.acc, i.cas_rn, i.term_id ORDER BY i.nm;

Date: 2025-07-16 14:54:36
Duration: 4s639ms

x Hide

Times Reported Time consuming queries #7

x Hide

SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'UBERON_0000916') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'UBERON_0000916') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'UBERON_0000916')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'UBERON_0000916')) ii GROUP BY ii.cd;

Date: 2025-07-16 13:57:49
Duration: 9s597ms
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence -- ,g.nm AS InferenceGeneSymbol -- get rid of this , -- ,cdr.network_score AS InferenceScore -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE cdr.source_cd = 'C' -- add this in to only get curated -- AND cdr.source_acc_txt is NOT null -- this is null for all curated, only if omim inferred GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm -- ,g.nm -- ,cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:50:24
Duration: 5s555ms

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence -- ,g.nm AS InferenceGeneSymbol -- get rid of this , -- ,cdr.network_score AS InferenceScore -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE cdr.source_cd = 'C' -- add this in to only get curated GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm -- ,g.nm -- ,cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:38:25
Duration: 2s381ms

x Hide

Times Reported Time consuming queries #9

x Hide

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence -- ,g.nm AS InferenceGeneSymbol -- get rid of this , -- ,cdr.network_score AS InferenceScore -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE cdr.source_cd = 'C' -- add this in to only get curated AND cdr.source_acc_txt is null GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm -- ,g.nm -- ,cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:47:42
Duration: 5s142ms

x Hide

Times Reported Time consuming queries #10

x Hide

select distinct (source_cd) from chem_disease_reference;

Date: 2025-07-16 14:05:46
Duration: 2s450ms

x Hide

Times Reported Time consuming queries #11

x Hide

select distinct (source_cd) from chem_disease_reference --select * from object_type;

Date: 2025-07-16 16:57:30
Duration: 1s745ms

x Hide

Times Reported Time consuming queries #1

x Hide

[ User: load - Total duration: 3m2s - Times executed: 6 ]

x Hide

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, db.CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from load.TERM t, load.TERM_LABEL l, edit.DB db where t.object_type_id = 2 and t.id = l.term_id and t.acc_db_id = db.id;

Date: 2025-07-16 08:52:54
Duration: 35s355ms
Database: ctddev51
User: load
Bind query: yes

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, db.CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from load.TERM t, load.TERM_LABEL l, edit.DB db where t.object_type_id = 2 and t.id = l.term_id and t.acc_db_id = db.id;

Date: 2025-07-16 11:18:25
Duration: 31s594ms
Database: ctddev51
User: load
Bind query: yes

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, db.CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from load.TERM t, load.TERM_LABEL l, edit.DB db where t.object_type_id = 2 and t.id = l.term_id and t.acc_db_id = db.id;

Date: 2025-07-16 11:08:51
Duration: 31s42ms
Database: ctddev51
User: load
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 9s534ms - Times executed: 3 ]

x Hide

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2025-07-16 15:23:30
Duration: 3s295ms
Database: ctddev51
User: pubeu
Bind query: yes

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2025-07-16 12:46:53
Duration: 3s152ms
Database: ctddev51
User: pubeu
Bind query: yes

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2025-07-16 13:54:23
Duration: 3s86ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

SELECT -- All Chem query - need to add in to filter for only curated i.nm as ChemicalName, i.acc as ChemicalID, i.cas_rn as CasRN, ( SELECT t.description FROM term t WHERE t.id = i.term_id) as Definition, STRING_AGG(DISTINCT p.acc_db_cd || ':' || p.object_acc_txt, '|' ORDER BY p.acc_db_cd || ':' || p.object_acc_txt) as ParentIDs, STRING_AGG(DISTINCT i.tree_num, '|' ORDER BY i.tree_num) as TreeNumbers, STRING_AGG(DISTINCT p.acc_txt, '|' ORDER BY p.acc_txt) as ParentTreeNumbers, ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = i.term_id AND tl.nm != ( SELECT tn.nm FROM term tn where tn.id = tl.term_id) AND tlt.nm IN ('CHEMICAL SYNONYM', 'EQUIVALENT TERM', 'CAS TYPE 1 NAME')) as Synonyms FROM ( SELECT DISTINCT n.nm, n.parent_id, n.acc_txt tree_num, n.acc_db_cd || ':' || n.object_acc_txt acc, n.secondary_nm cas_rn, n.object_acc_txt, n.object_id term_id FROM dag_node n WHERE n.dag_id = ( SELECT id FROM dag WHERE cd = 'chem') AND n.object_id IN ( SELECT gcr.chem_id FROM gene_chem_reference gcr UNION SELECT cdr.chem_id FROM chem_disease_reference cdr WHERE source_cd = 'C') ORDER BY n.object_acc_txt) i LEFT OUTER JOIN dag_node p ON i.parent_id = p.id GROUP BY i.nm, i.acc, i.cas_rn, i.term_id ORDER BY i.nm;

Date: 2025-07-16 14:56:30
Duration: 5s419ms

SELECT -- All Chem query - need to add in to filter for only curated i.nm as ChemicalName, i.acc as ChemicalID, i.cas_rn as CasRN, ( SELECT t.description FROM term t WHERE t.id = i.term_id) as Definition, STRING_AGG(DISTINCT p.acc_db_cd || ':' || p.object_acc_txt, '|' ORDER BY p.acc_db_cd || ':' || p.object_acc_txt) as ParentIDs, STRING_AGG(DISTINCT i.tree_num, '|' ORDER BY i.tree_num) as TreeNumbers, STRING_AGG(DISTINCT p.acc_txt, '|' ORDER BY p.acc_txt) as ParentTreeNumbers, ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = i.term_id AND tl.nm != ( SELECT tn.nm FROM term tn where tn.id = tl.term_id) AND tlt.nm IN ('CHEMICAL SYNONYM', 'EQUIVALENT TERM', 'CAS TYPE 1 NAME')) as Synonyms FROM ( SELECT DISTINCT n.nm, n.parent_id, n.acc_txt tree_num, n.acc_db_cd || ':' || n.object_acc_txt acc, n.secondary_nm cas_rn, n.object_acc_txt, n.object_id term_id FROM dag_node n WHERE n.dag_id = ( SELECT id FROM dag WHERE cd = 'chem') AND n.object_id IN ( SELECT gcr.chem_id FROM gene_chem_reference gcr UNION SELECT cdr.chem_id FROM chem_disease_reference cdr WHERE source_cd = 'C') ORDER BY n.object_acc_txt) i LEFT OUTER JOIN dag_node p ON i.parent_id = p.id GROUP BY i.nm, i.acc, i.cas_rn, i.term_id ORDER BY i.nm;

Date: 2025-07-16 14:54:36
Duration: 4s639ms

x Hide

Times Reported Time consuming queries #4

x Hide

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence -- ,g.nm AS InferenceGeneSymbol -- get rid of this , -- ,cdr.network_score AS InferenceScore -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE cdr.source_cd = 'C' -- add this in to only get curated -- AND cdr.source_acc_txt is NOT null -- this is null for all curated, only if omim inferred GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm -- ,g.nm -- ,cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:50:24
Duration: 5s555ms

SELECT --/* AllCuratedCDRelationsDAO */\n") c.nm AS ChemicalName, c.acc_txt AS ChemicalID, c.secondary_nm AS CasRN, d.nm AS DiseaseName, d.acc_db_cd || ':' || d.acc_txt AS DiseaseID, a.action_type_nm AS DirectEvidence -- ,g.nm AS InferenceGeneSymbol -- get rid of this , -- ,cdr.network_score AS InferenceScore -- get rid of this STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) AS OmimIDs, STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) AS PubMedIDs FROM chem_disease_reference cdr INNER JOIN term d ON cdr.disease_id = d.id INNER JOIN term c ON cdr.chem_id = c.id LEFT OUTER JOIN chem_disease_reference_axn a ON cdr.id = a.chem_disease_reference_id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE cdr.source_cd = 'C' -- add this in to only get curated GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.nm, d.acc_db_cd || ':' || d.acc_txt, a.action_type_nm -- ,g.nm -- ,cdr.network_score ORDER BY c.nm_sort, d.nm;

Date: 2025-07-16 16:38:25
Duration: 2s381ms