Query Executing Slow

[akumar.ana06]

Hi. Hope everyone doing great.

When i run SELECT 'X' FROM PS_INSTALLATION this it's taking no time, and when i run the inner query it's taking 3 sec. But when i run the compete query it's taking 2 mins. This is system generated built-in query i can't change the query. Execution plans for the query and inner query are different.

CODE:

SELECT 'X' FROM PS_INSTALLATION WHERE EXISTS
(SELECT 'X' FROM PS_KK_SOURCE_LN A, PS_KK_SOURCE_HDR B , PS_KK_ACTIVITY_LOG C
WHERE B.BUSINESS_UNIT = 'BUS' AND B.PO_ID = 'PO_NUM' AND A.LINE_NBR = '1' AND A.SCHED_NBR = '1'
AND A.DISTRIB_LINE_NUM = '1' AND A.KK_TRAN_ID = B.KK_TRAN_ID AND A.KK_TRAN_DT = B.KK_TRAN_DT
AND A.KK_TRAN_ID = C.KK_TRAN_ID AND A.KK_TRAN_DT = C.KK_TRAN_DT AND A.KK_TRAN_LN = C.KK_TRAN_LN
AND C.ENTRY_EVENT <> ' ' AND C.EE_PROC_STATUS IN ('N', ' '))

Any help would be greatly appreciated!

Thanks,
Best Regards,
Kumar.

 

[oumahexi]

Hi. Hope everyone doing great.

When i run SELECT 'X' FROM PS_INSTALLATION this it's taking no time, and when i run the inner query it's taking 3 sec. But when i run the compete query it's taking 2 mins. This is system generated built-in query i can't change the query. Execution plans for the query and inner query are different.

CODE:

SELECT 'X' FROM PS_INSTALLATION WHERE EXISTS
(SELECT 'X' FROM PS_KK_SOURCE_LN A, PS_KK_SOURCE_HDR B , PS_KK_ACTIVITY_LOG C
WHERE B.BUSINESS_UNIT = 'BUS'
AND B.PO_ID = 'PO_NUM'
AND A.LINE_NBR = '1'
AND A.SCHED_NBR = '1'
AND A.DISTRIB_LINE_NUM = '1'
AND A.KK_TRAN_ID = B.KK_TRAN_ID
AND A.KK_TRAN_DT = B.KK_TRAN_DT
AND A.KK_TRAN_ID = C.KK_TRAN_ID
AND A.KK_TRAN_DT = C.KK_TRAN_DT
AND A.KK_TRAN_LN = C.KK_TRAN_LN
AND C.ENTRY_EVENT <> ' '
AND C.EE_PROC_STATUS IN ('N', ' '))

Any help would be greatly appreciated!

Thanks,
Best Regards,
Kumar.

I think the number of conditions is slowing it down. Also, I think the second last row is confusing, perhaps what you mean is C.ENTRY_EVENT IS NULL?

 

[JamesMcS]

I depends on a few things I think - if your conditions are based upon non-indexed text fields for example, it'll run more slowly than a number field that's the primary key.