Optimzation sproc!! - 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 2005 Forums

Transact-SQL (2005)

Optimzation sproc!!

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-28 : 04:55:11

hi ,

can you please optimze this below sproc please..


SELECT A.CURCOD, A.RAT001 FROM TBL1 A 
 WHERE  A.APPDAT=
(SELECT MAX(D.APPDAT) FROM TBL1 D 
WHERE A.CURCOD=D.CURCOD AND
 D.APPDAT <= CAST(CONVERT(CHAR(8), getdate(), 112) AS DECIMAL(8, 0))) AND A.CURCOD IN 
(SELECT DISTINCT CURCOD FROM TBL2 WHERE DELFLG=0)
 AND A.CURCOD IN (SELECT DISTINCT CURCOD
 FROM TBL3 WHERE DELFLG=0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-28 : 05:31:16

[code]SELECT w.CURCOD,
w.RAT001
FROM (
SELECT CURCOD,
RAT001,
ROW_NUMBER() OVER (PARTITION BY CURCOD ORDER BY APPDAT DESC) AS recID
FROM TBL1
WHERE APPDAT <= CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DECIMAL(8, 0))
) AS w
INNER JOIN (
SELECT t2.CURCOD
FROM TBL2 AS t2
INNER JOIN TBL3 AS t3 ON t3.CURCOD = t2.CURCOD
WHERE t2.DELFLG = 0
AND t3.DELFLG = 0
GROUP BY t2.CURCOD
) AS x ON x.CURCOD = t1.CURCOD
WHERE w.recID = 1
ORDER BY w.CURCOD[/code]

N 56°04'39.26"
E 12°55'05.63"

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-28 : 05:37:05

quote:

Originally posted by Peso

SELECT		w.CURCOD,
		w.RAT001
FROM		(
			SELECT	CURCOD,
				RAT001,
				ROW_NUMBER() OVER (PARTITION BY CURCOD ORDER BY APPDAT DESC) AS recID
			FROM	TBL1
			WHERE	APPDAT <= CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DECIMAL(8, 0))
		) AS w
INNER JOIN	(
			SELECT		t2.CURCOD
			FROM		TBL2 AS t2
			INNER JOIN	TBL3 AS t3 ON t3.CURCOD = t2.CURCOD
			WHERE		t2.DELFLG = 0
					AND t3.DELFLG = 0
			GROUP BY	t2.CURCOD
		) AS x ON x.CURCOD = t1.CURCOD
WHERE		w.recID = 1
ORDER BY	w.CURCOD

N 56°04'39.26"
E 12°55'05.63"

Thank you very much peso..

awesome..thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-28 : 06:21:27

Thank you.

How long time did the query take before, and how long time does it take now?

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-28 : 09:37:23

[code]
SELECT T.CURCOD, T.RAT001
FROM
(
SELECT CURCOD
FROM TBL1

INTERSECT

SELECT CURCODE
FROM TBL2
WHERE DELFLG = 0

INTERSECT

SELECT CURCODE
FROM TBL3
WHERE DELFLG = 0
) D
INNER JOIN (SELECT CURCOD, RAT001
FROM TBL1)T ON T.CURCODE = D.CURCODE

CROSS APPLY (SELECT 1
FROM TBL1
WHERE CURCOD = T.CURCODE
AND APPDAT <= CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DECIMAL(8, 0))
HAVING T.APPDAT = MAX(APPDAT)) D(i)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources