Times Reported Time consuming queries #1

x Hide

[ User: editeu - Total duration: 17s450ms - Times executed: 4 ]

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-02-09 09:57:35
Duration: 5s171ms
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-02-09 09:57:53
Duration: 4s792ms
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-02-09 09:57:52
Duration: 3s793ms
Database: ctddev51
User: editeu
Bind query: yes

x Hide

Times Reported Time consuming queries #2

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-02-09 05:49:13
Duration: 15s109ms
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1275443')) ORDER BY r.sort_txt LIMIT 50;

Date: 2024-02-09 05:47:54
Duration: 2s309ms
Bind query: yes

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2036477')) ORDER BY r.sort_txt LIMIT 50;

Date: 2024-02-09 05:48:06
Duration: 2s248ms
Bind query: yes

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1275443')) ORDER BY r.sort_txt LIMIT 50;

Date: 2024-02-09 05:47:57
Duration: 2s72ms
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: editeu - Total duration: 3s407ms - Times executed: 1 ]

x Hide

select distinct e.reference_acc_txt, referenceExp.create_by, referenceExp.create_tm, pref.abbr_authors_txt as author, referenceExp.email_addr, ( Select STRING_AGG(distinct studyFactor.cd, '|')) as studyFactor, referenceExp.author_summary, referenceExp.curator_comments, ( Select STRING_AGG(distinct eventproject.project_nm, '|')) as associatedStudyTitles, CONCAT(eevent.collection_start_yr, '-', eevent.collection_end_yr) as enrollmentYears, ( Select STRING_AGG(distinct studyFactor.nm, '|')) as studyFactor, ( Select STRING_AGG(distinct stressorSrcType.cd, '|')) as stressorSourceCategory, stressor.chem_term_nm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, ereceptor.qty as nbrReceptors, receptor.nm, ereceptor.term_nm, ereceptor.term_acc_txt, ereceptor.note as receptor_note, ( Select STRING_AGG(distinct COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ' || tobaccoUse.nm, COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ', tobaccoUse.nm)), '|')) as smokerStatus, ereceptor.age || ' ' || age_uom.nm || ' (' || age_qualifier.nm || ')' as ageRange, ( Select STRING_AGG(CAST(pct as int) || '% ' || gender.nm_html, '|') from edit.exp_receptor_gender expgender left outer join edit.gender on expgender.gender_id = gender.id where exp_receptor_id = ereceptor.id) as genderDetails, ( Select STRING_AGG(distinct CAST(receptorRace.pct as int) || '% ' || race.cd, '|')) as receptorRace, ( Select STRING_AGG(distinct eventAssayMethod.nm, '|')) as assayMethods, CONCAT(eevent.detection_limit, ' ', eevent.detection_limit_uom) as detectionLimit, eevent.detection_freq as detectionFreq, medium.nm as medium_nm, eevent.exp_marker_term_nm, CONCAT(eevent.exp_marker_lvl, ' ', eevent.assay_uom) as markerLevel, eevent.assay_measurement_stat, eevent.assay_note, ( Select STRING_AGG(distinct country.nm, '|')) as country, ( Select STRING_AGG(distinct eventLocation.geographic_region_nm, '|')) as stateOrProvince, ( Select STRING_AGG(distinct eventLocation.locality_txt, '|')) as region, eevent.note as eventNote, eiot.description as outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_term_nm, ( Select STRING_AGG(distinct expAnatomy.anatomy_term_nm, '|')) as anatomy, outcome.note as outcomeNote from edit.exposure e left outer join pub1.reference pref on pref.acc_txt = e.reference_acc_txt left outer join edit.reference_exp referenceExp on referenceExp.reference_acc_txt = e.reference_acc_txt left outer join edit.exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id left outer join edit.study_factor studyFactor on studyFactor.id = expStudyFactor.study_factor_id left outer join edit.exp_event_project eventproject on eventproject.exp_event_id = e.exp_event_id inner join edit.exp_stressor stressor on e.exp_stressor_id = stressor.id left outer join edit.exp_receptor ereceptor on e.exp_receptor_id = ereceptor.id left outer join edit.receptor receptor on ereceptor.receptor_id = receptor.id left outer join edit.age_uom age_uom on ereceptor.age_uom_id = age_uom.id left outer join edit.age_qualifier age_qualifier on ereceptor.age_qualifier_id = age_qualifier.id left outer join edit.exp_event eevent on e.exp_event_id = eevent.id left outer join edit.medium medium on eevent.medium_id = medium.id left outer join edit.exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id left outer join edit.exp_stressor_src_type stressorSrcType on esss.exp_stressor_src_type_id = stressorSrcType.id left outer join edit.exp_receptor_tobacco_use receptorTobaccoUse on ereceptor.id = receptorTobaccoUse.exp_receptor_id left outer join edit.tobacco_use tobaccoUse on receptorTobaccoUse.tobacco_use_id = tobaccoUse.id left outer join edit.exp_receptor_race receptorRace on ereceptor.id = receptorRace.exp_receptor_id left outer join edit.race race on receptorRace.race_id = race.id left outer join edit.exp_event_location eventLocation on eevent.id = eventLocation.exp_event_id left outer join edit.country on eventLocation.country_id = country.id left outer join edit.exp_outcome outcome on e.exp_outcome_id = outcome.id left outer join edit.exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id left outer join edit.exp_anatomy expAnatomy on outcome.id = expAnatomy.exp_outcome_id left outer join edit.exp_event_assay_method eventAssayMethod on eevent.id = eventAssayMethod.exp_event_id where e.reference_acc_txt = '31103473' group by e.reference_acc_txt, author, referenceExp.create_by, referenceExp.create_tm, referenceExp.email_addr, referenceExp.author_summary, referenceExp.curator_comments, pref.abbr_authors_txt, enrollmentYears, stressor.chem_term_nm, stressorSrcDetails, stressorSampleQty, stressorNote, nbrReceptors, receptor.nm, ereceptor.term_nm, ereceptor.term_acc_txt, receptor_note, ageRange, detectionLimit, detectionFreq, medium_nm, eevent.exp_marker_term_nm, markerLevel, eevent.assay_measurement_stat, eevent.assay_note, eventNote, outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_term_nm, outcomeNote, ereceptor.id;

Date: 2024-02-09 09:40:48
Duration: 3s468ms
Bind query: yes

select distinct e.reference_acc_txt, referenceExp.create_by, referenceExp.create_tm, pref.abbr_authors_txt as author, referenceExp.email_addr, ( Select STRING_AGG(distinct studyFactor.cd, '|')) as studyFactor, referenceExp.author_summary, referenceExp.curator_comments, ( Select STRING_AGG(distinct eventproject.project_nm, '|')) as associatedStudyTitles, CONCAT(eevent.collection_start_yr, '-', eevent.collection_end_yr) as enrollmentYears, ( Select STRING_AGG(distinct studyFactor.nm, '|')) as studyFactor, ( Select STRING_AGG(distinct stressorSrcType.cd, '|')) as stressorSourceCategory, stressor.chem_term_nm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, ereceptor.qty as nbrReceptors, receptor.nm, ereceptor.term_nm, ereceptor.term_acc_txt, ereceptor.note as receptor_note, ( Select STRING_AGG(distinct COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ' || tobaccoUse.nm, COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ', tobaccoUse.nm)), '|')) as smokerStatus, ereceptor.age || ' ' || age_uom.nm || ' (' || age_qualifier.nm || ')' as ageRange, ( Select STRING_AGG(CAST(pct as int) || '% ' || gender.nm_html, '|') from edit.exp_receptor_gender expgender left outer join edit.gender on expgender.gender_id = gender.id where exp_receptor_id = ereceptor.id) as genderDetails, ( Select STRING_AGG(distinct CAST(receptorRace.pct as int) || '% ' || race.cd, '|')) as receptorRace, ( Select STRING_AGG(distinct eventAssayMethod.nm, '|')) as assayMethods, CONCAT(eevent.detection_limit, ' ', eevent.detection_limit_uom) as detectionLimit, eevent.detection_freq as detectionFreq, medium.nm as medium_nm, eevent.exp_marker_term_nm, CONCAT(eevent.exp_marker_lvl, ' ', eevent.assay_uom) as markerLevel, eevent.assay_measurement_stat, eevent.assay_note, ( Select STRING_AGG(distinct country.nm, '|')) as country, ( Select STRING_AGG(distinct eventLocation.geographic_region_nm, '|')) as stateOrProvince, ( Select STRING_AGG(distinct eventLocation.locality_txt, '|')) as region, eevent.note as eventNote, eiot.description as outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_term_nm, ( Select STRING_AGG(distinct expAnatomy.anatomy_term_nm, '|')) as anatomy, outcome.note as outcomeNote from edit.exposure e left outer join pub1.reference pref on pref.acc_txt = e.reference_acc_txt left outer join edit.reference_exp referenceExp on referenceExp.reference_acc_txt = e.reference_acc_txt left outer join edit.exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id left outer join edit.study_factor studyFactor on studyFactor.id = expStudyFactor.study_factor_id left outer join edit.exp_event_project eventproject on eventproject.exp_event_id = e.exp_event_id inner join edit.exp_stressor stressor on e.exp_stressor_id = stressor.id left outer join edit.exp_receptor ereceptor on e.exp_receptor_id = ereceptor.id left outer join edit.receptor receptor on ereceptor.receptor_id = receptor.id left outer join edit.age_uom age_uom on ereceptor.age_uom_id = age_uom.id left outer join edit.age_qualifier age_qualifier on ereceptor.age_qualifier_id = age_qualifier.id left outer join edit.exp_event eevent on e.exp_event_id = eevent.id left outer join edit.medium medium on eevent.medium_id = medium.id left outer join edit.exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id left outer join edit.exp_stressor_src_type stressorSrcType on esss.exp_stressor_src_type_id = stressorSrcType.id left outer join edit.exp_receptor_tobacco_use receptorTobaccoUse on ereceptor.id = receptorTobaccoUse.exp_receptor_id left outer join edit.tobacco_use tobaccoUse on receptorTobaccoUse.tobacco_use_id = tobaccoUse.id left outer join edit.exp_receptor_race receptorRace on ereceptor.id = receptorRace.exp_receptor_id left outer join edit.race race on receptorRace.race_id = race.id left outer join edit.exp_event_location eventLocation on eevent.id = eventLocation.exp_event_id left outer join edit.country on eventLocation.country_id = country.id left outer join edit.exp_outcome outcome on e.exp_outcome_id = outcome.id left outer join edit.exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id left outer join edit.exp_anatomy expAnatomy on outcome.id = expAnatomy.exp_outcome_id left outer join edit.exp_event_assay_method eventAssayMethod on eevent.id = eventAssayMethod.exp_event_id where e.reference_acc_txt = '31103473' group by e.reference_acc_txt, author, referenceExp.create_by, referenceExp.create_tm, referenceExp.email_addr, referenceExp.author_summary, referenceExp.curator_comments, pref.abbr_authors_txt, enrollmentYears, stressor.chem_term_nm, stressorSrcDetails, stressorSampleQty, stressorNote, nbrReceptors, receptor.nm, ereceptor.term_nm, ereceptor.term_acc_txt, receptor_note, ageRange, detectionLimit, detectionFreq, medium_nm, eevent.exp_marker_term_nm, markerLevel, eevent.assay_measurement_stat, eevent.assay_note, eventNote, outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_term_nm, outcomeNote, ereceptor.id;

Date: 2024-02-09 09:39:33
Duration: 3s407ms
Database: ctddev51
User: editeu
Bind query: yes

x Hide

Times Reported Time consuming queries #5

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-02-09 05:48:48
Duration: 5s839ms
Bind query: yes

x Hide