How to tune the following sql in order that the performance improve the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'AND A.POSTED_FLAG = 'N'AND ERROR_FLAG = ' '

peoplesoft
Profile Answers by peoplesoft Questions by peoplesoft
Oct 3rd, 2006
5
70476

Showing Answers 1 - 5 of 5 Answers

ARUN KUMAR

Oct 4th, 2006

In this qry we can create an index on the base conditions to improve the qry performance.

Vasanth Kumar

Oct 6th, 2006

1) Creating index for tuning is one step.2) You can remove Union and replace it with Union ALL.Union ALL is much faster than Union.Since you already have distinct clause in your SQL query you can very well remove UNION and replace it with UNION ALL.3) Check whether it would be possible for you to combine this to one single query.4) Check the Query plan.The above are some of the tips to optimise the query.

zskhan
Profile Answers by zskhan

Oct 12th, 2006

Remove DISTINCT from both queries.
Since UNION is being used, we are going to get distinct rows anyway and queries with distinct are quite expensive.

raji_4u
Profile Answers by raji_4u

Sep 19th, 2008

Remove union and place union all

Make sure the where conditions is in order of
1. =
2. >
3. <>
4. like.

Make sure that the condition that returns least records comes first.