Query Executing Slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Query Executing Slow

Author

Topic

akumar.ana06
Starting Member

4 Posts

Posted - 2010-06-03 : 12:03:47

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 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.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-03 : 12:51:57

1. There does not seem to be anything to link PS_INSTALLATION to the subquery - I suspect there should be.
2. JOINs in subqueries tend to be nested loops, so get rid of them.


SELECT 'X' 
FROM PS_INSTALLATION
WHERE EXISTS 
(
	SELECT *
	FROM PS_KK_SOURCE_LN A
	WHERE A.<something> = PS_INSTALLATION.<something>
		AND A.LINE_NBR = '1'
		AND A.SCHED_NBR = '1' 
		AND A.DISTRIB_LINE_NUM = '1'
		AND EXISTS
		(
			SELECT *
			FROM PS_KK_SOURCE_HDR B
			WHERE A.KK_TRAN_ID = B.KK_TRAN_ID
				AND A.KK_TRAN_DT = B.KK_TRAN_DT
				AND B.BUSINESS_UNIT = 'BUS'
				AND B.PO_ID = 'PO_NUM'
		)
		AND EXISTS
		(
			SELECT *
			FROM PS_KK_ACTIVITY_LOG C
			WHERE 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', ' ')
		)
)

akumar.ana06
Starting Member

4 Posts

Posted - 2010-06-03 : 14:03:31

Hi

This is built-in SQL generated by software which cannot be changed.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-04 : 05:06:11

As query plan guides where not introduced until SQL2005, you have no option but to contact the software provider and get them to change the query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-04 : 07:22:50

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=145526

No, you're never too old to Yak'n'Roll if you're too young to die.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-06-07 : 10:16:24

Oh, you can change PeopleSoft queries. It's not recommended and may even violate your contract agreement but we had no choice several years ago when one query was performing extremely poorly (20-30 minutes). We tweaked the join claused, got the process running in less than 4 minutes (acceptable considering the process!). For testing of all subsequent releases, we would test against PS vanilla and then our changes, to ensure nothing was broken. If we had to take an issue directly to PS (very rare), we always gave the vanilla code so we weren't in violation of our agreement with them. They had zero interest in our mods, go figure!

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources