asked
suggestions to optimize this query
with attending_doctorid_number as (select count(ref_tab.npicodes) as refcnt ,count(plff.attendingdoctoridnumber) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.npi ref_tab on (plff.attendingdoctoridnumber = ref_tab.npicodes) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W'), ordering_providerid_number as (select count(ref_tab.npicodes) as refcnt ,count(plff.orderingprovideridnumber) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.npi ref_tab on (plff.orderingprovideridnumber = ref_tab.npicodes) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W'), universal_serviceidentifier_identifier as (select count(ref_tab.code) as refcnt ,count(plff.universalserviceidentifieridentifier) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.loinc_codes ref_tab on (plff.universalserviceidentifieridentifier = ref_tab.code) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W'), universalservice_identifieralternate_identifier as (select count(ref_tab.code) as refcnt ,count(plff.universalserviceidentifieralternateidentifier) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.loinc_codes ref_tab on (plff.universalserviceidentifieralternateidentifier = ref_tab.code) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W'), procedurecode_identifier as (select count(ref_tab.cptcodes) as refcnt ,count(plff.procedurecodeidentifier) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.cpt ref_tab on (plff.procedurecodeidentifier = ref_tab.cptcodes) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W'), observation_identifier_identifier as (select count(ref_tab.code) as refcnt ,count(plff.observationidentifieridentifier) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.loinc_codes ref_tab on (plff.observationidentifieridentifier = ref_tab.code) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W'), observation_identifier_alternate_identifier as (select count(ref_tab.code) as refcnt ,count(plff.observationidentifieralternateidentifier) as ptcnt from labffinputdc plff LEFT OUTER JOIN vdatalake.loinc_codes ref_tab on (plff.observationidentifieralternateidentifier = ref_tab.code) where plff.workflowtransactionid='0000071-160610161227660-oozie-mapr-W') SELECT CONCAT((round(coalesce(((attending_doctorid_number.ptcnt - attending_doctorid_number.refcnt)/attending_doctorid_number.ptcnt),0)*100,2))," :","attendingdoctoridnumber") as attending_doctorid_number, null as ordering_providerid_number, null as universal_serviceidentifier_identifier, null as universalservice_identifieralternate_identifier, null as procedurecode_identifier, null as observation_identifier_identifier, null as observation_identifier_alternate_identifier FROM attending_doctorid_number UNION ALL SELECT null as attending_doctorid_number, CONCAT((round(coalesce(((ordering_providerid_number.ptcnt - ordering_providerid_number.refcnt)/ordering_providerid_number.ptcnt),0)*100,2))," :","orderingprovideridnumber") as ordering_providerid_number, null as universal_serviceidentifier_identifier, null as universalservice_identifieralternate_identifier, null as procedurecode_identifier, null as observation_identifier_identifier, null as observation_identifier_alternate_identifier from ordering_providerid_number UNION ALL select null as attending_doctorid_number, null as ordering_providerid_number, CONCAT((round(coalesce(((universal_serviceidentifier_identifier.ptcnt - universal_serviceidentifier_identifier.refcnt)/universal_serviceidentifier_identifier.ptcnt),0)*100,2))," :","universalserviceidentifieridentifier") as universal_serviceidentifier_identifier, null as universalservice_identifieralternate_identifier, null as procedurecode_identifier, null as observation_identifier_identifier, null as observation_identifier_alternate_identifier from universal_serviceidentifier_identifier UNION ALL select null as attending_doctorid_number, null as ordering_providerid_number, null as universal_serviceidentifier_identifier, CONCAT((round(coalesce(((universalservice_identifieralternate_identifier.ptcnt - universalservice_identifieralternate_identifier.refcnt)/universalservice_identifieralternate_identifier.ptcnt),0)*100,2))," :","universalserviceidentifieralternateidentifier") as universalservice_identifieralternate_identifier, null as procedurecode_identifier, null as observation_identifier_identifier, null as observation_identifier_alternate_identifier from universalservice_identifieralternate_identifier UNION ALL select null as attending_doctorid_number, null as ordering_providerid_number, null as universal_serviceidentifier_identifier, null as universalservice_identifieralternate_identifier, CONCAT((round(coalesce(((procedurecode_identifier.ptcnt - procedurecode_identifier.refcnt)/procedurecode_identifier.ptcnt),0)*100,2))," :","procedurecodeidentifier") as procedurecode_identifier, null as observation_identifier_identifier, null as observation_identifier_alternate_identifier from procedurecode_identifier UNION ALL select null as attending_doctorid_number, null as ordering_providerid_number, null as universal_serviceidentifier_identifier, null as universalservice_identifieralternate_identifier, null as procedurecode_identifier, CONCAT((round(coalesce(((observation_identifier_identifier.ptcnt - observation_identifier_identifier.refcnt)/observation_identifier_identifier.ptcnt),0)*100,2))," :","observationidentifieridentifier") as observation_identifier_identifier , null as observation_identifier_alternate_identifier from observation_identifier_identifier UNION ALL select null as attending_doctorid_number, null as ordering_providerid_number, null as universal_serviceidentifier_identifier, null as universalservice_identifieralternate_identifier, null as procedurecode_identifier, null as observation_identifier_identifier, CONCAT((round(coalesce(((observation_identifier_alternate_identifier.ptcnt - observation_identifier_alternate_identifier.refcnt)/observation_identifier_alternate_identifier.ptcnt),0)*100,2))," :","observationidentifieralternateidentifier") as observation_identifier_alternate_identifier from observation_identifier_alternate_identifier
If it were me, I would start by rewriting this to simplify it; your co-workers will thank you. I would suggest replacing the queries that are union-ed together into one query using ANSI joins (most likely outer joins) and incorporating the WITH queries into the join conditions and, if needed, the where clause. Doing this will also improve the result set by returning a single record's data on one row. In the current format you will have what I call a "cascading result set" with n rows per record where n is the number of queries union-ed together. After that I would review the execution plan to see if there are any areas of opportunity with regard to performance. I always start by looking for table scans and then moving on to index scans. Another thing to investigate is the degree of parallelism. You can add a query hint to the end of the query if you need to override that. The database will also offer index suggestions when the plan is generated. It never hurts to check those to see what suggestions are recommended. I hope this helps.
18 People are following this question.
