November 22, 2011 at 5:33 am
I had some code that was running like a three legged dog with three broken legs. All our systems work on Oracle & SQLServer with specific code inserted where necessary for the relevant DB. Here's the code as originally written. The table names and variable names aren't important, but there are about about 1.5m records in both O_950P & O_950D. There are suitable indices on O_950D for the selections being made.
SELECT A.PAGE_TOT ALPHA_VAR1,
A.SUB_ACC_ID ALPHA_VAR2,
A.STAT_DATE ALPHA_VAR3,
A.TYPE ALPHA_VAR4,
A.PAGE_NO ALPHA_VAR5,
A.CL_BAL ALPHA_VAR6
FROM O_950P A,
O_950D C
WHERE C.N_ACC_ID = 'xxx'
AND A.N_ORIGIN = C.N_ORIGIN
AND A.SUB_ACC_ID = C.SUB_ACC_ID
AND A.STAT_DATE = C.STAT_DATE
AND A.PAGE_TOT = C.LAST_PAGE_TOT
AND A.PAGE_NO = C.LAST_PAGE_NO
AND A.TYPE = C.TYPE
AND C.STAT_DATE = (SELECT MAX(D.STAT_DATE)
FROM O_950D D
WHERE D.N_ORIGIN = A.N_ORIGIN
AND D.SUB_ACC_ID = A.SUB_ACC_ID
AND D.TYPE = A.TYPE
);
Rewriting it in Oracle as this improved the performance about five fold:
WITH
RANKED_PAGES AS
(SELECT RANK() OVER (PARTITION BY P.N_ORIGIN,
P.SUB_ACC_ID,
P.TYPE
ORDER BY P.STAT_DATE DESC,
P.PAGE_TOT,
P.PAGE_NO) RK,
P.*
FROM O_950P P,
O_950SUB S
WHERE S.N_ACC_ID = 'xxx'
AND P.N_ORIGIN = S.SUB_ACC_ORIGIN
AND P.SUB_ACC_ID = S.SUB_ACC_ID
)
SELECT RANKED_PAGES.PAGE_TOT ALPHA_VAR1,
RANKED_PAGES.SUB_ACC_ID ALPHA_VAR2,
RANKED_PAGES.STAT_DATE ALPHA_VAR3,
RANKED_PAGES.TYPE ALPHA_VAR4,
RANKED_PAGES.PAGE_NO ALPHA_VAR5,
RANKED_PAGES.CL_BAL ALPHA_VAR6
FROM RANKED_PAGES
WHERE RANKED_PAGES.RK = 1
ORDER BY RANKED_PAGES.N_ORIGIN,
RANKED_PAGES.SUB_ACC_ID,
RANKED_PAGES.TYPE
;
My question is, does anybody know what the SQLServer equivalent of this (RANK ... OVER ... PARTITION) would be, please? (Assuming there one). It needs to run in SQLServer 2008 and, probably, 2005.
November 22, 2011 at 5:35 am
It's exactly the same:
November 22, 2011 at 6:51 am
Why didn't I think of that? Thank you very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply