SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Query Executing Slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akumar.ana06
Starting Member

USA
4 Posts

Posted - 06/03/2010 :  12:03:47  Show Profile  Reply with Quote
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

615 Posts

Posted - 06/03/2010 :  12:51:57  Show Profile  Reply with Quote
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', ' ')
		)
)

Edited by - Ifor on 06/03/2010 12:53:47
Go to Top of Page

akumar.ana06
Starting Member

USA
4 Posts

Posted - 06/03/2010 :  14:03:31  Show Profile  Reply with Quote
Hi

This is built-in SQL generated by software which cannot be changed.
Go to Top of Page

Ifor
Aged Yak Warrior

615 Posts

Posted - 06/04/2010 :  05:06:11  Show Profile  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 06/04/2010 :  07:22:50  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 06/07/2010 :  10:16:24  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000