I have this sql query delivered from a customer which we need to performance optimize.
Is there anyone that can point me in the right direction to where to start looking for optimizing the following query?
The query on my local machine takes about 6-7 seconds, but on the users it´s about 30 seconds, It executes on a mssql 2008r2
Thanks!
var query = @"
DECLARE @SearchString nvarchar(250)
set @SearchString = '1811820001'
;with BaseSelectorCTE (ID) as
(
SELECT ID FROM BaseCases b
where
b.CPR like @SearchString
OR (b.FirstName + ' ' + b.LastName) like @SearchString
OR b.CustomerInfo_InstitutionName like @SearchString
UNION
Select ID from FlexjobCase
where Kommune like @SearchString
UNION
Select ID from DisabledAssistantCase
where Kommune like @SearchString
UNION
Select ID from AdultStudentCase
where Kommune like @SearchString
UNION
Select ID from DiseaseCase
where Kommune like @SearchString
UNION
Select ID from MaternityCase
where Kommune like @SearchString
UNION
Select ID from MiscellaneousCase
where Kommune like @SearchString
UNION
Select ID from WageSubsidyCase
where Kommune like @SearchString
UNION
Select w.ID from WageSubsidyCase w inner join JobCenters j on
w.JobcenterID = j.ID
where
j.Name like @SearchString
UNION
Select a.ID from AdultStudentCase a inner join JobCenters j on
a.JobcenterID = j.ID
where
j.Name like @SearchString
)
--
-- Select BaseCases mapped to result type
--
,ResultSelectorCTE AS
(
select
bc.Id as CaseID,
bc.ChildCaseName,
bc.CPR,
bc.FirstName,
bc.LastName,
bc.CustomerInfo_CustomerInfoID as CustomerInfoID,
bc.CustomerInfo_InstitutionName as InstitutionName,
bc.CaseDeadline,
bc.StatusID,
cs.Name as [StatusName],
cs.Owner as [StatusOwner],
bc.MetaData_Updated as [LastChange],
bc.LastActionDay,
,CASE bc.StatusID WHEN 9 THEN 1 ELSE 0 END as SidstePeriodeSoegt
from BaseCases bc
inner join CaseStatus cs ON
bc.StatusID = cs.ID
inner join BaseSelectorCTE bsCTE ON
bc.ID = bsCTE.ID
)
select * from (Select *, ROW_NUMBER() Over(Order By @@version , CASE WHEN StatusID = 9 then 2 ELSE 1 END, CaseDeadline ASC,
SidstePeriodeSoegt)
As rownum from ResultSelectorCTE where 1=1 AND StatusOwner <> 2 AND StatusOwner <> 3
AND SUBSTRING(CPR, 0, 3) BETWEEN 26-08-2014 AND 26-08-2015) As Result
where rownum Between ((1 - 1) * 100 + 1) AND (1 * 100);
