SQL Performance for an 'in' statement. 3

[benmillus]

Any tips on how to make this query run faster? This is the part that really slows it down:

"o.pr_dept_ou_id in
(
select c.pr_dept_ou_id
from s_org_ext oe, s_contact c
where oe.x_name = ' 1234' and c.last_name = '1234'
)"

select ord.order_num,
ordt.name,
orgint.name,
prodint.name,
oi.row_id,
oi.alw_part_ship_flg,
oi.billable_flg,
oi.ln_num,
oi.loaner_flg,
oi.rollup_flg,
oi.wrnty_rcvr_flg,
oi.discnt_src_cd,
oi.excl_pricing_flg,
oi.hold_flg,
oi.promo_item_flg,
oi.auto_recv_flg,
oi.processed_flg,
oi.ship_complete_flg,
oi.ship_together_flg,
oi.single_src_flg,
ord.rev_num
from s_opty o,
s_order ord,
s_order_type ordt,
s_prod_int prodint,
s_bu orgint,
s_order_item oi
where o.pr_dept_ou_id in
(
select c.pr_dept_ou_id
from s_org_ext oe, s_contact c
where oe.x_name = ' 1234' and c.last_name = '1234'
)
and o.row_id = ord.opty_id
and ord.row_id = oi.order_id
and ord.order_type_id = ordt.row_id
and oi.prod_id = prodint.row_id
and o.bu_id = orgint.row_id
and o.created < sysdate - 1

 

[RobertT687]

An index on o.pr_dept_ou_id will probably help.

Also look at the query execution plan for
(select c.pr_dept_ou_id
from s_org_ext oe, s_contact c
where oe.x_name = ' 1234'
and c.last_name = '1234').
That may reveal other bottlenecks.

 

[SQLSister]

Well in trying to change your syntax to specify the joins so that I could understand what it was doing and change the where in to a derived table (which is more efficent), I discoved that you appear to have a cross join. Did you really want that or did you forget to describe one of the relationships? There is a reason why the syntax to descibe the joins you used is not the recommended syntax and that is because it is easy to get a cross join without intending to. I believe this is especially true when you have a complex series of joins like this. Cross joins are notoriouslly ineffcient unless very few records are involved so this may be the root cause of your problem.

My code below shows the join syntax I got to work, but I am not at all clear if it is the joining that you want.

select  ord.order_num,
        ordt.name,
        orgint.name,
        prodint.name,
        oi.row_id,
        oi.alw_part_ship_flg,
        oi.billable_flg,
        oi.ln_num,
        oi.loaner_flg,
        oi.rollup_flg,
        oi.wrnty_rcvr_flg,
        oi.discnt_src_cd,
        oi.excl_pricing_flg,
        oi.hold_flg,
        oi.promo_item_flg,
        oi.auto_recv_flg,
        oi.processed_flg,
        oi.ship_complete_flg,
        oi.ship_together_flg,
        oi.single_src_flg,
        ord.rev_num
from    s_opty o join s_order ord on o.row_id = ord.opty_id
        Join s_order_type ordt on ord.order_type_id = ordt.row_id
		cross join s_order_item oi        
		join s_prod_int prodint on  oi.prod_id = prodint.row_id
        Join s_bu orgint on o.bu_id = orgint.row_id
        Join
		(select c.pr_dept_ou_id as  pr_dept_ou_id
        from s_org_ext oe join s_contact c
		on oe.IDField = c.IDFIeld
        where oe.x_name = ' 1234' and c.last_name = '1234') a 
		on  o.pr_dept_ou_id = a.pr_dept_ou_id   
where   o.created < sysdate - 1

Questions about posting. See faq183-874

 

[vongrunt]

Dunno about cross join... table s_order_item is referenced twice:

and ord.row_id = oi.order_id
and oi.prod_id = prodint.row_id

 

[SQLSister]

Ok could be I got lost trying to translate into join syntax.

Questions about posting. See faq183-874

 

[benmillus]

Many thanks, to all, for taking the time to add your comments. I was able to use your observations to achieve sub-second query completion time.

benmillus