January 6, 2021 at 10:59 am
Any ideas to fix this proc. I have optimised it much but CPU is still high
CREATE PROCEDURE [dbo].[MyViewBuckets]
@firm_id INT,
@current_location INT,
@assigned_id INT,
@bucketID INT
AS
DECLARE @query AS NVARCHAR(MAX)
IF(@bucketID=1)
BEGIN
SELECT DISTINCT t.status_id status_id, (CASE WHEN t .status_id NOT IN (SELECT status_id FROM firm_return with(nolock) WHERE enabled = 'D' and firm_id=t.firm_id) THEN (cl.category_code + '-' + s.status_name) ELSE cl.category_code + '-' +
s.status_name END) AS status_type, t.assigned_id, t.firm_id, (CASE WHEN t .status_id NOT IN (SELECT status_id FROM firm_return with(nolock) WHERE enabled = 'D' and firm_id=t.firm_id) THEN (SELECT COUNT(*) AS Expr1 FROM t
ask_master AS tm with(nolock) CROSS JOIN firm_task AS ft with(nolock) CROSS JOIN category_lp AS c with(nolock) WHERE (tm.status_id = t .status_id) AND (tm.assigned_id = t .assigned_id) AND (tm.firm_id = t .firm_id) AND (tm.tasktype_id = ft.taskt
ype_id) AND (ft.firm_id = t .firm_id) AND (c.category_id = ft.category_id) AND (c.category_code = cl.category_code)) ELSE (SELECT COUNT(*) AS Expr1 FROM task_master AS tm with(nolock) CROSS JOIN firm_task AS ft with(nolock) CROSS JOIN category
_lp AS c with(nolock) WHERE (tm.status_id = t .status_id) AND (tm.assigned_id = t .assigned_id) AND (tm.firm_id = t .firm_id) AND (tm.tasktype_id = ft.tasktype_id) AND (ft.firm_id = t .firm_id) AND (c.category_id = ft.category_id)) END) AS taskcoun
t FROM task_master AS t with(nolock) INNER JOIN firm_return AS s with(nolock) ON t.status_id = s.status_id and t.firm_id=s.firm_id INNER JOIN firm_task AS ft with(nolock) ON t.tasktype_id = ft.tasktype_id AND t.firm_id = ft.firm_id INNER JOIN cat
egory_lp AS cl with(nolock) ON ft.category_id = cl.category_id WHERE (t.assigned_id = @assigned_id) AND (t.firm_id = @firm_id) AND (t.status_id NOT IN (6, 7, 8, 9, 10, 11, 13, 14, 15,20,21,22,23, 37, 38, 50)) GROUP BY t.status_id, s.status_name, t.ass
igned_id, t.firm_id, cl.category_code
UNION
--Checklist Query for Task Assigned To Me
SELECT DISTINCT s.status_id status_id, (CASE WHEN s.status_id NOT IN (SELECT status_id FROM firm_return with(nolock)WHERE enabled = 'D') THEN (cl.category_code + '-' + s.status_name) ELSE s.status_name END) AS status_type, tc.Assigned_pe
rson, t.firm_id, (SELECT COUNT(*) AS Expr1 FROM task_master AS tm with(nolock) , firm_task fk with(nolock) WHERE tm.firm_id = fk.firm_id AND tm.tasktype_id = fk.tasktype_id AND t.firm_id = tm.firm_id AND fk.category_id = cl.category_id AN
D (tm.orignating_location = orignating_location) and (task_id IN (SELECT DISTINCT task_id FROM task_checklist AS tl with(nolock) WHERE (tm.task_id = task_id) AND (Assigned_date <> '1/1/1900') AND (Assigned_person = @assigned_id) AND (ChkCo
mpletion_date = '1/1/1900') AND checklist_action!='D' AND (tasktype_id=(select tasktype_id from task_master with(nolock) where task_id=tm.task_id))))) AS taskcount FROM task_checklist AS tc with(nolock) INNER JOIN task_master AS t with(nolock) ON
tc.task_id = t.task_id INNER JOIN firm_return AS s with(nolock) ON s.status_id = 69 INNER JOIN firm_task AS ft with(nolock) ON t.tasktype_id = ft.tasktype_id AND t.firm_id = ft.firm_id INNER JOIN category_lp AS cl with(nolock) ON ft.category_id = cl.categ
ory_id WHERE (tc.Assigned_person = @assigned_id) AND (tc.Assigned_date <> '1/1/1900') AND (tc.ChkCompletion_date = '1/1/1900') AND tc.checklist_action!='D' AND (t.firm_id = @firm_id) AND (s.status_id = 69) ORDER BY status_id
END
IF(@bucketID=8) --
BEGIN
select td.status,fr.status_name,t.firm_id,fr.status_id,(select count(distinct(tt.task_id)) from task_master tt WITH(NOLOCK) inner join task_duedate tdd WITH(NOLOCK) on tdd.ext_assigned_to=@assigned_id and tt.task_id=tdd.task_id where fr.status_id=tdd.sta
tus and tt.firm_id=t.firm_id and tdd.extention_type!='' and tt.status_id!=23) taskcount from task_master t WITH(NOLOCK) inner join task_duedate td WITH(NOLOCK) on td.task_id=t.task_id inner join firm_return fr WITH(NOLOCK) on fr.firm_id=t.firm_id and td
.status=fr.status_id where t.firm_id=@firm_id and td.ext_assigned_to=@assigned_id and fr.category_id=7 and fr.enabled='Y' and fr.status_id NOT IN(53,54,56) group by td.status,fr.status_name,t.firm_id,fr.status_id
END
IF(@bucketID=2) --
BEGIN
SELECT DISTINCT(t.status_id),(cl.category_code + '-' + s.status_name)AS status_type, t.firm_id,(SELECT COUNT(distinct tm.task_id) AS Expr1 FROM task_master AS tm WITH(NOLOCK) left join task_track AS ttr WITH(NOLOCK) ON tm.task_id=ttr.task_id , firm_ta
sk ft WITH(NOLOCK) WHERE (tm.status_id = t.status_id) AND (tm.firm_id = t.firm_id) AND (tm.orignating_location = t.orignating_location) AND (ttr.updated_by = tt.updated_by) AND ft.firm_id = tm.firm_id AND tm.tasktype_id = ft.tasktype_id AND ft.category
_id = cl.category_id and ttr.to_status_id=t.status_id) AS taskcount FROM task_master AS t WITH(NOLOCK) left join task_track tt WITH(NOLOCK) on t.task_id=tt.task_id INNER JOIN firm_return AS s WITH(NOLOCK) ON t.firm_id=s.firm_id and t.status_id = s.status_
id INNER JOIN firm_task AS ft WITH(NOLOCK) ON t.firm_id = ft.firm_id AND t.tasktype_id = ft.tasktype_id INNER JOIN category_lp AS cl WITH(NOLOCK) ON ft.category_id = cl.category_id WHERE (t.firm_id = @firm_id) AND (tt.updated_by = (select top 1 updated
_by from task_track WITH(NOLOCK) where task_id=t.task_id and updated_by=@assigned_id and to_status_id=t.status_id order by updated_on desc)) AND tt.to_status_id=t.status_id AND (t.status_id NOT IN (1, 23, 35,57,70,126)) and t.orignating_location=@current
_location
END
IF(@bucketID=3) --
BEGIN
SELECT DISTINCT(t.status_id),(cl.category_code + '-' + s.status_name)AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) CROSS JOIN firm_task AS ft WITH(NOLOCK) CROSS JOIN category_lp AS c WITH(NOLOCK) WHERE (tm.firm_
id = t .firm_id) AND (tm.status_id = t .status_id) AND (ft.firm_id = t .firm_id) AND (tm.tasktype_id = ft.tasktype_id) AND (c.category_id = ft.category_id) and (t.orignating_location =tm.orignating_location) AND (c.category_code = cl.category_code))AS tas
kcount from task_master t WITH(NOLOCK),firm_status s WITH(NOLOCK),firm_task ft WITH(NOLOCK),category_lp cl WITH(NOLOCK) where t.firm_id=@firm_id and t.status_id=s.status_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and cl.category_id=ft.ca
tegory_id and t.status_id = s.status_id and assign_rule_general = 'U' and t.orignating_location=@current_location group by t.status_id,cl.category_code,s.status_name,t.firm_id,t.orignating_location
END
IF(@bucketID=4) --
BEGIN
select distinct(t.status_id),s.status_name as status_type,t.firm_id,(select count(*) from task_master tm WITH(NOLOCK) where tm.status_id=t.status_id and tm.firm_id=t.firm_id and tm.orignating_location=t.orignating_location and tm.preparation_id=2) taskc
ount from task_master t WITH(NOLOCK),firm_return s WITH(NOLOCK) where t.status_id=s.status_id and t.firm_id=@firm_id and t.firm_id=s.firm_id and t.status_id in (6,7,8,9,10,11,13,15,37,38,50) and t.preparation_id=2 and t.orignating_location=@current_loca
tion group by t.status_id,s.status_name,t.firm_id,t.orignating_location
END
IF(@bucketID=5) --
BEGIN
CREATE TABLE #IntermediateTasks (status_id int, firm_id int,tasktype_id int, task_count int)
INSERT #IntermediateTasks
select t.status_id,t.firm_id,t.tasktype_id, count(*) AS task_count
FROM task_master AS t WITH(NOLOCK)
WHERE t.firm_id = @firm_id
AND
t.status_id IN
(SELECT DISTINCT status_id FROM firm_return WITH(NOLOCK) WHERE firm_id = @firm_id
AND status_id NOT IN (23, 1, 35, 3, 4,6,7,8,9,10,11,13,37,49,50,58,55,70,126)
AND assign_rule_general IN ('A','PA','O','APA','AP')
AND t.status_id = status_id)
and t.orignating_location=@current_location
and t.status_id not in (57)
group by t.status_id,t.firm_id,t.tasktype_id -- Grouping to just get the rolled up numbers for each status, firm and task type
INSERT #IntermediateTasks
select t.status_id,t.firm_id,t.tasktype_id, count(*) AS task_count
FROM task_master AS t WITH(NOLOCK)
WHERE t.firm_id = @firm_id
AND
t.status_id IN
(SELECT DISTINCT status_id FROM firm_return WITH(NOLOCK) WHERE firm_id = @firm_id
AND status_id IN (57)
AND assign_rule_general IN ('A','PA','O','APA','AP')
AND t.status_id = status_id)
and t.orignating_location=@current_location
and t.assigned_id = 0 -- Filter for only those tasks with an assigned_id = 0
group by t.status_id,t.firm_id,t.tasktype_id -- Grouping to just get the rolled up numbers for each status, firm and task type
SELECT tt.status_id status_id,(cl.category_code + '-' + fr.status_name) AS status_type ,tt.firm_id, sum (tt.task_count) TaskCount
, cl.category_id
FROM #IntermediateTasks AS tt
INNER JOIN firm_return AS fr WITH(NOLOCK) ON tt.status_id = fr.status_id and tt.firm_id=fr.firm_id
INNER JOIN firm_task AS ft WITH(NOLOCK) ON tt.tasktype_id = ft.tasktype_id AND tt.firm_id = ft.firm_id
INNER JOIN category_lp AS cl WITH(NOLOCK) ON ft.category_id = cl.category_id and cl.category_id = fr.category_id -- this join had fr.category_id
WHERE tt.firm_id = @firm_id
and fr.status_id NOT IN (23, 1, 35, 3, 4,6,7,8,9,10,11,13,37,49,50,58,55,70,126) AND fr.assign_rule_general IN ('A','PA','O','APA','AP')
--AND tt.status_id IN
-- (SELECT DISTINCT status_id FROM firm_return WITH(NOLOCK) WHERE (firm_id = @firm_id)
-- AND status_id NOT IN (23, 1, 35, 3, 4,6,7,8,9,10,11,13,37,49,50,58,55,70,126)
-- AND assign_rule_general IN ('A','PA','O','APA','AP') AND tt.status_id = status_id)
/*Bug 2975 sf id = 00074533 : My View - Admin bucket showing status that are not supposed to shows*/
GROUP BY tt.status_id,(cl.category_code + '-' + fr.status_name) ,tt.firm_id,cl.category_id
UNION
SELECT DISTINCT s.status_id status_id,(cl.category_code + '-' + s.status_name) AS status_type, t.firm_id, (SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK), firm_task ft WITH(NOLOCK) WHERE tm.firm_id = ft.firm_id AND tm.tasktype_id = ft.ta
sktype_id AND tm.firm_id = t .firm_id AND ft.category_id = cl.category_id AND (tm.orignating_location = t.orignating_location) and (task_id IN (SELECT DISTINCT task_id FROM task_checklist AS tl WITH(NOLOCK) WHERE (tm.task_id = tl.task_id AND tm.taskt
ype_id=tl.tasktype_id) AND (Checklist_bucket='A') AND checklist_action!='D' AND Assigned_date!='1/1/1900' AND (ChkCompletion_date = '1/1/1900') and (tm.tasktype_id = tasktype_id)))) AS taskcount,cl.category_id FROM task_checklist AS tc WITH(NOLOCK) INNER
JOIN task_master AS t WITH(NOLOCK) ON tc.tasktype_id=t.tasktype_id AND tc.task_id = t.task_id INNER JOIN firm_status AS s WITH(NOLOCK) ON s.status_id = 69 INNER JOIN firm_return AS fr WITH(NOLOCK) ON t.firm_id = fr.firm_id INNER JOIN firm_task AS ft WI
TH(NOLOCK) ON t.tasktype_id = ft.tasktype_id AND t.firm_id = ft.firm_id INNER JOIN category_lp AS cl WITH(NOLOCK) ON ft.category_id = cl.category_id WHERE (t.firm_id = @firm_id) AND (s.status_id IN (SELECT DISTINCT status_id FROM firm_return WHERE (firm
_id = @firm_id) AND (status_id =69) AND (tc.Checklist_bucket='A') AND checklist_action!='D' AND tc.Assigned_date!='1/1/1900' AND (tc.ChkCompletion_date = '1/1/1900') AND (s.status_id = 69))) and t.orignating_location=@current_location
order by cl.category_id, status_id
OPTION (OPTIMIZE FOR (@firm_id unknown,@current_location UNKNOWN)) --MPW - made change for production performance issues brought on by parameter sniffing.
DROP TABLE #IntermediateTasks
END
IF(@bucketID=6)
BEGIN
IF EXISTS(SELECT 1 FROM firm_master fm (NOLOCK) JOIN firm_setting fs (NOLOCK) ON fs.firm_id = fm.firm_id
WHERE fm.Is_Efile_Integration_Enabled = 1 AND fs.Is_Efile_Integration_Active = 1 AND fm.firm_id = @firm_id)
BEGIN
SELECT DISTINCT(t.status_id),(cl.category_code + '-' + s.status_name)AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) CROSS JOIN firm_task AS ft WITH(NOLOCK) CROSS JOIN category_lp AS c WITH(NOLOCK) WHERE (tm.
firm_id = t .firm_id) AND (tm.status_id = t .status_id) AND (ft.firm_id = t .firm_id) AND (tm.tasktype_id = ft.tasktype_id) AND (c.category_id = ft.category_id) and (t.orignating_location =tm.orignating_location) AND (c.category_code = cl.category_cod
e))AS taskcount from task_master t WITH(NOLOCK),firm_status s WITH(NOLOCK),firm_task ft WITH(NOLOCK),category_lp cl WITH(NOLOCK) where t.firm_id=@firm_id and t.status_id=s.status_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and cl.cat
egory_id=ft.category_id and t.status_id in (SELECT status_id FROM firm_return (NOLOCK) WHERE category_id = 1 AND workflowstepidtax = 10 AND enabled = 'Y' AND Active = 'Y'
AND firm_id = @firm_id) and t.orignating_location=@current_location group by t.status_id,cl.category_code,s.status_name,t.firm_id,t.orignating_location
END
ELSE
BEGIN
SELECT DISTINCT(t.status_id),(cl.category_code + '-' + s.status_name)AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) CROSS JOIN firm_task AS ft WITH(NOLOCK) CROSS JOIN category_lp AS c WITH(NOLOCK) WHERE (tm.
firm_id = t .firm_id) AND (tm.status_id = t .status_id) AND (ft.firm_id = t .firm_id) AND (tm.tasktype_id = ft.tasktype_id) AND (c.category_id = ft.category_id) and (t.orignating_location =tm.orignating_location) AND (c.category_code = cl.category_cod
e))AS taskcount from task_master t WITH(NOLOCK),firm_status s WITH(NOLOCK),firm_task ft WITH(NOLOCK),category_lp cl WITH(NOLOCK) where t.firm_id=@firm_id and t.status_id=s.status_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and cl.cat
egory_id=ft.category_id and t.status_id in (20,21,22) and t.orignating_location=@current_location group by t.status_id,cl.category_code,s.status_name,t.firm_id,t.orignating_location
END
END
IF(@bucketID=7)
BEGIN
select td.status,fr.status_name,t.firm_id,fr.status_id,(select count(distinct(tm.task_id))from task_master tm WITH(NOLOCK) inner join task_duedate tdd WITH(NOLOCK) on tdd.task_id=tm.task_id where tm.firm_id=t.firm_id and tdd.status=fr.status_id and tm.ori
gnating_location in (@current_location) and tdd.extention_type!='' and tm.status_id!=23) taskcount from task_master t WITH(NOLOCK) inner join task_duedate td WITH(NOLOCK) on td.task_id=t.task_id inner join firm_return fr WITH(NOLOCK) on fr.firm_id=t.firm
_id and td.status=fr.status_id
where t.firm_id=@firm_id and fr.category_id=7 and fr.enabled='Y' and fr.status_id NOT IN(53,54,56) and t.orignating_location in (@current_location) group by td.status,fr.status_name,t.firm_id,fr.status_id order by td.status
END
IF(@bucketID=9)
BEGIN
SELECT distinct(t.status_id), ('TX-' + s.status_name) AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) inner join firm_task ft WITH(NOLOCK) on (ft.firm_id=tm.firm_id and tm.tasktype_id=ft.tasktype_id and ft.categ
ory_id=1) where tm.firm_id=t.firm_id and tm.status_id=t.status_id and tm.orignating_location=t.orignating_location) taskcount from task_master t WITH(NOLOCK),firm_return s WITH(NOLOCK) ,firm_task ft WITH(NOLOCK) where t.firm_id=s.firm_id and t.status_id=s
.status_id and t.firm_id=@firm_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and t.status_id not in (1,23,53,35,27,12,126) and s.assign_rule_general<>'P' and ft.category_id=1 and t.orignating_location=@current_location
group by t.status_id,s.status_name,t.firm_id,t.orignating_location
END
IF(@bucketID=10)
BEGIN
SELECT distinct(t.status_id), ('FS-' + s.status_name) AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) inner join firm_task ft WITH(NOLOCK) on (ft.firm_id=tm.firm_id and tm.tasktype_id=ft.tasktype_id and ft.categ
ory_id=2) where tm.firm_id=t.firm_id and tm.status_id=t.status_id and tm.orignating_location=t.orignating_location) taskcount from task_master t WITH(NOLOCK),firm_return s WITH(NOLOCK) ,firm_task ft WITH(NOLOCK) where t.firm_id=s.firm_id and t.status_id=s
.status_id and t.firm_id=@firm_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and t.status_id not in (1,23,53,35,33,27,12,70) and s.assign_rule_general<>'P' and ft.category_id=2 and t.orignating_location=@current_location group by t.statu
s_id,s.status_name,t.firm_id,t.orignating_location
END
IF(@bucketID=11)
BEGIN
SELECT distinct(t.status_id), ('BK-' + s.status_name) AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) inner join firm_task ft WITH(NOLOCK) on (ft.firm_id=tm.firm_id and tm.tasktype_id=ft.tasktype_id and ft.categ
ory_id=3) where tm.firm_id=t.firm_id and tm.status_id=t.status_id and tm.orignating_location=t.orignating_location) taskcount from task_master t WITH(NOLOCK),firm_return s WITH(NOLOCK) ,firm_task ft WITH(NOLOCK) where t.firm_id=s.firm_id and t.status_id=s
.status_id and t.firm_id=@firm_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and t.status_id not in (1,23,53,35,33,27,12,70) and s.assign_rule_general<>'P' and ft.category_id=3 and t.orignating_location=@current_location group by t.statu
s_id,s.status_name,t.firm_id,t.orignating_location
END
IF(@bucketID=12)
BEGIN
SELECT distinct(t.status_id), ('OT-' + s.status_name) AS status_type, t.firm_id,(SELECT COUNT(*) AS Expr1 FROM task_master AS tm WITH(NOLOCK) inner join firm_task ft WITH(NOLOCK) on (ft.firm_id=tm.firm_id and tm.tasktype_id=ft.tasktype_id and ft.categ
ory_id=4) where tm.firm_id=t.firm_id and tm.status_id=t.status_id and tm.orignating_location=t.orignating_location) taskcount from task_master t WITH(NOLOCK),firm_return s WITH(NOLOCK) ,firm_task ft WITH(NOLOCK) where t.firm_id=s.firm_id and t.status_id=s
.status_id and t.firm_id=@firm_id and ft.firm_id=t.firm_id and t.tasktype_id=ft.tasktype_id and t.status_id not in (1,23,53,35,33,27,12,70) and s.assign_rule_general<>'P' and ft.category_id=4 and t.orignating_location=@current_location group by t.statu
s_id,s.status_name,t.firm_id,t.orignating_location
END
January 6, 2021 at 2:20 pm
Do you really need all those DISTINCT statements? Shouldn't the one that fills the temporary tables do the job for the ones that follow? Each of those requires aggregation. Also, DISTINCT combined with UNION means that you're asking the optimizer to do two sets of aggregations. UNION itself is a distinct operation.
Overall though, you need to pull the execution plans for this to understand where the slow points are. All the NOLOCK statements are pretty horrifying, just saying. You could make that much easier by setting the connection to READ_UNCOMMITTED. Then, when you find out that NOLOCK is causing data problems (because it does), you have a single point to fix.
I see a lot of IN statements with sub-SELECTS. These are frequently more efficient as JOIN operations, even if they're still sub-SELECTS.
Post the actual execution plans if you can. If not, the estimated plans will do.
One thing you could do is focus down on which specific query is causing the most pain. Capture the individual statements using Extended Events. Then you can see where the most pain is coming from.
"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
January 6, 2021 at 4:05 pm
I don't like these massive procs with code branches. I would create a separate proc for each bucket. Then in this proc, EXEC the relevant new proc within each IF statement.
I would also order the IF statements in the most common use, and RETURN as soon as each proc has been executed. This would reduce the number of IF statements that SQL needs to evaluate each time.
First prize, would be to have the caller call the correct proc, rather than the one to rule them all.
January 6, 2021 at 7:28 pm
So many things in here are just downright horrifying. Not just for performance but maintainability. There is a LOT of code that makes this far more complicated than it needs to be.
Here is one such piece that has me scratching my head.
(CASE WHEN t .status_id NOT IN (SELECT status_id FROM firm_return with(nolock) WHERE enabled = 'D' and firm_id=t.firm_id) THEN (cl.category_code + '-' + s.status_name) ELSE cl.category_code + '-' + s.status_name END) AS status_type
What is the point of this case expression? You return the same value in both branches. Why bother with all this craziness? Simply make be "cl.category_code + '-' + s.status_name AS status_type"
All those cross joins later turned into an inner joins are just painful. You should use inner joins and avoid writing dozens of lines of extra code.
You are inconsistent with the horrible NOLOCK hint. Sometimes you use the correct syntax "with (nolock)", other times you omit the "with" keyword". Leaving it out has been deprecated. But even better would be to remove it entirely as that hint is dreadful and far more sinister than you think.
You have distinct AND group by on the same query. The department of redundancy department wants you to pick one of the other way of getting distinct rows.
To be honest this query doesn't need to be tuned, it needs to be redesigned from the beginning. It would take days to unravel this into something fast where starting over would be much faster to a solution. Not trying to bust your chops here, just trying to be realistic that there is a LOT of stuff here that needs to be addressed before you have a chance at making this perform well.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2021 at 1:52 pm
Ain't gonna lie, I'm shocked as heck that the optimizer didn't timeout on this query.
The good news, lots of index seeks. The bad news, scattered all over the place, all the different aggregations you're running. DISTINCT all over the map as shown in multiple different Sort operators to get distinct values. That's probably where your CPU usage is coming from. Sean and Des have suggestions worth following. I'd also look to eliminate all that DISTINCT stuff. It's killing you, for certain. Also, all the IN clauses may work better as JOINs.
"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
January 8, 2021 at 8:34 am
Thanks bunch for all the inputs. Have incorporated many of them still tweaking it
January 8, 2021 at 6:34 pm
You should review the logic for @bucketID's 9 through 12. The logic appears to be the same for each one - with minor differences. You should be able to reduce that to a single query and parameterize the differences.
For example - you can parameterize the status_name using a case expression checking for the bucket ID to determine what value to return. You can also parameterize ft.category_id based on the bucket ID...
You also don't need the sub-query to get the count...you should be able to adjust the group by to remove the firm_id and originating_location (they are not included in the select - so should not be included in the group by) - which will then allow you to remove the distinct(status_id).
If you parameterize the status code/name - you can then use that in the select and group by:
Declare @statusCode char(3) = Case @bucketID
When 9 Then 'TX-'
When 10 Then 'FS-'
When 11 Then 'BK-'
When 12 Then 'OT-'
End;
Select ...
, status_type = concat(@statusCode, s.status_name)
...
Group By
s.status_id
, concat(@statusCode, s.status_name)
...
I have not looked at the other queries - but I am sure there are opportunities to combine those in similar ways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2021 at 8:56 am
Thanks I thought of that. But there are few more differences in where so let it go.
January 11, 2021 at 4:51 pm
Thanks I thought of that. But there are few more differences in where so let it go.
I don't see those 'few more differences' in the code...the following could replace the last 4 statements:
If @bucketID In (9, 10, 11, 12)
Begin
Declare @categoryID int = @bucketID - 8
Declare @statusCode char(3) = choose(@categoryID, 'TX-', 'FS-', 'BK-', 'OT-');
Select t.status_id)
, status_type = concat(@statusCode, s.status_name)
, t.firm_id
, task_count = count(*)
From task_master t With(nolock)
Inner Join firm_return s With(nolock) On s.firm_id = t.firm_id
And s.status_id = t.status_id
Inner Join firm_task ft With(nolock) On ft.firm_id = t.firm_id
And ft.tasktype_id = t.tasktype_id
Where t.firm_id = @firm_id
And s.assign_rule_general <> 'P'
And ft.category_id = @categoryID
And t.orignating_location = @current_location
And t.status_id Not In (1, 23, 53, 35, 33, 27, 12, 70)
Group By
t.status_id
, concat(@statusCode, s.status_name);
End
You should not add grouping that isn't necessary - there are several where you have included columns/expressions in the group by that are not returned in the select. You are filtering by firm_id and originating_location - and grouping by those which is not necessary because you will only have a single value. These don't need to be included in the select because you already know the values that will be returned.
In many of the queries - you are getting a count from a sub-query that is correlated to the same set of tables in the from...and then you group by in the outer query without any aggregates. That is not necessary either...it can be done in the outer query and no need to use a sub-query to get the counts.
First step is to rewrite the queries using INNER/OUTER/CROSS JOIN syntax instead of using a comma - reformat the code you can read it - then simplify the query by removing unnecessary grouping, unnecessary sub-queries - and then parameterize what you can to reduce the actual number of queries (like above).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply