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

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?

Continue reading...