Data loading is too slow, query problem?

baylor2016 — Thu, 10 Aug 2017 19:58:17 GMT

Hi,

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;

Re: Data loading is too slow, query problem?

vishsaggi — Thu, 10 Aug 2017 20:07:50 GMT

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:

left join IVDMedUse b WITH (Nolock)

Re: Data loading is too slow, query problem?

sasiparupudi1 — Thu, 10 Aug 2017 20:31:33 GMT

I see a number of issues that will cause slowness in your query..

A lot of nested subqueries using distincts an also lower function with like

My suggestion would be to break the query into multiple and do the joins in qlik and/or do the transformations inside qlik

Hth

Sasi

Re: Data loading is too slow, query problem?

sasiparupudi1 — Thu, 10 Aug 2017 20:33:25 GMT

Also, please post the contents of the include file. .

topic Re: Data loading is too slow, query problem? in QlikView

Data loading is too slow, query problem?

Re: Data loading is too slow, query problem?

Re: Data loading is too slow, query problem?

Re: Data loading is too slow, query problem?