help needed (optimization) - 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

Transact-SQL (2000)

help needed (optimization)

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-18 : 23:13:29

DECLARE
@PostDate datetime,
@StartDate datetime,
@EGRP varchar(3)

SET @StartDate = '20060101'
SET @PostDate = '20060131'
SET @EGRP = '2-'

SELECT A.Code, A.U_EmpCode, A.U_WorkDate, A.U_Type,
ISNULL((SELECT TOP 1 C.Code FROM [@NCM_ESH] C, [@NCM_LS] D WHERE C.U_Type ='3' AND C.U_EmpCode = B.Code AND C.U_WEF <= A.U_WorkDate AND C.U_Code = D.Code ORDER BY U_WEF DESC),-1) AS ESH_Code,
ISNULL((SELECT TOP 1 C.U_Code FROM [@NCM_ESH] C, [@NCM_LS] D WHERE C.U_Type ='3' AND C.U_EmpCode = B.Code AND C.U_WEF <= A.U_WorkDate AND C.U_Code = D.Code ORDER BY U_WEF DESC),-1) AS ESH_U_Code,
ISNULL((SELECT TOP 1 C.U_WEF FROM [@NCM_ESH] C, [@NCM_LS] D WHERE C.U_Type ='3' AND C.U_EmpCode = B.Code AND C.U_WEF <= A.U_WorkDate AND C.U_Code = D.Code ORDER BY U_WEF DESC),-1) As ESH_U_WEF

FROM [@NCM_TE] A, [@NCM_EM] B
WHERE
A.U_EmpCode = B.Code AND
B.U_EmpGrp = @EGRP AND
B.U_IncPay = 'Y' AND
A.U_Lock IS NULL AND
(ISNULL(DATEDIFF(MONTH,@PostDate,B.U_EmpLtD), 0) >= 0 AND ISNULL(DATEDIFF(MONTH,B.U_EmpStD,@PostDate), 0) >= 0) AND
(A.U_Type='A' OR A.U_Type='B' OR A.U_Type='C') AND
A.U_WorkDate BETWEEN @StartDate AND @PostDate
ORDER BY A.U_WorkDate

This is the SQL statement looks like. I wonder whether someone can help me to change it to be more efficient. I found out that when i run this statement (using execution plan), these three statements :

ISNULL((SELECT TOP 1 C.U_Code FROM [@NCM_ESH] C, [@NCM_LS] D WHERE C.U_Type ='3' AND C.U_EmpCode = B.Code AND C.U_WEF <= A.U_WorkDate AND C.U_Code = D.Code ORDER BY U_WEF DESC),-1) AS ESH_U_Code,
ISNULL((SELECT TOP 1 C.U_WEF FROM [@NCM_ESH] C, [@NCM_LS] D WHERE C.U_Type ='3' AND C.U_EmpCode = B.Code AND C.U_WEF <= A.U_WorkDate AND C.U_Code = D.Code ORDER BY U_WEF DESC),-1)

makes things slow as it has to sort before retrieving TOP 1.
any suggestion that i can apply to make it more efficient?
sorry i cant provide the tables as it's very large.
thx.

regards,
erwine

... sql is fun...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-18 : 23:39:53

Move those three selects down into the FROM clause as derived tables. Select ISNULL(MAX(column),-1) and additional columns needed to get the distinct per row in your main SELECT. You then run the statement once for the entire batch instead of once for every row of the SELECT, which is going to kill you if you get a very big resultset.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-05-19 : 00:55:30

Hi Derrick,
Do you mind to elaborate more about additional columns needed??
And do you think that if I define several clustered indexes in those tables, then the process if more efficient?
Thx for your kind attention.

regards,
erwine

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources