Times Reported Time consuming queries #1

x Hide

update pub2.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.GENE_DISEASE_REFERENCE gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));

Date: 2024-04-16 12:18:02
Duration: 40m29s
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: load - Total duration: 20m24s - Times executed: 8 ]

x Hide

SELECT r.ixn_id, edit.get_ixn_prose (r.ixn_id), r.reference_acc_txt, r.taxon_acc_txt, r.internal_note, r.field_cd, edit.get_ixn_xml (r.ixn_id), to_char(r.create_tm, 'mm-dd-yyyy'), r.evidence_cd, i.create_by FROM edit.REFERENCE_IXN r, edit.IXN i where r.ixn_id = i.id and i.ixn_type_id = ( select id from edit.IXN_TYPE where nm = 'PHENOTYPE');

Date: 2024-04-16 20:38:08
Duration: 2m34s
Database: ctddev51
User: load
Bind query: yes

SELECT r.ixn_id, edit.get_ixn_prose (r.ixn_id), r.reference_acc_txt, r.taxon_acc_txt, r.internal_note, r.field_cd, edit.get_ixn_xml (r.ixn_id), to_char(r.create_tm, 'mm-dd-yyyy'), r.evidence_cd, i.create_by FROM edit.REFERENCE_IXN r, edit.IXN i where r.ixn_id = i.id and i.ixn_type_id = ( select id from edit.IXN_TYPE where nm = 'PHENOTYPE');

Date: 2024-04-16 14:45:00
Duration: 2m34s
Database: ctddev51
User: load
Bind query: yes

SELECT r.ixn_id, edit.get_ixn_prose (r.ixn_id), r.reference_acc_txt, r.taxon_acc_txt, r.internal_note, r.field_cd, edit.get_ixn_xml (r.ixn_id), to_char(r.create_tm, 'mm-dd-yyyy'), r.evidence_cd, i.create_by FROM edit.REFERENCE_IXN r, edit.IXN i where r.ixn_id = i.id and i.ixn_type_id = ( select id from edit.IXN_TYPE where nm = 'PHENOTYPE');

Date: 2024-04-16 14:33:01
Duration: 2m34s
Database: ctddev51
User: load
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: load - Total duration: 17m41s - Times executed: 8 ]

x Hide

select distinct primaryGeneTerm.acc_txt, taxonTerm.acc_txt, gt.gene_acc_txt from pub1.GENE_TAXON gt, pub1.TERM primaryGeneTerm, pub1.TERM taxonTerm where gt.gene_id = primaryGeneTerm.id and gt.taxon_id = taxonTerm.id;

Date: 2024-04-16 14:36:12
Duration: 2m25s
Database: ctddev51
User: load
Bind query: yes

select distinct primaryGeneTerm.acc_txt, taxonTerm.acc_txt, gt.gene_acc_txt from pub1.GENE_TAXON gt, pub1.TERM primaryGeneTerm, pub1.TERM taxonTerm where gt.gene_id = primaryGeneTerm.id and gt.taxon_id = taxonTerm.id;

Date: 2024-04-16 14:00:01
Duration: 2m18s
Database: ctddev51
User: load
Bind query: yes

select distinct primaryGeneTerm.acc_txt, taxonTerm.acc_txt, gt.gene_acc_txt from pub1.GENE_TAXON gt, pub1.TERM primaryGeneTerm, pub1.TERM taxonTerm where gt.gene_id = primaryGeneTerm.id and gt.taxon_id = taxonTerm.id;

Date: 2024-04-16 13:44:13
Duration: 2m15s
Database: ctddev51
User: load
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 1;

Date: 2024-04-16 13:45:15
Duration: 55s445ms
Bind query: yes

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 1;

Date: 2024-04-16 14:00:59
Duration: 49s358ms
Bind query: yes

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 1;

Date: 2024-04-16 20:42:06
Duration: 47s328ms
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

update pub2.TERM set has_exposures = false;

Date: 2024-04-16 11:33:34
Duration: 3m18s
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

select i.root_id, iq.nm from edit.REFERENCE_IXN_QUALIFIER riq, edit.IXN_QUALIFIER iq, edit.REFERENCE_IXN ri, edit.IXN i where riq.ixn_qualifier_id = iq.id and ri.id = riq.reference_ixn_id and ri.ixn_id = i.id;

Date: 2024-04-16 20:38:47
Duration: 26s743ms
Bind query: yes

select i.root_id, iq.nm from edit.REFERENCE_IXN_QUALIFIER riq, edit.IXN_QUALIFIER iq, edit.REFERENCE_IXN ri, edit.IXN i where riq.ixn_qualifier_id = iq.id and ri.id = riq.reference_ixn_id and ri.ixn_id = i.id;

Date: 2024-04-16 14:33:36
Duration: 25s965ms
Bind query: yes

select i.root_id, iq.nm from edit.REFERENCE_IXN_QUALIFIER riq, edit.IXN_QUALIFIER iq, edit.REFERENCE_IXN ri, edit.IXN i where riq.ixn_qualifier_id = iq.id and ri.id = riq.reference_ixn_id and ri.ixn_id = i.id;

Date: 2024-04-16 14:19:57
Duration: 25s213ms
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

update pub2.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.PHENOTYPE_TERM_REFERENCE ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));

Date: 2024-04-16 12:20:23
Duration: 2m21s
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

update pub2.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.CHEM_DISEASE_REFERENCE cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));

Date: 2024-04-16 11:37:32
Duration: 1m51s
Bind query: yes

x Hide

Times Reported Time consuming queries #9

x Hide

update pub2.DAG_NODE set has_exposures = false;

Date: 2024-04-16 11:35:04
Duration: 1m20s
Bind query: yes

x Hide

Times Reported Time consuming queries #10

x Hide

select t.nm, t.acc_txt, ri.ixn_id from edit.REFERENCE_IXN_ANATOMY a, pub1.TERM t, edit.REFERENCE_IXN ri, edit.IXN i where a.anatomy_acc_txt = t.acc_txt and t.object_type_id = 10 and t.acc_db_cd = 'MESH' and ri.id = a.reference_ixn_id and ri.ixn_id = i.id and i.ixn_type_id = ( select id from edit.IXN_TYPE where nm = 'PHENOTYPE') order by level_seq asc;

Date: 2024-04-16 20:38:19
Duration: 9s625ms
Bind query: yes

select t.nm, t.acc_txt, ri.ixn_id from edit.REFERENCE_IXN_ANATOMY a, pub1.TERM t, edit.REFERENCE_IXN ri, edit.IXN i where a.anatomy_acc_txt = t.acc_txt and t.object_type_id = 10 and t.acc_db_cd = 'MESH' and ri.id = a.reference_ixn_id and ri.ixn_id = i.id and i.ixn_type_id = ( select id from edit.IXN_TYPE where nm = 'PHENOTYPE') order by level_seq asc;

Date: 2024-04-16 14:19:31
Duration: 8s806ms
Bind query: yes

select t.nm, t.acc_txt, ri.ixn_id from edit.REFERENCE_IXN_ANATOMY a, pub1.TERM t, edit.REFERENCE_IXN ri, edit.IXN i where a.anatomy_acc_txt = t.acc_txt and t.object_type_id = 10 and t.acc_db_cd = 'MESH' and ri.id = a.reference_ixn_id and ri.ixn_id = i.id and i.ixn_type_id = ( select id from edit.IXN_TYPE where nm = 'PHENOTYPE') order by level_seq asc;

Date: 2024-04-16 13:41:23
Duration: 7s934ms
Bind query: yes

x Hide

Times Reported Time consuming queries #11

x Hide

update pub2.REFERENCE set has_exposures = false;

Date: 2024-04-16 11:35:41
Duration: 35s860ms
Bind query: yes

x Hide

Times Reported Time consuming queries #12

x Hide

[ User: editeu - Total duration: 13s567ms - Times executed: 4 ]

[ User: pubeu - Total duration: 3s372ms - 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 = '588473' 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-04-16 11:32:03
Duration: 3s492ms
Database: ctddev51
User: editeu
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 = '588473' 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-04-16 11:30:47
Duration: 3s458ms
Database: ctddev51
User: editeu
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 = '588473' 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-04-16 11:28:46
Duration: 3s397ms
Database: ctddev51
User: editeu
Bind query: yes

x Hide

Times Reported Time consuming queries #13

x Hide

[ User: editeu - Total duration: 16s841ms - Times executed: 6 ]

x Hide

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub1.TERM t, pub1.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;

Date: 2024-04-16 12:43:35
Duration: 3s977ms
Database: ctddev51
User: editeu
Bind query: yes

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub1.TERM t, pub1.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;

Date: 2024-04-16 11:09:09
Duration: 3s910ms
Database: ctddev51
User: editeu
Bind query: yes

select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub1.TERM t, pub1.TERM_LABEL l where t.OBJECT_TYPE_ID = 1 and t.id = l.TERM_ID;

Date: 2024-04-16 12:43:34
Duration: 3s80ms
Database: ctddev51
User: editeu
Bind query: yes

x Hide

Times Reported Time consuming queries #14

x Hide

INSERT INTO pub2.EXPOSURE (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) SELECT e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id FROM edit.EXPOSURE e INNER JOIN pub2.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';

Date: 2024-04-16 11:30:16
Duration: 10s140ms
Bind query: yes

x Hide

Times Reported Time consuming queries #15

x Hide

[ User: pub2 - Total duration: 9s778ms - Times executed: 1 ]

x Hide

INSERT INTO pub2.EXP_EVENT (exp_marker_lvl, exp_marker_lvl_range, detection_limit_uom, note, detection_freq, detection_freq_range, collection_end_yr, detection_limit_is_loq, detection_limit, detection_limit_range, assay_note, assay_measurement_stat, exp_marker_actor_form_type_id, exp_marker_acc_db_id, exp_marker_acc_txt, collection_start_yr, assay_uom, exp_marker_term_id, exp_marker_type_id, medium_id, medium_nm, medium_term_id, medium_term_acc_txt, has_locations, id, exp_marker_term_nm, exp_marker_term_nm_html) SELECT DISTINCT ee.exp_marker_lvl, ee.exp_marker_lvl_range, ee.detection_limit_uom, ee.note, ee.detection_freq, ee.detection_freq_range, ee.collection_end_yr, ee.detection_limit_is_loq, ee.detection_limit, ee.detection_limit_range, ee.assay_note, ee.assay_measurement_stat, ee.exp_marker_actor_form_type_id, ee.exp_marker_acc_db_id, ee.exp_marker_acc_txt, ee.collection_start_yr, ee.assay_uom, t.id, ee.exp_marker_type_id, ee.medium_id, m.nm, mt.id, m.term_acc_txt, ee.has_locations, ee.id, ee.exp_marker_term_nm, t.nm_html FROM edit.EXP_EVENT ee INNER JOIN edit.EXPOSURE e ON e.exp_event_id = ee.id LEFT OUTER JOIN edit.EXP_MARKER_TYPE emt ON ee.exp_marker_type_id = emt.id LEFT OUTER JOIN pub2.TERM t ON ee.exp_marker_acc_txt = t.acc_txt AND emt.object_type_id = t.object_type_id LEFT OUTER JOIN pub2.MEDIUM m ON ee.medium_id = m.id LEFT OUTER JOIN pub2.TERM mt ON m.term_acc_txt = mt.acc_txt AND m.term_object_type_id = mt.object_type_id;

Date: 2024-04-16 11:29:29
Duration: 9s778ms
Database: ctddev51
User: pub2
Bind query: yes

x Hide

Times Reported Time consuming queries #16

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 /* 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 chemTerm.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 viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'BCL2') and geneTerm.id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'BCL2') and phenotypeTerm.id = ANY (ARRAY ( 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 diseaseTerm.id = ANY (ARRAY ( 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 = 3 and upper(baseTerm.nm) LIKE 'NECROSIS'))) 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-04-16 05:49:05
Duration: 8s739ms
Bind query: yes