Query Performance.....!

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

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.

Hi,

Thanks Marovic.

The Left Outer worked.

Thanks


raj

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