March 19, 2012 at 7:07 am
We have this script..it takes over an hour to run if I uncomment the code in the select.
Runs great if I comment out the addtional select statement
There has got to be a better way than I am using here..but can't think of how to do it..
SELECT DISTINCT
RM.RIM_NO AS 'RIM NO',
RM.BRANCH_NO AS 'BRANCH NO',
RM.FIRST_NAME AS 'CLIENT FIRST NAME',
RM.LAST_NAME AS 'CLIENT LAST NAME',
AU.UserName AS 'USER NAME'
--(Select max(CreatedDate) from VCDB00.LPB_ASPNETDB.dbo.LPBSecurity_LoginActivity LA
-- where LA.Userid=AU.Userid and LA.LoginActionLookupid in (23,44,58)) AS 'DATE LAST LOGGED IN'
----SA.SERVICE_ID AS 'SERVICE'
FROM [SSDS_CORE_phoenix.XYZPRIVATEBANK.COM\CORE].phoenix.DBO.RM_ACCT RM
INNER JOIN [ssds_core_phoenix.XYZprivatebank.com\core].phoenix.DBO.XP_RM_SVCS_USER XU WITH(NOLOCK)
ON XU.RIM_NO=RM.RIM_NO
--INNER JOIN [ssds_core_phoenix.XYZprivatebank.com\core].phoenix.DBO.XP_RM_SVCS_USER_PWD XP WITH(NOLOCK)
-- ON XU.RIM_NO= XP.RIM_NO
--INNER JOIN [ssds_core_phoenix.XYZprivatebank.com\core].phoenix.DBO.AD_XP_SVCS XS WITH(NOLOCK)
-- ON XU.SERVICE_ID=XS.SERVICE_ID
INNER JOIN [SSDS_CORE_phoenix.XYZPRIVATEBANK.COM\CORE].phoenix.DBO.XP_RM_SVCS_ACCT SA
ON RM.RIM_NO = SA.RIM_NO
INNER JOIN VCDB00.LPB_ASPNETDB.DBO.IDENTITYGUID IG WITH(NOLOCK)
ON XU.RIM_NO=IG.COREID
INNER JOIN VCDB00.LPB_ASPNETDB.DBO.ASPNET_USERS AU WITH(NOLOCK)
ON AU.USERID=IG.USERID
WHERE SA.SERVICE_ID='152'
AND
RM.STATUS='ACTIVE'
March 19, 2012 at 7:16 am
At first sight that's because you have a correlated subquery in your SELECT statement.
For every row, the SELECT MAX(createddate) query is executed.
You can try to create a CTE that has the MAX created date for every user and then join to this CTE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 19, 2012 at 7:21 am
Or just perform the MAX statement as a derived table (same thing as a CTE) and join to that. In this situation a CTE just changes the location of defining the derived table (not that that's a bad thing, but I just don't like extra code steps if I can avoid them). Same difference either way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2012 at 7:23 am
Grant Fritchey (3/19/2012)
Or just perform the MAX statement as a derived table (same thing as a CTE) and join to that. In this situation a CTE just changes the location of defining the derived table (not that that's a bad thing, but I just don't like extra code steps if I can avoid them). Same difference either way.
I find CTEs to be more readable, but I guess that's just personal preference. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 19, 2012 at 7:37 am
Thanks Guys!
March 19, 2012 at 7:55 am
Koen Verbeeck (3/19/2012)
Grant Fritchey (3/19/2012)
Or just perform the MAX statement as a derived table (same thing as a CTE) and join to that. In this situation a CTE just changes the location of defining the derived table (not that that's a bad thing, but I just don't like extra code steps if I can avoid them). Same difference either way.I find CTEs to be more readable, but I guess that's just personal preference. 🙂
In this situation, yeah, it'd be down to personal preference.
BTW, you could also try a CROSS APPLY instead of a JOIN. Just thinking about it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply