SQL Server Performance

Query Performance.....!

Discussion in 'Performance Tuning for DBAs' started by krajdba, Mar 7, 2005.

  1. krajdba New Member

    Hi Gurus,

    TO Avoid NOT IN and IN Operator, I did this But according to Query Execution
    Plan the Former(Query Cost=33.06%) proves to be Best when compared to the
    Latter(Query Cost=66.94%). Why ....?

    Query 1

    SELECT ISNULL(SUM(PSD.Billed_Units * PSD.Unit_Charge + PSD.Billed_OTAmt),0)
    FROM Payment_Schedule PS,Payment_Schedule_Detail PSD,Invoice I
    WHERE PS.Status_id not in(SELECT Valid_value_id FROM Valid_values vv,Data_items di
    where di.data_item_name = 'Payment_Status' AND Programmer_use1='Cancelled'AND vv.data_item_id=di.data_item_id)
    AND PS.Payment_number=PSD.Payment_number AND PS.Invoice_number=I.Invoice_number
    AND I.Status_id in(SELECT Valid_value_id FROM Valid_values vv,Data_items di where
    di.data_item_name = 'Invoice_Status' AND Programmer_use1 in ('Approved','Paid','Overdue')
    AND vv.data_item_id=di.data_item_id)
    AND PS.vendor_id=1
    GROUP BY PS.vendor_id

    Query 2

    SELECT ISNULL(SUM(PSD.Billed_Units * PSD.Unit_Charge + PSD.Billed_OTAmt),0)
    FROM Payment_Schedule PS Inner loop Join Payment_Schedule_Detail PSD ON PS.Payment_number=PSD.Payment_number
    Inner loop Join Invoice I ON PS.Invoice_number=I.Invoice_number
    Inner loop Join (SELECT Valid_value_id FROM Valid_values vv,Data_items di where di.data_item_name = 'Invoice_Status' AND
    (Programmer_use1='Approved' OR Programmer_use1='Paid' OR Programmer_use1='Overdue') AND vv.data_item_id=di.data_item_id) A
    ON I.Status_id=A.Valid_value_id
    Inner Join (SELECT Valid_value_id FROM Valid_values vv,Data_items di where
    di.data_item_name = 'Payment_Status' AND Programmer_use1='Cancelled'AND vv.data_item_id=di.data_item_id) As B
    ON PS.Status_id<>B.Valid_value_id
    WHERE PS.vendor_id=1
    GROUP BY PS.vendor_id

    What changes I can make to the Query 2 to Increase the Performance.
    Please suggest.

    Urgent Please.

    Thanks



    raj
  2. mmarovic Active Member

    quote:
    Inner Join (SELECT Valid_value_id FROM Valid_values vv,Data_items di where
    di.data_item_name = 'Payment_Status' AND Programmer_use1='Cancelled'AND vv.data_item_id=di.data_item_id) As B
    ON PS.Status_id<>B.Valid_value_id
    That part is problematic. Don't use "<>" operator as the only join condition. Better keep original query or use left outer join on ps.Status_id = b.Valid_value_id and add
    B.Valid_value_id is null
    in where clause.
  3. krajdba New Member

    Hi,

    Thanks Marovic.

    The Left Outer worked.

    Thanks


    raj
  4. mmarovic Active Member

    You are welcome, it is really nice to have feedback <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page