Help*** Query performance - sub-queries vs. volatile tables

Database
Enthusiast

Help*** Query performance - sub-queries vs. volatile tables

Hello to All,

 

I am running the following query in SQL Assistant and it takes more than 30 minutes to run just 1-day worth of data. I replicated this query using volatile tables and the performance is great (5 minutes to pull 3-months worth of data). The problem is that neither MS PowerPivot nor Tibco Spotfire handle queries with volatile tables.

 

Just wondering if I am doing something wrong with the query below and what are the options to improve its performance?

 

Thanks a million and kind regards,

 

select

lof_list.issued_dt

,lof_list.pnr_create_dt

,lof_list.Initial_tkt_num

,lof_list.source_rcrd_loc

,lof.dist_channel

,lof_list.pax_name

,lof_list.tktng_agency_id

,agy.agency_name

,agy.hol_id

,agy.hol_agency_name

,lof_list.bkng_agency_id

,lof_list.tour_cd

,lof_list.exchgd_tkt_info

,coalesce(cpn11.od11,'')||','||coalesce(cpn12.od12,'')||','||coalesce(cpn13.od13,'')||','||coalesce(cpn14.od14,'')as lof_od

,coalesce(cpn11.cos11,'')||','||coalesce(cpn12.cos12,'')||','||coalesce(cpn13.cos13,'')||','||coalesce(cpn14.cos14,'')as lof_cos

,coalesce(cpn11.mktg_carr11,'')||','||coalesce(cpn12.mktg_carr12,'')||','||coalesce(cpn13.mktg_carr13,'')||','||coalesce(cpn14.mktg_carr14,'')as lof_mktg_carr

,coalesce(cpn11.oper_carr11,'')||','||coalesce(cpn12.oper_carr12,'')||','||coalesce(cpn13.oper_carr13,'')||','||coalesce(cpn14.oper_carr14,'')as lof_oper_carr

,sum(lof_list.prorate_amt)

,sum(comm.base_comm_amt)

,sum(comm.override_comm_amt)

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

left join co_prod_vmdb.vw_agency_current_with_hol agy

on agy.agency_id = lof_list.tktng_agency_id

left join co_prod_vmdb.tcn_commission comm

on lof_list.record_id = comm.record_id

and lof_list.relative_tkt_seq = comm.relative_tkt_seq

and lof_list.coupon_num = comm.coupon_num

left join (

select

lof_list.initial_tkt_num

,trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) as od11

,cpn.dprt_dt as dprt11

,lof_list.oper_carr_cd as oper_carr11

,lof_list.mktg_carr_cd as mktg_carr11

,lof_list.tktng_cos as cos11

,lof_list.tkt_designator as des11

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

 

left join co_prod_vmdb.vw_tcn_coupon cpn

on lof_list.record_id = cpn.record_id

and lof_list.relative_tkt_seq = cpn.relative_tkt_seq

and lof_list.coupon_num = cpn.coupon_num

 

 

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num = 1 and lof_list.issued_dt between '?start_date' and '?end_date')cpn11 --enter date as yyyy-mm-dd

on cpn11.initial_tkt_num =lof_list.initial_tkt_num

left join (

select

lof_list.initial_tkt_num

,trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) as od12

,cpn.dprt_dt as dprt12

,lof_list.oper_carr_cd as oper_carr12

,lof_list.mktg_carr_cd as mktg_carr12

,lof_list.tktng_cos as cos12

,lof_list.tkt_designator as des12

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

left join co_prod_vmdb.vw_tcn_coupon cpn

on lof_list.record_id = cpn.record_id

and lof_list.relative_tkt_seq = cpn.relative_tkt_seq

and lof_list.coupon_num = cpn.coupon_num

 

 

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num = 2 and lof_list.issued_dt between '?start_date' and '?end_date')cpn12 --enter date as yyyy-mm-dd

on cpn12.initial_tkt_num =lof_list.initial_tkt_num

left join (

select

lof_list.initial_tkt_num

,trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) as od13

,cpn.dprt_dt as dprt13

,lof_list.oper_carr_cd as oper_carr13

,lof_list.mktg_carr_cd as mktg_carr13

,lof_list.tktng_cos as cos13

,lof_list.tkt_designator as des13

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

 

left join co_prod_vmdb.vw_tcn_coupon cpn

on lof_list.record_id = cpn.record_id

and lof_list.relative_tkt_seq = cpn.relative_tkt_seq

and lof_list.coupon_num = cpn.coupon_num

 

 

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num = 3 and lof_list.issued_dt between '?start_date' and '?end_date')cpn13 --enter date as yyyy-mm-dd

on cpn13.initial_tkt_num =lof_list.initial_tkt_num

left join (

select

lof_list.initial_tkt_num

,trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) as od14

,cpn.dprt_dt as dprt14

,lof_list.oper_carr_cd as oper_carr14

,lof_list.mktg_carr_cd as mktg_carr14

,lof_list.tktng_cos as cos14

,lof_list.tkt_designator as des14

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

left join co_prod_vmdb.vw_tcn_coupon cpn

on lof_list.record_id = cpn.record_id

and lof_list.relative_tkt_seq = cpn.relative_tkt_seq

and lof_list.coupon_num = cpn.coupon_num

 

 

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num = 4 and lof_list.issued_dt between '?start_date' and '?end_date')cpn14 --enter date as yyyy-mm-dd

on cpn14.initial_tkt_num =lof_list.initial_tkt_num

 

 

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.issued_dt between '?start_date' and '?end_date' --enter date as yyyy-mm-dd

 

group by

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17


Accepted Solutions
Senior Supporter

Re: Help*** Query performance - sub-queries vs. volatile tables

As @DaveWellman stated Explains and DDLs would help to support you. It is likely that there are stats missing.

The moment you use volatile tables you reduce the complexity of the SQL and you can control the PI of the VT which might help the optimizer to come up with a better plan.

 

Beside this I check the SQL

Am I right that the four left outer join sections are mainly the same, beside the lof_list.coupon_num condition.

In this case I would try to avoid the 4 left outer joins and would tryu to do an aggregation on one subquery like

select

lof_list.initial_tkt_num

,max(case when lof_list.coupon_num = 1 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od11
,max(case when lof_list.coupon_num = 1 then cpn.dprt_dt else null)  as dprt11
,max(case when lof_list.coupon_num = 1 then lof_list.oper_carr_cd else null)  as oper_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.mktg_carr_cd else null)  as mktg_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.tktng_cos as else null)  cos11
,max(case when lof_list.coupon_num = 1 then lof_list.tkt_designator else null)  as des11

,max(case when lof_list.coupon_num = 2 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od12
,max(case when lof_list.coupon_num = 2 then cpn.dprt_dt else null)  as dprt12
,max(case when lof_list.coupon_num = 2 then lof_list.oper_carr_cd else null)  as oper_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.mktg_carr_cd else null)  as mktg_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.tktng_cos as else null)  cos12
,max(case when lof_list.coupon_num = 2 then lof_list.tkt_designator else null)  as des12

,max(case when lof_list.coupon_num = 3 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od13
,max(case when lof_list.coupon_num = 3 then cpn.dprt_dt else null)  as dprt13
,max(case when lof_list.coupon_num = 3 then lof_list.oper_carr_cd else null)  as oper_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.mktg_carr_cd else null)  as mktg_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.tktng_cos as else null)  cos13
,max(case when lof_list.coupon_num = 3 then lof_list.tkt_designator else null)  as des13

,max(case when lof_list.coupon_num = 4 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od14
,max(case when lof_list.coupon_num = 4 then cpn.dprt_dt else null)  as dprt14
,max(case when lof_list.coupon_num = 4 then lof_list.oper_carr_cd else null)  as oper_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.mktg_carr_cd else null)  as mktg_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.tktng_cos as else null)  cos14
,max(case when lof_list.coupon_num = 4 then lof_list.tkt_designator else null)  as des14
 

from co_prod_vmdb.vw_tcn_all_lof lof_list
left join co_prod_vmdb.vw_tcn_coupon cpn
on lof_list.record_id = cpn.record_id
and lof_list.relative_tkt_seq = cpn.relative_tkt_seq
and lof_list.coupon_num = cpn.coupon_num

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num in (1,2,3,4) and lof_list.issued_dt between '?start_date' and '?end_date')cpn14
group by lof_list.initial_tkt_num

this should change the sql to

select

lof_list.issued_dt

,lof_list.pnr_create_dt

,lof_list.Initial_tkt_num

,lof_list.source_rcrd_loc

,lof.dist_channel

,lof_list.pax_name

,lof_list.tktng_agency_id

,agy.agency_name

,agy.hol_id

,agy.hol_agency_name

,lof_list.bkng_agency_id

,lof_list.tour_cd

,lof_list.exchgd_tkt_info

,coalesce(cpn14.od11,'')||','||coalesce(cpn14.od12,'')||','||coalesce(cpn14.od13,'')||','||coalesce(cpn14.od14,'')as lof_od

,coalesce(cpn14.cos11,'')||','||coalesce(cpn14.cos12,'')||','||coalesce(cpn14.cos13,'')||','||coalesce(cpn14.cos14,'')as lof_cos

,coalesce(cpn14.mktg_carr11,'')||','||coalesce(cpn14.mktg_carr12,'')||','||coalesce(cpn14.mktg_carr13,'')||','||coalesce(cpn14.mktg_carr14,'')as lof_mktg_carr

,coalesce(cpn14.oper_carr11,'')||','||coalesce(cpn14.oper_carr12,'')||','||coalesce(cpn14.oper_carr13,'')||','||coalesce(cpn14.oper_carr14,'')as lof_oper_carr

,sum(lof_list.prorate_amt)

,sum(comm.base_comm_amt)

,sum(comm.override_comm_amt)

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

left join co_prod_vmdb.vw_agency_current_with_hol agy

on agy.agency_id = lof_list.tktng_agency_id

left join co_prod_vmdb.tcn_commission comm

on lof_list.record_id = comm.record_id

and lof_list.relative_tkt_seq = comm.relative_tkt_seq

and lof_list.coupon_num = comm.coupon_num

left join (

select

lof_list.initial_tkt_num

,max(case when lof_list.coupon_num = 1 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od11
,max(case when lof_list.coupon_num = 1 then cpn.dprt_dt else null)  as dprt11
,max(case when lof_list.coupon_num = 1 then lof_list.oper_carr_cd else null)  as oper_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.mktg_carr_cd else null)  as mktg_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.tktng_cos as else null)  cos11
,max(case when lof_list.coupon_num = 1 then lof_list.tkt_designator else null)  as des11

,max(case when lof_list.coupon_num = 2 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od12
,max(case when lof_list.coupon_num = 2 then cpn.dprt_dt else null)  as dprt12
,max(case when lof_list.coupon_num = 2 then lof_list.oper_carr_cd else null)  as oper_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.mktg_carr_cd else null)  as mktg_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.tktng_cos as else null)  cos12
,max(case when lof_list.coupon_num = 2 then lof_list.tkt_designator else null)  as des12

,max(case when lof_list.coupon_num = 3 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od13
,max(case when lof_list.coupon_num = 3 then cpn.dprt_dt else null)  as dprt13
,max(case when lof_list.coupon_num = 3 then lof_list.oper_carr_cd else null)  as oper_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.mktg_carr_cd else null)  as mktg_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.tktng_cos as else null)  cos13
,max(case when lof_list.coupon_num = 3 then lof_list.tkt_designator else null)  as des13

,max(case when lof_list.coupon_num = 4 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od14
,max(case when lof_list.coupon_num = 4 then cpn.dprt_dt else null)  as dprt14
,max(case when lof_list.coupon_num = 4 then lof_list.oper_carr_cd else null)  as oper_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.mktg_carr_cd else null)  as mktg_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.tktng_cos as else null)  cos14
,max(case when lof_list.coupon_num = 4 then lof_list.tkt_designator else null)  as des14
 

from co_prod_vmdb.vw_tcn_all_lof lof_list
left join co_prod_vmdb.vw_tcn_coupon cpn
on lof_list.record_id = cpn.record_id
and lof_list.relative_tkt_seq = cpn.relative_tkt_seq
and lof_list.coupon_num = cpn.coupon_num

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num in (1,2,3,4) and lof_list.issued_dt between '?start_date' and '?end_date'

group by lof_list.initial_tkt_num

)cpn14


on cpn14.initial_tkt_num =lof_list.initial_tkt_num


where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.issued_dt between '?start_date' and '?end_date' --enter date as yyyy-mm-dd

 

group by

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17

Ulrich

1 ACCEPTED SOLUTION
3 REPLIES
Senior Apprentice

Re: Help*** Query performance - sub-queries vs. volatile tables

Hi Fabiano,

It might help if you could provide the explain plan for this query.

Also, given that he VT approach ran much quicker (I realise you can't use it in those products) it might be useful if you could show that approach as well. It could help others to identify the major differences between the two approaches.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Supporter

Re: Help*** Query performance - sub-queries vs. volatile tables

As @DaveWellman stated Explains and DDLs would help to support you. It is likely that there are stats missing.

The moment you use volatile tables you reduce the complexity of the SQL and you can control the PI of the VT which might help the optimizer to come up with a better plan.

 

Beside this I check the SQL

Am I right that the four left outer join sections are mainly the same, beside the lof_list.coupon_num condition.

In this case I would try to avoid the 4 left outer joins and would tryu to do an aggregation on one subquery like

select

lof_list.initial_tkt_num

,max(case when lof_list.coupon_num = 1 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od11
,max(case when lof_list.coupon_num = 1 then cpn.dprt_dt else null)  as dprt11
,max(case when lof_list.coupon_num = 1 then lof_list.oper_carr_cd else null)  as oper_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.mktg_carr_cd else null)  as mktg_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.tktng_cos as else null)  cos11
,max(case when lof_list.coupon_num = 1 then lof_list.tkt_designator else null)  as des11

,max(case when lof_list.coupon_num = 2 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od12
,max(case when lof_list.coupon_num = 2 then cpn.dprt_dt else null)  as dprt12
,max(case when lof_list.coupon_num = 2 then lof_list.oper_carr_cd else null)  as oper_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.mktg_carr_cd else null)  as mktg_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.tktng_cos as else null)  cos12
,max(case when lof_list.coupon_num = 2 then lof_list.tkt_designator else null)  as des12

,max(case when lof_list.coupon_num = 3 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od13
,max(case when lof_list.coupon_num = 3 then cpn.dprt_dt else null)  as dprt13
,max(case when lof_list.coupon_num = 3 then lof_list.oper_carr_cd else null)  as oper_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.mktg_carr_cd else null)  as mktg_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.tktng_cos as else null)  cos13
,max(case when lof_list.coupon_num = 3 then lof_list.tkt_designator else null)  as des13

,max(case when lof_list.coupon_num = 4 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od14
,max(case when lof_list.coupon_num = 4 then cpn.dprt_dt else null)  as dprt14
,max(case when lof_list.coupon_num = 4 then lof_list.oper_carr_cd else null)  as oper_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.mktg_carr_cd else null)  as mktg_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.tktng_cos as else null)  cos14
,max(case when lof_list.coupon_num = 4 then lof_list.tkt_designator else null)  as des14
 

from co_prod_vmdb.vw_tcn_all_lof lof_list
left join co_prod_vmdb.vw_tcn_coupon cpn
on lof_list.record_id = cpn.record_id
and lof_list.relative_tkt_seq = cpn.relative_tkt_seq
and lof_list.coupon_num = cpn.coupon_num

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num in (1,2,3,4) and lof_list.issued_dt between '?start_date' and '?end_date')cpn14
group by lof_list.initial_tkt_num

this should change the sql to

select

lof_list.issued_dt

,lof_list.pnr_create_dt

,lof_list.Initial_tkt_num

,lof_list.source_rcrd_loc

,lof.dist_channel

,lof_list.pax_name

,lof_list.tktng_agency_id

,agy.agency_name

,agy.hol_id

,agy.hol_agency_name

,lof_list.bkng_agency_id

,lof_list.tour_cd

,lof_list.exchgd_tkt_info

,coalesce(cpn14.od11,'')||','||coalesce(cpn14.od12,'')||','||coalesce(cpn14.od13,'')||','||coalesce(cpn14.od14,'')as lof_od

,coalesce(cpn14.cos11,'')||','||coalesce(cpn14.cos12,'')||','||coalesce(cpn14.cos13,'')||','||coalesce(cpn14.cos14,'')as lof_cos

,coalesce(cpn14.mktg_carr11,'')||','||coalesce(cpn14.mktg_carr12,'')||','||coalesce(cpn14.mktg_carr13,'')||','||coalesce(cpn14.mktg_carr14,'')as lof_mktg_carr

,coalesce(cpn14.oper_carr11,'')||','||coalesce(cpn14.oper_carr12,'')||','||coalesce(cpn14.oper_carr13,'')||','||coalesce(cpn14.oper_carr14,'')as lof_oper_carr

,sum(lof_list.prorate_amt)

,sum(comm.base_comm_amt)

,sum(comm.override_comm_amt)

 

from co_prod_vmdb.vw_tcn_all_lof lof_list

left join co_prod_vmdb.vw_agency_current_with_hol agy

on agy.agency_id = lof_list.tktng_agency_id

left join co_prod_vmdb.tcn_commission comm

on lof_list.record_id = comm.record_id

and lof_list.relative_tkt_seq = comm.relative_tkt_seq

and lof_list.coupon_num = comm.coupon_num

left join (

select

lof_list.initial_tkt_num

,max(case when lof_list.coupon_num = 1 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od11
,max(case when lof_list.coupon_num = 1 then cpn.dprt_dt else null)  as dprt11
,max(case when lof_list.coupon_num = 1 then lof_list.oper_carr_cd else null)  as oper_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.mktg_carr_cd else null)  as mktg_carr11
,max(case when lof_list.coupon_num = 1 then lof_list.tktng_cos as else null)  cos11
,max(case when lof_list.coupon_num = 1 then lof_list.tkt_designator else null)  as des11

,max(case when lof_list.coupon_num = 2 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od12
,max(case when lof_list.coupon_num = 2 then cpn.dprt_dt else null)  as dprt12
,max(case when lof_list.coupon_num = 2 then lof_list.oper_carr_cd else null)  as oper_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.mktg_carr_cd else null)  as mktg_carr12
,max(case when lof_list.coupon_num = 2 then lof_list.tktng_cos as else null)  cos12
,max(case when lof_list.coupon_num = 2 then lof_list.tkt_designator else null)  as des12

,max(case when lof_list.coupon_num = 3 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od13
,max(case when lof_list.coupon_num = 3 then cpn.dprt_dt else null)  as dprt13
,max(case when lof_list.coupon_num = 3 then lof_list.oper_carr_cd else null)  as oper_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.mktg_carr_cd else null)  as mktg_carr13
,max(case when lof_list.coupon_num = 3 then lof_list.tktng_cos as else null)  cos13
,max(case when lof_list.coupon_num = 3 then lof_list.tkt_designator else null)  as des13

,max(case when lof_list.coupon_num = 4 then trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) else null) as od14
,max(case when lof_list.coupon_num = 4 then cpn.dprt_dt else null)  as dprt14
,max(case when lof_list.coupon_num = 4 then lof_list.oper_carr_cd else null)  as oper_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.mktg_carr_cd else null)  as mktg_carr14
,max(case when lof_list.coupon_num = 4 then lof_list.tktng_cos as else null)  cos14
,max(case when lof_list.coupon_num = 4 then lof_list.tkt_designator else null)  as des14
 

from co_prod_vmdb.vw_tcn_all_lof lof_list
left join co_prod_vmdb.vw_tcn_coupon cpn
on lof_list.record_id = cpn.record_id
and lof_list.relative_tkt_seq = cpn.relative_tkt_seq
and lof_list.coupon_num = cpn.coupon_num

where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num in (1,2,3,4) and lof_list.issued_dt between '?start_date' and '?end_date'

group by lof_list.initial_tkt_num

)cpn14


on cpn14.initial_tkt_num =lof_list.initial_tkt_num


where

lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.issued_dt between '?start_date' and '?end_date' --enter date as yyyy-mm-dd

 

group by

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17

Ulrich

Enthusiast

Re: Help*** Query performance - sub-queries vs. volatile tables

Thank you so much Ulrich. Performance is much better now.