sql server - How can i optimize or speed up the following sql query?

Question

So I have the following SQL query which by the way works fine in terms of data extraction. The only issue is that it take forever to retrieve the data.

select distinct cast(bb.idPrefix as varchar)+'-'+cast(bb.id as varchar) as 'TicketID',
bb.Title,
bb.Description,
bb.Submitter,
bb.IssueType,
bb.ProgressStatus as 'Status',
bb.Resolution,
bb.CurrentOwner as 'Assignee',
bb.TimeEstimated / 60 as 'TimeEstimated (minutes)',
bb.TimeRemaining /60 as 'TimeRemaining (minutes)',
bb.TimeLogged /60 as 'TimeSpent (minutes)',
bb.Projectname ,
case when bb.id in (Select id from project.sprint GROUP BY id HAVING COUNT(*) > 1) and 
                      (Select count(*) from project.sprint where id = bb.Id GROUP BY id HAVING COUNT(*) > 1) >1 then 
                      (select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc)
     when bb.id in (Select id from project.sprint GROUP BY id HAVING COUNT(*) > 1) and 
                      (Select count(*) from project.sprint where id = bb.id GROUP BY id HAVING COUNT(*) > 1) >1 then 
                      (select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc)
                       else null end as 'End Sprint',
case when bb.id in (Select id from project.sprint GROUP BY id HAVING COUNT(*) > 1) and 
                      (Select count(*) from project.sprint where id = bb.id GROUP BY id HAVING COUNT(*) > 1) > 1 then 
                      (select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid Asc)
     when bb.id in (Select id from project.sprint GROUP BY id HAVING COUNT(*) > 1) and 
                      (Select count(*) from project.sprint where id = bb.id GROUP BY id HAVING COUNT(*) > 1) > 1 then 
                      (select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid Asc) end as 'Start Sprint',
case when bb.resolution is null and bb.TimeEstimated is null and bb.CurrentOwner is null then 1 else 0 end as 'Backlog',
case when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid asc),14,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid asc),14,1)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid asc),15,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid asc),14,2)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid asc),16,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid asc),14,3)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid asc),12,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid asc),12,1)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid asc),13,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid asc),12,2)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid asc),14,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid asc),12,3) else 0 end as [StartCO],
case when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc),14,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc),14,1)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc),15,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc),14,2)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc),16,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'XXXXX sprint%' order by sprintid desc),14,3)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc),12,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc),12,1)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc),13,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc),12,2)
     when substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc),14,1) like '[0-9]' then substring((select top 1 SprintName from project.sprint where id = bb.id and bb.projectid = 12345 and sprintname like 'YYY sprint%' order by sprintid desc),12,3) else 0 end as [EndCO]
from project.bugs bb 
left join project.sprint bs on
bb.id = bs.id 
left join project.logs l on 
bb.id = l.id
where bb.projectid = 18540
and bb.IssueType = 'tracking' and bb.idPrefix like 'Test%' and (bb.title like '%XXXXX%auto%' or bb.title like '%YYY%auto%')

Any ideas what can I change to speed up this process?

question from:https://stackoverflow.com/questions/65832910/how-can-i-optimize-or-speed-up-the-following-sql-query

Answer 1

This is littered with comments I've added. Some note that the desired behaviour is unknown/impossible so need addressing. I believe, however, I rewrote the correct logic. I got rid of almost all the sub queries bar 2; I moved the rest to the FROM in an OUTER APPLY. Of course, this is impossible to test, as I have no sample data, nor expected results, however, the SQL does parse:

SELECT CAST(bb.idPrefix AS varchar) + '-' + CAST(bb.id AS varchar) AS TicketID, --Don't use single quotes for aliases, they are for literal strings
       bb.Title,
       bb.Description,
       bb.Submitter,
       bb.IssueType,
       bb.ProgressStatus AS Status,--Don't use single quotes for aliases, they are for literal strings
       bb.Resolution,
       bb.CurrentOwner AS Assignee,--Don't use single quotes for aliases, they are for literal strings
       bb.TimeEstimated / 60 AS TimeEstimatedMinutes,--Don't use single quotes for aliases, they are for literal strings. Also, stick to names that don't need delimit identifing
       bb.TimeRemaining / 60 AS TimeRemainingMinutes,--Don't use single quotes for aliases, they are for literal strings.  Also, stick to names that don't need delimit identifing
       bb.TimeLogged / 60 AS TimeSpentMinutes,--Don't use single quotes for aliases, they are for literal strings.  Also, stick to names that don't need delimit identifing
       bb.Projectname,
       CASE
            WHEN (SELECT COUNT(*)
                  FROM project.sprint s
                  WHERE s.id = bb.Id
                  --HAVING is pointless when you're checking in the CASE. If you want to use a HAVING use EXISTS in the CASE
                  GROUP BY id) > 1 THEN SNx.SprintName
            --This will never be true, it's the same as the last WHEN
            /*
            WHEN (SELECT COUNT(*)
                  FROM project.sprint s
                  WHERE s.id = bb.id
                  GROUP BY id
                  --HAVING is pointless when you're checking in the CASE. If you want to use a HAVING use EXISTS in the CASE
                  HAVING COUNT(*) > 1) > 1 THEN SNy.SprintName 
            */
       --ELSE NULL is redundant, a CASE expression already returns NULL if it doesn't evaluate to TRUE
       END AS EndSprint, --Don't use single quotes for aliases, they are for literal strings. Also, stick to names that don't need delimit identifing
       CASE
            WHEN (SELECT COUNT(*)
                  FROM project.sprint s
                  WHERE s.id = bb.id
                  --HAVING is pointless when you're checking in the CASE. If you want to use a HAVING use EXISTS in the CASE
                  GROUP BY id) > 1 THEN SNx.SprintName
            --This will never be true, it's the same as the last WHEN
            /*
            WHEN (SELECT COUNT(*)
                  FROM project.sprint s
                  WHERE s.id = bb.id
                  GROUP BY id
                  --HAVING is pointless when you're checking in the CASE. If you want to use a HAVING use EXISTS in the CASE
                  HAVING COUNT(*) > 1) > 1 THEN SNy.SprintName 
            */
       --ELSE NULL is redundant, a CASE expression already returns NULL if it doesn't evaluate to TRUE
       END AS StartSprint, --Don't use single quotes for aliases, they are for literal strings. Also, stick to names that don't need delimit identifing
       CASE
            WHEN bb.resolution IS NULL
             AND bb.TimeEstimated IS NULL
             AND bb.CurrentOwner IS NULL THEN 1
            ELSE 0
       END AS Backlog, --Don't use single quotes for aliases, they are for literal strings
       CASE
            WHEN SUBSTRING(SNx.SprintName, 14, 1) LIKE '[0-9]' THEN SUBSTRING(SNx.SprintName, 14, 1)
            WHEN SUBSTRING(SNx.SprintName, 15, 1) LIKE '[0-9]' THEN SUBSTRING(SNx.SprintName, 14, 2)
            WHEN SUBSTRING(SNx.SprintName, 16, 1) LIKE '[0-9]' THEN SUBSTRING(SNx.SprintName, 14, 3)
            WHEN SUBSTRING(SNy.SprintName, 12, 1) LIKE '[0-9]' THEN SUBSTRING(SNy.SprintName, 12, 1)
            WHEN SUBSTRING(SNy.SprintName, 13, 1) LIKE '[0-9]' THEN SUBSTRING(SNy.SprintName, 12, 2)
            WHEN SUBSTRING(SNy.SprintName, 14, 1) LIKE '[0-9]' THEN SUBSTRING(SNy.SprintName, 12, 3)
            ELSE 0
       END AS StartCO,
       CASE
            WHEN SUBSTRING(SNx.SprintName, 14, 1) LIKE '[0-9]' THEN SUBSTRING(SNx.SprintName, 14, 1)
            WHEN SUBSTRING(SNx.SprintName, 15, 1) LIKE '[0-9]' THEN SUBSTRING(SNx.SprintName, 14, 2)
            WHEN SUBSTRING(SNx.SprintName, 16, 1) LIKE '[0-9]' THEN SUBSTRING(SNx.SprintName, 14, 3)
            WHEN SUBSTRING(SNy.SprintName, 12, 1) LIKE '[0-9]' THEN SUBSTRING(SNy.SprintName, 12, 1)
            WHEN SUBSTRING(SNy.SprintName, 13, 1) LIKE '[0-9]' THEN SUBSTRING(SNy.SprintName, 12, 2)
            WHEN SUBSTRING(SNy.SprintName, 14, 1) LIKE '[0-9]' THEN SUBSTRING(SNy.SprintName, 12, 3)
            ELSE 0
       END AS EndCO
FROM project.bugs bb
     OUTER APPLY (SELECT TOP 1
                         s.SprintName
                  FROM project.sprint s
                  WHERE s.id = bb.id
                    AND bb.projectid = 12345
                    AND s.sprintname LIKE 'XXXXX sprint%'
                  ORDER BY s.sprintid ASC) SNx
     OUTER APPLY (SELECT TOP 1
                         s.SprintName
                  FROM project.sprint s
                  WHERE s.id = bb.id
                    AND bb.projectid = 12345
                    AND s.sprintname LIKE 'YYY sprint%'
                  ORDER BY s.sprintid ASC) SNy
WHERE bb.projectid = 18540
  AND bb.IssueType = 'tracking'
  AND bb.idPrefix LIKE 'Test%'
  AND (bb.title LIKE '%XXXXX%auto%' --This will never be SARGable due to leading wildcard.
    OR bb.title LIKE '%YYY%auto%'); --This will never be SARGable due to leading wildcard.

Of course, indexing is another matter, but not something I'm even going to begin to entertain here without full DDL and DML of the objects and an execution plan (of this query if the results are correct).