January 26, 2018 at 4:05 am
Hi all,
I am working on a database that i have no control over the structure. I currently also don't have showplan access (looking to get this resolved). I have this query:
[
DECLARE @domainname NVARCHAR(1024)
SET @domainname = 'domain\user';
WITH cte_data
AS (
SELECT sysuserObj.systemuserid
,sysuserObj.domainname
,ROW_NUMBER() OVER (
ORDER BY audit.action
,audit.createdon ASC
) AS rownum
,sysuserUSER.fullname AS ChangeUser
,sysuserObj.fullname ObjUser
,audit.createdon
,action
,CASE WHEN action = 1 THEN 'Create' WHEN action = 2 THEN 'Update' WHEN action = 33 THEN 'Associate' WHEN action = 34 THEN 'Disassociate' END AS 'Action Type'
,ROLE.businessunitidname
,ROLE.NAME AS ROLE
,OPERATION
,BU.NAME AS LastBU
--,Team.Name as Team
FROM auditbase AUDIT
INNER JOIN filteredsystemuser sysuserObj ON sysuserObj.systemuserid = audit.objectid
INNER JOIN filteredsystemuser sysuserUSER ON sysuserUSER.systemuserid = audit.userid
LEFT JOIN filteredrole ROLE ON CAST(ROLE.roleid AS VARCHAR(100)) = RIGHT(CASE WHEN OPERATION = 1 THEN 'x' ELSE changedata END, CASE WHEN OPERATION = 1 THEN 1 ELSE LEN(changedata) - 33 END)
--LEFT JOIN FilteredTeam Team ON CAST(Team.Teamid AS VARCHAR(100)) = RIGHT(CASE WHEN OPERATION = 1 THEN 'x' ELSE changedata END, CASE WHEN OPERATION = 1 THEN 1 ELSE LEN(changedata) - 32 END)
LEFT JOIN filteredbusinessunit BU ON CAST(BU.businessunitid AS VARCHAR(100)) = RIGHT(CASE WHEN OPERATION = 1 THEN 'x' ELSE changedata END, CASE WHEN OPERATION = 1 THEN 1 ELSE LEN(changedata) - 13 END)
WHERE sysuserObj.domainname = @domainname
AND (
(
left(audit.changedata,33) ='systemuserroles_association~role,'
AND action IN (34, 33)
)
--OR
-- (
-- left(audit.changedata,32) = 'teammembership_association~team,'
-- AND action IN (34, 33)
-- and isnull(team.systemmanaged,0)<>1
-- and isnull(team.isdefault,0)<>1
-- )
OR (
left(audit.changedata,13) = 'businessunit,%'
AND action IN (1, 2)
)
OR audit.OPERATION = 1
)
)
SELECT cte_data.domainname
,cte_data.changeuser
,cte_data.objuser
,cte_data.createdon
,cte_data.[action type]
,cte_data.ROLE
,CASE WHEN nex.lastbu IS NULL
AND cte_data.action = 2 THEN sysuser.businessunitidname WHEN nex.lastbu IS NOT NULL THEN nex.lastbu ELSE nex.lastbu END NewBU
--,cte_data.Team
FROM cte_data
LEFT JOIN cte_data nex ON nex.rownum = cte_data.rownum + 1
INNER JOIN filteredsystemuser SysUser ON cte_data.systemuserid = SysUser.systemuserid
ORDER BY createdon
The bits that are -- removed from the code are the elements that i am looking to add. Unfortunately it changes the code from being run in 1 second - to not completing after 10 minutes. Any ideas what i can do to add these elements, but speed up the performance? The data returned is correct.
BTW the ID`s are all GUIDS. I am aware its not ideal having to convert them into Varchar, but it seems to be the only way to get it to work (as sometimes the change data wont contain a GUID at all!).
Many thanks in advance for any advice,
Dan
January 26, 2018 at 4:27 am
No way to say for 100% certain without at least the execution plan, but, best guess, those LEFT functions on the columns are going to lead to index scans. Same thing with the ISNULL functions. These are absolutely against best practices. I'd eliminate them from the code. Another option is to look to the OR statements. You can sometimes replace an OR with two different queries combined in a UNION ALL and achieve superior results. It's very situational on that and the functions on the columns are still going to step all over what you're trying to do.
Query tuning without a plan is just vague guesses. Get the plan to understand what's happening.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply