When I added the following queries to my qvw project. The data loading takes forever. It only takes several minutes without it. In Oracle, it takes 242 seconds for first 50 rows and it does not take too long to get all the data into Excel (less then 5 minutes). Please advise. Thanks
Medication: sql $(include=EncounterQuerySnippet.txt) , StatinMed as ( SELECT distinct medication_id, pharm_class_c, pharm_subclass_c, name as Medication FROM clarity_medication where lower(name) like '%atorvastatin%' or lower(name) like '%fluvastatin%' or lower(name) like '%lovastatin%' or lower(name) like '%pravastatin%' or lower(name) like '%rosuvastatin%' or lower(name) like '%simvastatin%' or lower(name) like '%pitavastatin%' ), AMIMed as ( SELECT distinct medication_id, pharm_class_c, pharm_subclass_c, name as Medication FROM clarity_medication where lower(name) like '%acebutolol%' or lower(name) like '%atenolol%' or lower(name) like '%betaxolol%' or lower(name) like '%betaxolol%' or lower(name) like '%bisoprolol fumarate%' or lower(name) like '%carteolol%' or lower(name) like '%carvedilol%' or lower(name) like '%esmolol%' or lower(name) like '%labetalol%' or lower(name) like '%metoprolol%' or lower(name) like '%nadolol%' or lower(name) like '%nebivolol%' or lower(name) like '%penbutolol%' or lower(name) like '%pindolol%' or lower(name) like '%propranolol%' or lower(name) like '%sotalol%' ), IVDMed as ( SELECT distinct medication_id, pharm_class_c, pharm_subclass_c, name as Medication FROM clarity_medication where lower(name) like '%aspirin%' or lower(name) like '%warfarin%' or lower(name) like '%clopidogrel%' or lower(name) like '%prasugrel%' or lower(name) like '%ticagrelor%' or lower(name) like '%apixaban%' or lower(name) like '%betrixaban%' or lower(name) like '%darexaban%' or lower(name) like '%edoxaban%' or lower(name) like '%otamixaban%' or lower(name) like '%rivaroxaban%' or lower(name) like '%argatroban%' or lower(name) like '%dabigatran%' or lower(name) like '%efegatran%' or lower(name) like '%inogatran%' ), IVDMedUse as ( select ordering_date, a.pat_enc_csn_id, a.pat_id, c.medication from order_med a inner join encounter b on a.pat_enc_csn_id = b.pat_enc_csn_id inner join IVDMed c on c.medication_id = a.medication_id where a.ORDER_STATUS_C <> 4 ), AMIMedUse as ( select ordering_date, a.pat_enc_csn_id, a.pat_id, c.medication from order_med a inner join encounter b on a.pat_enc_csn_id = b.pat_enc_csn_id inner join AMIMed c on c.medication_id = a.medication_id where a.ORDER_STATUS_C <> 4 ),StatinMedUse as ( select ordering_date, a.pat_enc_csn_id, a.pat_id, c.medication from order_med a inner join encounter b on a.pat_enc_csn_id = b.pat_enc_csn_id inner join StatinMed c on c.medication_id = a.medication_id where a.ORDER_STATUS_C <> 4 ) select a.pat_enc_csn_id, b.ordering_date as IVDMedOrderDate, c.ordering_date as AMIMedOrderDate, d.ordering_date as StatinMedUse, case when b.ordering_date is null then 0 else 1 end as FLAG_IVDMed, case when c.ordering_date is null then 0 else 1 end as FLAG_AMIMed, case when d.ordering_date is null then 0 else 1 end as FLAG_StatinMed from encounter a left join IVDMedUse b on a.pat_enc_csn_id = b.pat_enc_csn_id left join AMIMedUse c on a.pat_enc_csn_id = c.pat_enc_csn_id left join StatinMedUse d on a.pat_enc_csn_id = d.pat_enc_csn_id where b.ordering_date is not null or c.ordering_date is not null or d.ordering_date is not null;
242 seconds in Oracle for just 50 rows is also too slow i believe. Did you check with your DBA guys if they can add any indexes to the tables involved. AND try to use (NOLOCK) after every table name like:
