Times Reported Time consuming queries #1

x Hide

SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;

Date: 2024-09-18 13:04:14
Duration: 2m48s
Bind query: yes

SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;

Date: 2024-09-18 13:58:40
Duration: 2m40s
Bind query: yes

SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;

Date: 2024-09-18 13:17:31
Duration: 2m36s
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 5m13s - Times executed: 1 ]

x Hide

select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where exists ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'CHEMICALS') and viaChemPTR.via_term_id = dp.descendant_object_id) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;

Date: 2024-09-18 09:44:13
Duration: 5m13s
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pub1 - Total duration: 5m6s - Times executed: 5 ]

x Hide

SELECT gene_id # 015, chem_id # 015, acc_txt # 015 FROM pub2.GENE_CHEM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 # 015;

Date: 2024-09-18 11:34:16
Duration: 1m4s
Database: ctddev51
User: pub1
Bind query: yes

SELECT gene_id # 015, chem_id # 015, acc_txt # 015 FROM pub2.GENE_CHEM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 # 015;

Date: 2024-09-18 11:39:37
Duration: 1m1s
Database: ctddev51
User: pub1
Bind query: yes

SELECT gene_id # 015, chem_id # 015, acc_txt # 015 FROM pub2.GENE_CHEM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 # 015;

Date: 2024-09-18 13:13:44
Duration: 1m
Database: ctddev51
User: pub1
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pub1 - Total duration: 2m57s - Times executed: 3 ]

x Hide

SELECT gene_id # 015, chem_id # 015, acc_txt # 015 FROM pub2.GENE_CHEM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 # 015;

Date: 2024-09-18 16:17:15
Duration: 1m
Database: ctddev51
User: pub1
Bind query: yes

SELECT gene_id # 015, chem_id # 015, acc_txt # 015 FROM pub2.GENE_CHEM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 # 015;

Date: 2024-09-18 16:01:52
Duration: 58s789ms
Database: ctddev51
User: pub1
Bind query: yes

SELECT gene_id # 015, chem_id # 015, acc_txt # 015 FROM pub2.GENE_CHEM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 # 015;

Date: 2024-09-18 13:54:50
Duration: 58s318ms
Database: ctddev51
User: pub1
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct go_term_id # 015, gene_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 13:01:13
Duration: 42s998ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct go_term_id # 015, gene_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 13:14:43
Duration: 42s707ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct go_term_id # 015, gene_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 12:57:51
Duration: 41s192ms
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct go_term_id # 015, gene_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 16:02:51
Duration: 42s482ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct go_term_id # 015, gene_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 13:55:48
Duration: 41s772ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct go_term_id # 015, gene_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 16:18:13
Duration: 40s950ms
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: pub1 - Total duration: 45s616ms - Times executed: 1 ]

x Hide

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct gene_id # 015, go_term_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 12:52:05
Duration: 45s616ms
Database: ctddev51
User: pub1
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 4 UNION # 015 select distinct gene_id # 015, go_term_id # 015, evidence_cd # 015 from pub2.GENE_GO_ANNOT # 015 where is_not = 'f' # 015;

Date: 2024-09-18 11:40:36
Duration: 40s849ms
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 2 # 015;

Date: 2024-09-18 11:39:54
Duration: 9s728ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 2 # 015;

Date: 2024-09-18 13:13:59
Duration: 7s168ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' and term_object_type_id = 2 # 015;

Date: 2024-09-18 13:00:29
Duration: 6s888ms
Bind query: yes

x Hide

Times Reported Time consuming queries #9

x Hide

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;

Date: 2024-09-18 16:02:08
Duration: 7s5ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;

Date: 2024-09-18 16:17:30
Duration: 6s582ms
Bind query: yes

SELECT phenotype_id # 015, term_id # 015, acc_txt # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;

Date: 2024-09-18 13:55:05
Duration: 6s456ms
Bind query: yes

x Hide

Times Reported Time consuming queries #10

x Hide

SELECT chem_id # 015, disease_id # 015, acc_txt # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' # 015;

Date: 2024-09-18 11:39:44
Duration: 4s57ms
Bind query: yes

SELECT chem_id # 015, disease_id # 015, acc_txt # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' # 015;

Date: 2024-09-18 11:34:23
Duration: 3s829ms
Bind query: yes

SELECT chem_id # 015, disease_id # 015, acc_txt # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015, pub2.REFERENCE r # 015 where reference_id = r.id # 015 and source_cd = 'C' # 015;

Date: 2024-09-18 13:13:50
Duration: 3s388ms
Bind query: yes

x Hide

Times Reported Time consuming queries #11

x Hide

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));

Date: 2024-09-18 16:02:58
Duration: 2s383ms
Bind query: yes

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));

Date: 2024-09-18 11:40:42
Duration: 2s331ms
Bind query: yes

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));

Date: 2024-09-18 16:18:19
Duration: 2s240ms
Bind query: yes

x Hide

Times Reported Time consuming queries #12

x Hide

SELECT chem_id # 015, disease_id # 015, acc_txt # 015 FROM pub2.CHEM_DISEASE_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' # 015;

Date: 2024-09-18 13:54:57
Duration: 3s339ms
Bind query: yes

SELECT chem_id # 015, disease_id # 015, acc_txt # 015 FROM pub2.CHEM_DISEASE_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' # 015;

Date: 2024-09-18 16:01:59
Duration: 3s297ms
Bind query: yes

SELECT chem_id # 015, disease_id # 015, acc_txt # 015 FROM pub2.CHEM_DISEASE_REFERENCE gdr # 015 LEFT OUTER JOIN pub2.REFERENCE r ON gdr.reference_id = r.id # 015 where source_cd = 'C' # 015;

Date: 2024-09-18 16:17:22
Duration: 3s236ms
Bind query: yes

x Hide

Times Reported Time consuming queries #13

x Hide

[ User: pubeu - Total duration: 6s564ms - Times executed: 1 ]

x Hide

select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'MAPK1') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;

Date: 2024-09-18 13:23:33
Duration: 6s564ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #14

x Hide

[ User: pubeu - Total duration: 3s461ms - Times executed: 1 ]

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 = '589967' 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: 2024-09-18 05:45:12
Duration: 3s461ms
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 = '1432724' 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: 2024-09-18 05:45:13
Duration: 1s55ms
Bind query: yes

x Hide

Times Reported Time consuming queries #15

x Hide

SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1291235)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;

Date: 2024-09-18 05:48:27
Duration: 4s271ms
Bind query: yes

x Hide

Times Reported Time consuming queries #16

x Hide

select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;

Date: 2024-09-18 05:48:48
Duration: 4s3ms
Bind query: yes