Times Reported Time consuming queries #1

x Hide

update pub1.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));

Date: 2024-04-22 12:29:11
Duration: 27m46s
Bind query: yes

update pub1.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));

Date: 2024-04-22 16:23:16
Duration: 27m12s
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

update pub1.TERM set has_exposures = false;

Date: 2024-04-22 15:53:14
Duration: 2m34s
Bind query: yes

update pub1.TERM set has_exposures = false;

Date: 2024-04-22 11:58:29
Duration: 2m14s
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

update pub1.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));

Date: 2024-04-22 16:25:11
Duration: 1m54s
Bind query: yes

update pub1.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));

Date: 2024-04-22 12:31:04
Duration: 1m52s
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

update pub1.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));

Date: 2024-04-22 12:01:24
Duration: 1m24s
Bind query: yes

update pub1.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));

Date: 2024-04-22 15:56:04
Duration: 1m21s
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

update pub1.DAG_NODE set has_exposures = false;

Date: 2024-04-22 15:54:17
Duration: 57s799ms
Bind query: yes

update pub1.DAG_NODE set has_exposures = false;

Date: 2024-04-22 11:59:28
Duration: 53s563ms
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: editeu - Total duration: 54s319ms - Times executed: 16 ]

[ User: pubeu - Total duration: 3s380ms - 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-22 14:21:33
Duration: 3s484ms
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-22 14:24:58
Duration: 3s457ms
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-22 14:38:05
Duration: 3s436ms
Database: ctddev51
User: editeu
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

update pub1.REFERENCE set has_exposures = false;

Date: 2024-04-22 11:59:59
Duration: 28s783ms
Bind query: yes

update pub1.REFERENCE set has_exposures = false;

Date: 2024-04-22 15:54:42
Duration: 23s224ms
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

INSERT INTO pub1.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 pub1.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';

Date: 2024-04-22 11:56:14
Duration: 10s455ms
Bind query: yes

INSERT INTO pub1.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 pub1.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';

Date: 2024-04-22 15:50:39
Duration: 10s209ms
Bind query: yes

x Hide

Times Reported Time consuming queries #9

x Hide

[ User: pub1 - Total duration: 18s913ms - Times executed: 2 ]

x Hide

INSERT INTO pub1.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 pub1.TERM t ON ee.exp_marker_acc_txt = t.acc_txt AND emt.object_type_id = t.object_type_id LEFT OUTER JOIN pub1.MEDIUM m ON ee.medium_id = m.id LEFT OUTER JOIN pub1.TERM mt ON m.term_acc_txt = mt.acc_txt AND m.term_object_type_id = mt.object_type_id;

Date: 2024-04-22 15:49:55
Duration: 9s474ms
Database: ctddev51
User: pub1
Bind query: yes

INSERT INTO pub1.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 pub1.TERM t ON ee.exp_marker_acc_txt = t.acc_txt AND emt.object_type_id = t.object_type_id LEFT OUTER JOIN pub1.MEDIUM m ON ee.medium_id = m.id LEFT OUTER JOIN pub1.TERM mt ON m.term_acc_txt = mt.acc_txt AND m.term_object_type_id = mt.object_type_id;

Date: 2024-04-22 11:55:29
Duration: 9s438ms
Database: ctddev51
User: pub1
Bind query: yes

x Hide

Times Reported Time consuming queries #10

x Hide

INSERT INTO pub1.EXP_EVENT_LOCATION (id, exp_event_id, country_id, geographic_region_id, geographic_region_nm, locality_txt) SELECT DISTINCT eel.id, eel.exp_event_id, eel.country_id, eel.geographic_region_id, eel.geographic_region_nm, eel.locality_txt FROM edit.EXP_EVENT_LOCATION eel INNER JOIN edit.EXPOSURE e ON e.exp_event_id = eel.exp_event_id;

Date: 2024-04-22 15:50:01
Duration: 6s441ms
Bind query: yes

INSERT INTO pub1.EXP_EVENT_LOCATION (id, exp_event_id, country_id, geographic_region_id, geographic_region_nm, locality_txt) SELECT DISTINCT eel.id, eel.exp_event_id, eel.country_id, eel.geographic_region_id, eel.geographic_region_nm, eel.locality_txt FROM edit.EXP_EVENT_LOCATION eel INNER JOIN edit.EXPOSURE e ON e.exp_event_id = eel.exp_event_id;

Date: 2024-04-22 11:55:36
Duration: 6s342ms
Bind query: yes

x Hide

Times Reported Time consuming queries #11

x Hide

INSERT INTO pub1.EXP_STRESSOR (id, chem_id, chem_term_nm, chem_term_nm_html, chem_acc_txt, chem_acc_db_id, src_details, sample_qty, note) SELECT DISTINCT es.id, t.id, es.chem_term_nm, t.nm_html, es.chem_acc_txt, es.chem_acc_db_id, es.src_details, es.sample_qty, es.note FROM edit.EXP_STRESSOR es INNER JOIN edit.EXPOSURE e ON e.exp_stressor_id = es.id LEFT OUTER JOIN pub1.TERM t ON t.acc_txt = es.chem_acc_txt AND t.object_type_id = 2;

Date: 2024-04-22 15:50:12
Duration: 5s372ms
Bind query: yes

INSERT INTO pub1.EXP_STRESSOR (id, chem_id, chem_term_nm, chem_term_nm_html, chem_acc_txt, chem_acc_db_id, src_details, sample_qty, note) SELECT DISTINCT es.id, t.id, es.chem_term_nm, t.nm_html, es.chem_acc_txt, es.chem_acc_db_id, es.src_details, es.sample_qty, es.note FROM edit.EXP_STRESSOR es INNER JOIN edit.EXPOSURE e ON e.exp_stressor_id = es.id LEFT OUTER JOIN pub1.TERM t ON t.acc_txt = es.chem_acc_txt AND t.object_type_id = 2;

Date: 2024-04-22 11:55:47
Duration: 5s270ms
Bind query: yes

x Hide

Times Reported Time consuming queries #12

x Hide

update pub1.TERM set has_exposures = true where id in ( select ancestor_object_id from pub1.DAG_PATH where descendant_object_id in ( select distinct s.chem_id from pub1.EXP_STRESSOR s union select distinct e.exp_marker_term_id from pub1.EXP_EVENT e union select distinct e.phenotype_id from pub1.EXP_OUTCOME e union select distinct e.disease_id from pub1.EXP_OUTCOME e union select distinct e.term_id from pub1.EXP_RECEPTOR e) union select distinct e.exp_marker_term_id from pub1.EXP_EVENT e UNION select distinct e.term_id from pub1.EXP_RECEPTOR e where object_type_id = ( select id from pub1.OBJECT_TYPE where cd = 'gene'));

Date: 2024-04-22 11:58:35
Duration: 5s496ms
Bind query: yes

update pub1.TERM set has_exposures = true where id in ( select ancestor_object_id from pub1.DAG_PATH where descendant_object_id in ( select distinct s.chem_id from pub1.EXP_STRESSOR s union select distinct e.exp_marker_term_id from pub1.EXP_EVENT e union select distinct e.phenotype_id from pub1.EXP_OUTCOME e union select distinct e.disease_id from pub1.EXP_OUTCOME e union select distinct e.term_id from pub1.EXP_RECEPTOR e) union select distinct e.exp_marker_term_id from pub1.EXP_EVENT e UNION select distinct e.term_id from pub1.EXP_RECEPTOR e where object_type_id = ( select id from pub1.OBJECT_TYPE where cd = 'gene'));

Date: 2024-04-22 15:53:19
Duration: 4s661ms
Bind query: yes

x Hide

Times Reported Time consuming queries #13

x Hide

INSERT INTO pub1.EXP_RECEPTOR (id, description, object_type_id, term_id, term_nm, term_nm_html, term_acc_txt, term_acc_db_id, qty, note, age, age_range, age_uom_id, age_qualifier_id, gender_id, age_uom_nm, age_qualifier_nm, gender_nm, gender_nm_html) SELECT DISTINCT er.id, r.nm, er.object_type_id, t.id, er.term_nm, t.nm_html, er.term_acc_txt, er.term_acc_db_id, er.qty, er.note, er.age, er.age_range, er.age_uom_id, er.age_qualifier_id, er.gender_id, au.nm, aq.nm, g.nm, g.nm_html FROM edit.EXP_RECEPTOR er INNER JOIN edit.EXPOSURE e ON e.exp_receptor_id = er.id INNER JOIN edit.RECEPTOR r ON er.receptor_id = r.id LEFT OUTER JOIN pub1.AGE_UOM au ON er.age_uom_id = au.id LEFT OUTER JOIN pub1.AGE_QUALIFIER aq ON er.age_qualifier_id = aq.id LEFT OUTER JOIN pub1.GENDER g ON er.gender_id = g.id LEFT OUTER JOIN pub1.TERM t ON er.term_acc_txt = t.acc_txt AND er.object_type_id = t.object_type_id;

Date: 2024-04-22 11:55:57
Duration: 4s982ms
Bind query: yes

INSERT INTO pub1.EXP_RECEPTOR (id, description, object_type_id, term_id, term_nm, term_nm_html, term_acc_txt, term_acc_db_id, qty, note, age, age_range, age_uom_id, age_qualifier_id, gender_id, age_uom_nm, age_qualifier_nm, gender_nm, gender_nm_html) SELECT DISTINCT er.id, r.nm, er.object_type_id, t.id, er.term_nm, t.nm_html, er.term_acc_txt, er.term_acc_db_id, er.qty, er.note, er.age, er.age_range, er.age_uom_id, er.age_qualifier_id, er.gender_id, au.nm, aq.nm, g.nm, g.nm_html FROM edit.EXP_RECEPTOR er INNER JOIN edit.EXPOSURE e ON e.exp_receptor_id = er.id INNER JOIN edit.RECEPTOR r ON er.receptor_id = r.id LEFT OUTER JOIN pub1.AGE_UOM au ON er.age_uom_id = au.id LEFT OUTER JOIN pub1.AGE_QUALIFIER aq ON er.age_qualifier_id = aq.id LEFT OUTER JOIN pub1.GENDER g ON er.gender_id = g.id LEFT OUTER JOIN pub1.TERM t ON er.term_acc_txt = t.acc_txt AND er.object_type_id = t.object_type_id;

Date: 2024-04-22 15:50:22
Duration: 4s878ms
Bind query: yes

x Hide

Times Reported Time consuming queries #14

x Hide