Sql Query performance issue

Here is a fun one for a Friday afternoon.

I am building a procedure for a custom report and the code below is the meat and potatoes of it.

Unfortunately this part of the code is really slow and I can't figure out a better way to optimize it. I tried indexes on sli_no,perf_no and price category id. I created the indexes before populating the #sales table, I tried after populating.  I am beginning to wonder if I need to break up the #sales insert into smaller chunks or maybe there is a better join I can do.

Any ideas?

Thanks,

Marty

--Start Code

IF OBJECT_ID('tempdb..#perfs') IS NOT Null
 DROP TABLE #perfs

   Insert into #perfs
   Select
    Perf_no,season
   from
    t_perf p Join
   WHERE p.season = 57

IF OBJECT_ID('tempdb..#Mos_Category') IS NOT Null
 DROP TABLE #Mos_Category

Create table #Mos_Category (id int)

INSERT INTO #Mos_Category (id)
values (2)

INSERT INTO #Mos_Category (id)
values (3)

IF OBJECT_ID('tempdb..#Sales') IS NOT Null
 DROP TABLE #Sales

Create Table #Sales (
 Order_no int Null,
 Order_Paid_Status char(3) Default '***',
 Ticket_Paid_Status char(3) Default '***',
 Sli_no int,
 sli_status int null,
 Perf_no int Null,
 Seated char(1) Default '*',--'N',
 Paid_Status char(3) Default '***', -- 'UPD',
 Returned char(1) Default 'N',
 Returned_Amt Money Default 0,
 Donated char(1) Default 'N',
 Due_Amt Money Default 0,
 Paid_Amt Money Default 0,
 Price_Type_Id int,
 Price_Type_Category_Id int,
 Price_Category_Id int,
 Season_Id Int,
 Zone_no INT,
 Mos_Category INT,
 PRIMARY KEY (sli_no,Price_Category_Id)
)

 CREATE INDEX IX_Sales_Price_Category
    ON #Sales (Price_Category_Id);

 CREATE INDEX IX_Sales__Perf_no
    ON #Sales (Perf_no);

/*
*********************************************************************************
Populate the #Sales with only the Relevant Data
*********************************************************************************
*/

Insert #Sales (
 Order_no,
 Sli_no,
 sli_status,
 Perf_no,
 Price_Type_Id,
 Price_Type_Category_Id,
 Season_Id,
 Zone_no,
 price_category_id,
 Seated,
 Paid_Amt,
 Due_Amt,
 Paid_Status,
 Order_Paid_Status,
 Ticket_Paid_Status,
 Mos_Category
 )
Select Distinct
 sl.Order_no,
 sl.Sli_No,
 sl.sli_status,
 sl.Perf_no,
 pt.id,
 pt.price_type_category,
 tp.season,
 sl.Zone_no,
 pm.price_category,
 Seated = (Case when Max(sl.seat_no) > 0 then 'Y' else 'N' end),
 Sum(Sd.Paid_Amt) as Paid,
 Sum(Sd.Due_Amt) as Due,
 Paid_Status =                          --SLI_Detail level
  Case
   When Sum(Sd.Paid_Amt) = 0 and Sum(Sd.Due_Amt) > 0 and pt.Price_Type_Category <> 4 then 'UPD'
   When Sum(Sd.Paid_Amt) > 0 and Sum(Sd.Paid_Amt) <> Sum(Sd.Due_Amt) then 'PPD'
   When (Sum(Sd.Paid_Amt) > 0 and Sum(Sd.Paid_Amt) = Sum(Sd.Due_Amt)) or pt.Price_Type_Category = 4 then 'FPD'
   When Sum(Sd.Paid_Amt) = 0 and Sum(Sd.Due_Amt) = 0 and pt.Price_Type_Category <> 4 then 'FPD' --Free Tickets non-comp price type category???
  End,
 Order_Paid_Status =                    --Total order status
        Case
            when (Sum(o.tot_due_amt) <> Sum(o.tot_paid_amt)) and Sum(o.tot_paid_amt) > 0 then 'PPD'
            when (Sum(o.tot_due_amt) = Sum(o.tot_paid_amt)) then 'FPD'
            when Sum(o.tot_due_amt) > 0 and Sum(o.tot_paid_amt) = 0 then 'UPD'
            End,
 Ticket_Paid_Status =                   --Total Ticket status at the order level
        Case
            when (Sum(o.tot_ticket_purch_amt  + o.tot_ticket_return_amt) <> Sum(o.tot_ticket_paid_amt)) and Sum(o.tot_ticket_paid_amt) > 0 then 'PPD'
            when (Sum(o.tot_ticket_purch_amt + o.tot_ticket_return_amt) = Sum(o.tot_ticket_paid_amt)) then 'FPD'
            when Sum(o.tot_ticket_purch_amt + o.tot_ticket_return_amt) > 0 and Sum(o.tot_ticket_paid_amt) = 0 then 'UPD'
            END,
 mos_category = m.category
From
 t_order o With(nolock) JOIN
 TR_MOS m ON m.id = o.MOS JOIN
 #Mos_Category mm ON mm.id = m.category JOIN
 t_lineitem l With(nolock) on l.order_no = o.order_no Join
 #perfs tp on tp.perf_no = l.perf_no Join
 T_Sub_Lineitem sl With(nolock) on l.li_seq_no = sl.li_seq_no and sl.perf_no = l.perf_no join
 t_sli_detail sd With(nolock) on sd.sli_no = sl.sli_no Join
 TR_PRICE_TYPE pt With(nolock) on pt.id = sl.price_type Join
 t_pmap pm With(nolock) on pm.pmap_no = sd.pmap_no
where
 sl.Sli_Status not in (4,13)
 and sl.zone_no > 0 -- **Business Rule** All sub lineitems must have a zone.
 And l.primary_ind = 'Y'
Group by
 sl.Order_no,
 sl.Sli_No,
 sl.sli_status,
 sl.Perf_no,
 pt.id,
 pt.price_type_category,
 tp.season,
 sl.Zone_no,
 pm.price_category,
 m.category

/*
*********************************************************************************
Determine if a Sli_no is returned
and update the #Sales Table returned amount and returned status column

*********************************************************************************
*/
Update
 #Sales
Set Returned_Amt = aaa.paid_amt,
 Returned = 'Y'
From
(
Select
 s.sli_no,
 pm.price_category,
 Sum(sd.paid_amt) as Paid_Amt
From
 #Sales s Join
 t_Sub_lineitem sl With(nolock) on  s.sli_no = sl.ret_parent_sli_no and sl.perf_no = s.perf_no JOin -- get the returned sub_lineitem
 t_sli_detail sd With(nolock) on sd.sli_no = sl.sli_no Join -- get the detail rows using that sub_lineitem
 t_pmap pm With(nolock) on pm.pmap_no = sd.pmap_no and pm.price_category = s.price_category_id
Where
 s.sli_status in (7,8) and sl.sli_status = 4
Group by
 s.sli_no,
 pm.price_category
) as aaa
Where
 aaa.sli_no = #Sales.Sli_no and aaa.price_category = #Sales.price_category_id

--===============================================================================

/*
*********************************************************************************
Determine if a Sli_no is Donated
and update the #Sales Table donated status column

*********************************************************************************
*/

Update
 #Sales
Set
 Donated = 'Y'
From
 #Sales s Join
 t_Sub_lineitem sl With(nolock) on  s.sli_no = sl.ret_parent_sli_no
Where
 s.sli_status in (7,8) and sl.sli_status = 13

-- End Code