Tuning query

  • 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

    • This topic was modified 1 year, 11 months ago by  khushbu.
  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.

    • This reply was modified 1 year, 11 months ago by  DesNorton.
  • 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/

  • Thanks for your inputs.

    Attaching the EP

    • This reply was modified 1 year, 11 months ago by  khushbu.
    Attachments:
    You must be logged in to view attached files.
  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks bunch for all the inputs. Have incorporated many of them still tweaking it

  • 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

  • Thanks I thought of that. But there are few more differences in where so let it go.

  • khushbu wrote:

    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