January 15, 2021 at 9:24 am
I have fine tune the query to great extent but these nested loops are something that eat up most in execution plan
Any suggestions?
--khushbu modyfying
DECLARE @Firm_ID_INT INT
DECLARE @TASK_ID_INT INT
SELECT distinct t.task_id AS id, t.client_id clientId, fsc.code as TaskCategory,
(CASE WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like '%, ,'
THEN c.last_corporate_name
WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like '%,'
THEN (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name )
ELSE (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name +','+ c.generation_type) END) AS ClientName,
td.status, t.tasktype_id, tt.task_code AS TaskTypeCode, tt.task_name as taskTypeName, t.status_id AS StatusID,
fr.status_name as StatusType,
t.period_enddate AS PeriodEndDate, t.firm_id,
(SUBSTRING(um.first_name,1,1) +''+ SUBSTRING(um.middle_name,1,1) +''+ SUBSTRING(um.last_name,1,1)) AS AssignedTo,
t.task_desc AS TaskDesc, c.account_number,
um.cUserFullName AS AssignedToName,
t.assigned_id, td.ext_assigned_to
FROM dbo.task_master AS t WITH(NOLOCK)
inner join dbo.firm_return fr ON fr.firm_id = t.firm_id AND fr.status_id = t.status_id
INNER JOIN dbo.firm_task AS tt WITH(NOLOCK) ON t.firm_id = tt.firm_id AND t.tasktype_id = tt.tasktype_id
--inner join firm_specific_category fsc ON fsc.firm_id=tt.firm_id and fsc.category_id= tt.category_id
INNER JOIN dbo.client_master AS c WITH(NOLOCK) ON t.firm_id = c.firm_id AND t.client_id = c.client_id
-- INNER JOIN dbo.firm_task AS tt WITH(NOLOCK) ON t.firm_id = tt.firm_id AND t.tasktype_id = tt.tasktype_id
INNER JOIN dbo.Firm_specific_Category AS fsc WITH(NOLOCK) ON tt.firm_id = fsc.Firm_id AND tt.category_id=fsc.Category_id AND fsc.is_active=1
INNER JOIN dbo.task_duedate AS td WITH(NOLOCK) ON td.task_id = t.task_id AND td.FirmId = t.firm_id
INNER JOIN dbo.Linked_Tasks lt WITH(NOLOCK) ON t.firm_id = lt.firm_id AND t.task_id = lt.lnktask_id
LEFT OUTER JOIN dbo.user_master um WITH(NOLOCK) ON um.firm_id = t.firm_id AND um.user_id = t.assigned_id
WHERE T.FIRM_ID = @Firm_ID_INT AND t.task_id IN (@TASK_ID_INT) AND lt.islinked = 'Y'
UNION ALL
SELECT distinct t.task_id AS id, t.client_id clientId, fsc.code as TaskCategory,
--(SELECT code FROM dbo.firm_specific_category fsc WITH(NOLOCK) WHERE fsc.firm_id=tt.firm_id
--and fsc.category_id=tt.category_id )TaskCategory,
(CASE WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like '%, ,' THEN c.last_corporate_name
WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like '%,' THEN (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name )
ELSE (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name +','+ c.generation_type) END) AS ClientName,
td.status, t.tasktype_id, tt.task_code AS TaskTypeCode, tt.task_name as taskTypeName, t.status_id AS StatusID,
fr.status_name as StatusType,
--(SELECT TOP 1 status_name FROM dbo.firm_return WITH(NOLOCK) WHERE firm_id = t.firm_id AND status_id = t.status_id)StatusType,
t.period_enddate AS PeriodEndDate, t.firm_id,
--(SELECT SUBSTRING(um.first_name,1,1) +''+ SUBSTRING(um.middle_name,1,1) +''+ SUBSTRING(um.last_name,1,1) AS Expr1 FROM dbo.user_master um WITH(NOLOCK) WHERE um.firm_id = t.firm_id AND um.user_id = t.assigned_id) AS AssignedTo,
(SUBSTRING(um.first_name,1,1) +''+ SUBSTRING(um.middle_name,1,1) +''+ SUBSTRING(um.last_name,1,1)) AS AssignedTo,
t.task_desc AS TaskDesc, c.account_number,
--(SELECT cUserFullName AS Expr1 FROM dbo.user_master AS um WITH(NOLOCK) WHERE firm_id = t.firm_id AND (user_id=t.assigned_id)) AS AssignedToName,
um.cUserFullName AS AssignedToName,
t.assigned_id, td.ext_assigned_to
FROM dbo.task_master AS t WITH(NOLOCK)
INNER JOIN dbo.client_master AS c WITH(NOLOCK) ON T.FIRM_ID = C.FIRM_ID AND t.client_id = c.client_id
inner join dbo.firm_return fr ON fr.firm_id = t.firm_id AND fr.status_id = t.status_id
INNER JOIN dbo.firm_task AS tt WITH(NOLOCK) ON t.firm_id = tt.firm_id AND t.tasktype_id = tt.tasktype_id
INNER JOIN dbo.Firm_specific_Category AS fsc WITH(NOLOCK) ON tt.firm_id = fsc.Firm_id AND tt.category_id=fsc.Category_id AND fsc.is_active=1
INNER JOIN dbo.task_duedate AS td WITH(NOLOCK) ON td.task_id = t.task_id AND td.FirmId = t.firm_id
INNER JOIN dbo.Linked_Tasks lt WITH(NOLOCK) ON t.firm_id = lt.firm_id AND t.task_id = lt.lnktask_id
LEFT OUTER JOIN dbo.user_master um WITH(NOLOCK) ON um.firm_id = t.firm_id AND um.user_id = t.assigned_id
WHERE T.FIRM_ID = @Firm_ID_INT AND lt.task_id IN (@TASK_ID_INT) AND lt.islinked='Y'
AND linkedtype IN (SELECT linkedtype FROM dbo.Linked_Tasks WITH(NOLOCK) WHERE firm_id = @Firm_ID_INT AND task_id = (@TASK_ID_INT))
January 15, 2021 at 10:07 am
I see no 'nested loops' here. What do you mean? Looping in T-SQL would require the use of something like WHILE ...
Are you happy that this query may occasionally return inaccurate results, as a result of using NOLOCK all over?
Using
lt.task_id IN (@TASK_ID_INT)
rather than
lt.task_id = @TASK_ID_INT
invites confusion. Why not use the standard = form?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 15, 2021 at 12:25 pm
can you supply a ACTUAL plan instead of estimated of a query with problems - a 1 row estimated is not an issue
on the plan you supplied your big issues are the sort caused by your use of distinct - if this is really required you won't be able to get ride of them.
all others loops on the plan supplied with single row index seek for most of them are optimal on this particular supplied case.
on a separate note and as mentioned on another of your threads get ride of those "nolock"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply