combining queries runs longer

Question

I have 2 queries. Individually, both run well. How when I combine them, they run terribly slow and time out on me. The second query is returning a list of invoices that I want to use in the IN clause in the first query. Not sure if I'm doing something wrong. Thanks for your help....

select 
ba.bill_acct_nbr,
ba.acct_name,
i.inv_id,
i.prnt_tmsp,
i.due_dt,
d.dvr_frst_name,
d.dvr_srnm,
r.ecr_ticket_no,
r.co_tmsp,
i.dr_note_amt ,
ht.amt HT,
vat.amt VAT

from rfs.rnt_agr_inv_notes i

    inner join rfs.rnt_Agrs r on  r.rnt_agr_nbr = i.rea_rnt_agr_nbr
    inner join rfs_rv.dvr_rras d on r.rnt_agr_nbr = d.rdy_rnt_agr_nbr and d.main_dvr_flg = 'MR'
    inner join rfs_rv.bus_acnts ba on ba.acct_id = i.bac_acc_id

    inner join (select sum(chg_amt) amt,  rain.inv_id
                    from rfs.rra_chgs rrc 
                    inner join rfs.rnt_agr_inv_note_lns rainl on rrc.rrc_id=rainl.rrc_rrc_id
                    inner join rfs.rnt_agr_inv_notes rain on rainl.rai_inv_id=rain.inv_id 
                    inner join rfs.rnt_agrs ra on rain.rea_rnt_agr_nbr=ra.rnt_agr_nbr
                    inner join rfs.rra_chg_typs rct on rrc.rct_chg_typ=rct.chg_typ 

                    where rrc.rct_chg_typ not in ('TAX', 'SUR', 'VAT') and not ( ra.stn_system='ECR' and (rrc.rct_chg_typ ='02000' or rrc.rct_chg_typ ='02201'))
                    group by  rain.inv_id) ht on ht.inv_id=i.inv_id  

    inner join (select sum(chg_amt) amt,  rain.inv_id
                    from rfs.rra_chgs rrc 
                    inner join rfs.rnt_agr_inv_note_lns rainl on rrc.rrc_id=rainl.rrc_rrc_id
                    inner join rfs.rnt_agr_inv_notes rain on rainl.rai_inv_id=rain.inv_id
                    inner join rfs.rnt_agrs ra on rain.rea_rnt_agr_nbr=ra.rnt_agr_nbr
                    inner join rfs.rra_chg_typs rct on rrc.rct_chg_typ=rct.chg_typ and not ( ra.stn_system='ECR' and  rrc.rct_chg_typ ='02200') 

                    where rrc.rct_chg_typ in ('TAX', 'SUR', 'VAT')
                    group by  rain.inv_id) vat on vat.inv_id=i.inv_id
where                               
i.inv_id in (425001975206,550008226812,425002005105, 425002046396, 42500190929)

I commented out the last line above and replaced it with this code; which runs well by itself.

 i.inv_id in (select    
        q.inv_id
        from
        rfs.rnt_agr_inv_notes q, 
        rfs_rv.bus_acnts ba

    where   
        ba.acct_id = q.bac_acc_id
        and ba.bill_acct_nbr IN ('16785616')
        AND extract(MONTH from q.prnt_tmsp)  = 5
        AND extract(YEAR from q.prnt_tmsp)  = 2015)

When you Explain the 2nd query what's the estimated number of rows? If this is the actual query it seems like you don't need that subquery at all. Simply move the WHERE-condition to the outer query. `where ba.acct_id = i.bac_acc_id and ba.bill_acct_nbr IN ('16785616') AND extract(MONTH from i.prnt_tmsp) = 5 AND extract(YEAR from i.prnt_tmsp) = 2015)` — dnoeth, May 22 '15 at 14:08
Sub-queries can be quite expensive, especially if the number of rows you have is large. Is there a way for you to try to write the query to get the same results without using sub-queries? — Muhammad Abdul-Rahim, May 22 '15 at 14:09
Additionally the `EXTRACTs` might lead to wrong estimates, `i.prnt_tmsp >= timestamp '2015-05-01 00:00:00' and `i.prnt_tmsp < timestamp '2015-06-01 00:00:00' might be more efficient. — dnoeth, May 22 '15 at 14:11
I think adding the same where to the subqueries like so where inv_id in (425001975206,550008226812,425002005105, 425002046396, 42500190929) will speed it up somewhat — verhie, May 22 '15 at 14:16
@dnoeth......The number of rows in the second query is going to depend on the customer number. (bill_acct_nbr). In this case 05 /2015, there are only 5 invoice numbers. For 04/2015, there were 10 invoice numbers. Other customer numbers could be a lot more. — Shaves, May 22 '15 at 14:53
@Mari M.........So far the number of rows in the subquery are relatively small. The ones I have worked with so far are 5 -10. My guess is you could maybe have something less than 200 but I'm not sure if it could be more than that — Shaves, May 22 '15 at 14:55
@dnoeth..............the reason we use the EXTRACTs is that we only want invoice dates (in this case) with a Month of 5 and a year of 2015. I'll look into changing it. The only thing that I'm not sure about is how to write this if the month is 12. Normally, I would add 1 to the month but when the month is 12 than I would have to change the second month to 1 and the year to + 1. It's a little advanced for my skills at this time — Shaves, May 22 '15 at 15:39

score 0 · Answer 1 · answered May 22 '15 at 14:15

0

I can give you a solution for combining the two which has worked well for our product. It looks horrible but had a vastly huge performance improvement by generating the list of Ids up front in a temp table.

(As an aside, you really need to take a look at the huge amount of INNER JOIN in your query as this is not helping your performance).

You'd want something like this:

CREATE TABLE #invIds (id INT)
INSERT INTO #invIds (id) SELECT 
    q.inv_id
    from
    rfs.rnt_agr_inv_notes q, 
    rfs_rv.bus_acnts ba

where   
    ba.acct_id = q.bac_acc_id
    and ba.bill_acct_nbr IN ('16785616')
    AND extract(MONTH from q.prnt_tmsp)  = 5
    AND extract(YEAR from q.prnt_tmsp)  = 2015

Then the query would do this

d.dvr_frst_name,
d.dvr_srnm,
r.ecr_ticket_no,
r.co_tmsp,
i.dr_note_amt ,
ht.amt HT,
vat.amt VAT

from rfs.rnt_agr_inv_notes I .....

.....where                               
i.inv_id in #invIds

answered May 22 '15 at 14:15

Ilessa

602
8
27

@llessa..................Thanks..........I've used a derived table before. Is this kind of the same thing? I think I only have read access so I'm not sure I can create a temp table but I'm going to give it a try. Thanks again – Shaves May 22 '15 at 16:02
@Shaves........The first answer [here](http://stackoverflow.com/questions/2326395/) explains the difference quite well. Because your Ids are fixed and won't change, a temp table would give you better performance as you won't be trying to get the list of Ids every time, but instead searching against the list of Ids which is already there. – Ilessa May 26 '15 at 09:37
@illessa.............I've tried to create the table but don't have the security to do so; which is why I've used a derived table in the past. I'm an accountant and am getting more into a report role; so I'm relatively new at this Thanks for your help.... – Shaves May 26 '15 at 15:07

combining queries runs longer

1 Answers1