Share via

How can I refine my query in a best way with optimum results (for large database tables) ?

mehmood tekfirst 771 Reputation points
2022-11-08T07:33:45.937+00:00

How can I refine my query in a best way with optimum results (for large database tables) ?

I tried to optimize the query with same results.
please see my query. I used two inner join by using the same derived tables which is fine.
Our client is now experiencing the slow performance issues by using below query. 

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
ALTER PROCEDURE [dbo].[spGetCommonList]   
 @firstName nvarchar(250) = NULL,  
 @surName nvarchar(250) = NULL,  
 @title nvarchar(50) = NULL,  
 @companyName nvarchar(250) = NULL,  
 @phone nvarchar(50) = NULL,  
 @email nvarchar(250) = NULL,  
 @country nvarchar(250) = NULL,  
 @county nvarchar(250) = NULL,  
 @town nvarchar(250) = NULL,  
 @postcode nvarchar(250) = NULL,  
 @isHighRiskUser bit = NULL,  
 @note nvarchar(250) = NULL,  
 @franchiseId int = null,  
 @renterType int = NULL,  
 @Take int = 0,  
 @Skip int = 0  
AS  
BEGIN  
  
declare @PageSize int = @Take;  
declare @PageNumber int = @Skip;  
  
Select tblD.Id as 'Id', [dbo].ConvertToEncryptedId(tblD.Id) as 'EncId',  
(CASE     
  WHEN ISNULL(tblD.RenterType, 0) = 3 THEN  
  ISNULL(tblD.CompanyName, '')  
  ELSE  
  ISNULL(tblD.FirstName, '') + ' ' + ISNULL(tblD.Surname, '')  
END) as 'RenterAccountHolderFullName',  
  
ISNULL(tblD.RenterPhone, '') as 'RenterPhone',  
ISNULL(tblD.RenterEmail, '') as 'RenterEmail',  
ISNULL(tblD.PostCode, '') as 'LicencePostCode',  
ISNULL(tblD.CountryName, '') as 'CountryName',  
ISNULL(tblD.CountryName, '') as 'CountyName',  
ISNULL(tblD.TownName, '') as 'TownName',  
ISNULL(tblD.ContactPostCode, '') as 'ContactPostCode',  
  
(CASE     
  WHEN ISNULL(tblD.RenterType,0) = 3 THEN  
  'Company'  
  WHEN ISNULL(tblD.RenterType,0) = 1 THEN  
  'Individual / Sole Trade'  
  WHEN ISNULL(tblD.RenterType,0) = 2 THEN  
  'Individual / Sole Trade'  
  ELSE  
  'N/A'  
END) as 'RenterTypeName',  
  
ISNULL(tblD.isHighRiskUser, 0) as 'IshighRiskRenter',  
  
(CASE     
  WHEN ISNULL(tblD.isHighRiskUser, 0) = 1 THEN  
  'Yes'  
  ELSE  
  'No'  
END) as 'HighRiskUsrTxt',  
  
ISNULL(tblD.HighRiskUsrComment, '') as 'HighRiskUsrComment',  
ISNULL(tblD.CompanyAccountNo, '') as 'RenterCompanyAcNumber',  
ISNULL(tblD.CompanyName, '') as 'RenterCompanyName',  
ISNULL(tblD.CompanyDetail, '') as 'RenterCompanyDetails',  
ISNULL(tblD.FirstName, '') as 'RenterFirstName',  
ISNULL(tblD.Surname, '') as 'RenterSurname',  
ISNULL(tblD.FranchiseName, '') as 'FranchiseName',  
ISNULL(tblD.RACount, 0) as 'RACount',  
ISNULL(tblD.ResCount, 0) as 'ResCount',  
count(ISNULL(tblD.Id, 0)) over() as 'TotalCount'  
  
FROM  
(  
SELECT r.[Id],r.[LicenceDetails] AS LicenceDetailAvailable, r.[Id] RenterId,0 LookupRenterId,r.[LicenceIssuedBy],  
r.[LicenceIssuedCountry],r.[LicenceExpiryDate],  
       r.[ETDSeen] IsETDSeen,r.[WebCheck] WebCheck,r.[LicenceSeen] LicenceSeen, r.[CopyMade] CopyMade,r.[RenterType] AS  RenterType,         
    r.Sources  ,   
    r.[Title] ,   
    r.[FirstName], r.[Surname] , r.[DOB] ,  
    r.[PhoneNo] AS RenterPhone,  
  r.[Email] AS  RenterEmail,  
 r.[CompanyAccountNo],  
 r.[CompanyName],  
     r.[CompanyDetail],  
    r.[PostCode], r.[LicenceNo],  r.[House], r.[Street] , r.[Village] ,  
    r.[Country], r.[County] , r.[Town] , (case when isnull(r.CountryName,'') = '-- Select Country --' then '' else r.CountryName end) CountryName, r.CountyName, r.TownName,  
     r.[ContactPostCode],   
  r.[ContactAddressType],  
   r.[ContactAddress1],  
   r.[ContactAddress2],  
   r.[ContactAddress3],   
  r.[ContactCountry],  
  r.[ContactCounty],  
   r.[ContactTown], r.[ContactMethod]  ContactMethod, r.[ContactCountryName] ContactCountryName, r.[ContactCountyName] ContactCountyName,  r.[ContactTownName] ContactTownName,   
    r.[Occupation] Occupation, r.[OccEmploymentType]  OccEmploymentType, r.[OccName]  OccName, r.[OccPhone] OccPhone, r.[OccPostcode] OccPostcode, r.[OccHouse] ,  
       r.[OccStreet] ,r.[OccVillage], r.[OccCountry] OccCountry, r.OccCountryName,r.[OccCounty]  OccCounty, r.OccCountyName,  r.[OccTown] OccTown, r.OccTownName,  r.[OccVerified]  OccVerified,   
     r.[IsApprovedAcountHolder] ,  
    r.[Identification],      
      r.[CoiInsuranceCompany] ,   
   r.[CoiPolicyNumber],  
  r.[CoiContactName]  ,  
     r.[CoiExpiryDate],  
  r.[CoiPhone],  
  r.[CoiCertificateCopy] , r.[LicenceTestPassDate],  r.[LicenceType],r.[LicenceGroups] ,  
    '' RenterAccountHolderFullName , r.[isHighRiskUser], r.[HighRiskUsrComment] HighRiskUsrComment, r.[VatNo] VatNo,r.[IptNo]  IptNo, /*rt.RenterType*/ '' RenterTypeName,   
    /*fr.Name*/ '' FranchiseName,          
    r.[LicenceIsUkAddress], r.[ContactIsUkAddress], r.[OccIsUkAddress] ,   
    r.[MainDriverId] MainDriverId,'' OccVerifiedText,r.[FranchiseId],     
    r.IsPermissionToSpeak , r.InsuranceProvider, r.InsuranceCoverNote, r.InsuranceInsuranceExpiryDate, r.InsurancePhone, r.InsuranceContract, r.IsInsuranceSubmissionCompleted,  
    r.Note,ra.RACount RACount, res.ResCount  ResCount,     
 ROW_NUMBER() OVER (PARTITION BY r.[Id] ORDER BY r.[Id] desc) AS RowNum      
  FROM AgreementUsers  r WITH (NOLOCK)    
  left join (Select tblRA.Id,tblRA.UserId,  
 count(tblRA.Id) OVER (  
        PARTITION BY tblRA.UserId /*tblRA.Id*/ Order by tblRA.UserId desc  
    ) RACount FROM  
    (Select distinct ba.Id,ar.RenterUserId UserId   
     from BookingAgreements ba with (nolock)  
 join AgreementRenters ar with (nolock)  on ba.Id = ar.AgreementId    
 where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') <> ''  
     UNION   
 Select distinct ba.Id,ad.MainDriverUserId UserId   
 from BookingAgreements ba  with (nolock)  
 join AgreementDrivers ad with (nolock) on ba.Id = ad.AgreementId   
     where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') <> '') tblRA) ra on ra.UserId = r.Id  
  
 left join (Select tblRes.Id,tblRes.UserId,count(tblRes.Id) OVER (  
        PARTITION BY tblRes.UserId  Order by tblRes.UserId desc  
    ) ResCount FROM  
 (Select distinct ba.Id,ar.RenterUserId UserId   
 from BookingAgreements ba  with (nolock)   
 join AgreementRenters ar with (nolock) on ba.Id = ar.AgreementId   
 where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') = ''  
     UNION   
 Select distinct ba.Id,ad.MainDriverUserId UserId   
 from BookingAgreements ba with (nolock)  
 join AgreementDrivers ad with (nolock) on ba.Id = ad.AgreementId   
     where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') = '') tblRes ) res on res.UserId = r.Id  
  
 Where   
  (isnull(@franchiseId,0) > 0  and r.FranchiseId = @franchiseId)  and  
 (ISNULL(@firstName,'') = '' OR  isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%')) AND  
  (ISNULL(@surName,'') = '' OR (isnull(REPLACE(lower(Surname),' ',''),'')  like CONCAT(ISNULL(REPLACE(lower(@surName),' ',''),''),'%'))) AND  
  (ISNULL(@companyName,'') = '' OR isnull(REPLACE(lower(r.CompanyName),' ',''),'')  like CONCAT(ISNULL(REPLACE(lower(@companyName),' ',''),''),'%'))  
 AND (ISNULL(@email,'') = '' OR  r.[Email] = ISNULL(@email,''))  
 AND (ISNULL(@phone,'') = '' OR  r.[PhoneNo] = ISNULL(@phone,''))  
 AND (ISNULL(@postcode,'') = '' OR  r.[PostCode] = ISNULL(@postcode,''))   
 AND (ISNULL(@country,'') = '' OR  replace(lower(r.[CountryName]),' ','') = ISNULL(replace(lower(@country),' ',''),''))   
 AND (ISNULL(@county,'') = '' OR  replace(lower(r.[CountyName]),' ','') = ISNULL(replace(lower(@county),' ',''),''))   
 AND (ISNULL(@town,'') = '' OR  replace(lower(r.[TownName]),' ','') = ISNULL(replace(lower(@town),' ',''),''))   
 AND (ISNULL(@note,'') = '' OR  Replace(r.HighRiskUsrComment,' ','') like CONCAT('%',ISNULL(REPLACE(@note,' ',''),''),'%'))  
 AND (ISNULL(@renterType,'') = '' OR (ISNULL(@renterType,'') = 1 AND r.[RenterType] in (1,2)) OR (ISNULL(@renterType,'') = 3 AND r.[RenterType] = 3))   
 AND (ISNULL(@isHighRiskUser,'') = '' OR  r.[isHighRiskUser] = @isHighRiskUser)   
 ) tblD WHERE tblD.RowNum = 1  
 order by tblD.[Id] desc  
 OFFSET (@Take * @Skip) ROWS  
 FETCH NEXT @Take ROWS ONLY ;  
  
  
END

and the query plain is mentioned below:

https://1drv.ms/u/s!AtPCgaqki20WhDY5VvrxjsUp4FE9?e=NWJEEV

and my new query which I developed using CTE is as follow: 

GO  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
ALTER PROCEDURE [dbo].[spGetCommonRenterListHOD]   
 @firstName nvarchar(250) = NULL,  
 @surName nvarchar(250) = NULL,  
 @title nvarchar(50) = NULL,  
 @companyName nvarchar(250) = NULL,  
 @phone nvarchar(50) = NULL,  
 @email nvarchar(250) = NULL,  
 @country nvarchar(250) = NULL,  
 @county nvarchar(250) = NULL,  
 @town nvarchar(250) = NULL,  
 @postcode nvarchar(250) = NULL,  
 @isHighRiskUser bit = NULL,  
 @note nvarchar(250) = NULL,  
 @franchiseId int = null,  
 @renterType int = NULL,  
 @Take int = 0,  
 @Skip int = 0  
AS  
BEGIN  
  
declare @PageSize int = @Take;  
declare @PageNumber int = @Skip;  
SET NOCOUNT ON;  
;WITH CTECount AS (  
Select dr.Id, dr.AgreementStatus ,dr.RentalAgreementId,dr.UserId, dr.RACount, dr.ResCount, dr.FranchiseId  
FROM   
(  
Select ba.Id,userDetail.UserId,ba.RentalAgreementId, ba.AgreementStatus,  ba.FranchiseId,         
         ROW_NUMBER() over (partition by userDetail.UserId order by userDetail.UserId desc) RowNo ,         
 (case when ba.RentalAgreementId <> '' then Count(ba.Id)  
 OVER (Partition by (case when ba.RentalAgreementId <> '' then userDetail.UserId else 0 end)) else 0 end) RACount,  
 (case when ba.RentalAgreementId = '' then  Count(ba.Id)   
 OVER (Partition by (case when ba.RentalAgreementId = '' then userDetail.UserId else 0 end)) else 0 end)  ResCount  
     from BookingAgreements ba with (nolock)  
 outer apply  
 (  
 Select ar.AgreementId,ar.RenterUserId UserId, ar.FranchiseId   
 FROM   
 AgreementRenters ar with (nolock)    
 where ar.FranchiseId = ba.FranchiseId and ar.AgreementId = ba.Id  
 UNION   
 Select ad.AgreementId,ad.MainDriverUserId UserId, ad.FranchiseId   
 FROM AgreementDrivers ad with (nolock)  
 where ad.FranchiseId = ba.FranchiseId and ad.AgreementId = ba.Id  
 ) userDetail   
 Where ba.FranchiseId = userDetail.FranchiseId and ba.AgreementStatus <> 2  
 ) dr  where dr.RowNo = 1  
),   
CTE_RecordRows AS(  
  Select tblD.Id,tblD.EncId,tblD.CompanyName,  
  tblD.CompanyAccountNo,  
  tblD.CompanyDetail,  
  tblD.FirstName, tblD.Surname,  
tblD.RenterPhone  ,  
tblD.RenterEmail ,  
tblD.PostCode,  
tblD.CountyName,  
tblD.CountryName,  
tblD.TownName,  
tblD.ContactPostCode,  
tblD.RenterType,    
tblD.isHighRiskUser,  
tblD.HighRiskUsrComment,  
tblD.FranchiseName FranchiseName,  
tblD.RACount, tblD.ResCount,  
tblD.FranchiseId  
FROM  
  (SELECT r.Id ,  
  [dbo].ConvertToEncryptedId(r.Id) as 'EncId',  
  r.CompanyName,  
  r.CompanyAccountNo,  
  r.CompanyDetail,  
  r.FirstName, r.Surname,  
r.PhoneNo  RenterPhone,  
r.Email RenterEmail,  
r.PostCode,  
r.CountyName,  
(case when isnull(r.CountryName,'') = '-- Select Country --' then '' else r.CountryName end) CountryName,  
r.TownName,  
r.ContactPostCode,  
r.RenterType,    
r.isHighRiskUser,  
r.HighRiskUsrComment,  
frn.Name FranchiseName,  
c.RACount,  
c.ResCount,  
r.FranchiseId  
  FROM AgreementUsers  r WITH (NOLOCK)    
  inner join CTECount c on r.Id = c.UserId   
  inner join Franchise frn on r.FranchiseId = frn.Id   
 Where frn.Status_Id = 1 and  
 -- (isnull(@franchiseId,0) > 0  and r.FranchiseId = @franchiseId)  and  
 (ISNULL(@firstName,'') = '' OR  isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%')) AND  
  (ISNULL(@surName,'') = '' OR (isnull(REPLACE(lower(Surname),' ',''),'')  like CONCAT(ISNULL(REPLACE(lower(@surName),' ',''),''),'%'))) AND  
  (ISNULL(@companyName,'') = '' OR isnull(REPLACE(lower(r.CompanyName),' ',''),'')  like CONCAT(ISNULL(REPLACE(lower(@companyName),' ',''),''),'%'))  
 AND (ISNULL(@email,'') = '' OR  r.[Email] = ISNULL(@email,''))  
 AND (ISNULL(@phone,'') = '' OR  r.[PhoneNo] = ISNULL(@phone,''))  
 AND (ISNULL(@postcode,'') = '' OR  r.[PostCode] = ISNULL(@postcode,''))   
 AND (ISNULL(@country,'') = '' OR  replace(lower(r.[CountryName]),' ','') = ISNULL(replace(lower(@country),' ',''),''))   
 AND (ISNULL(@county,'') = '' OR  replace(lower(r.[CountyName]),' ','') = ISNULL(replace(lower(@county),' ',''),''))   
 AND (ISNULL(@town,'') = '' OR  replace(lower(r.[TownName]),' ','') = ISNULL(replace(lower(@town),' ',''),''))   
 AND (ISNULL(@note,'') = '' OR  Replace(r.HighRiskUsrComment,' ','') like CONCAT('%',ISNULL(REPLACE(@note,' ',''),''),'%'))  
 AND (ISNULL(@renterType,'') = '' OR (ISNULL(@renterType,'') = 1 AND r.[RenterType] in (1,2)) OR (ISNULL(@renterType,'') = 3 AND r.[RenterType] = 3))   
 AND (ISNULL(@isHighRiskUser,'') = '' OR  r.[isHighRiskUser] = @isHighRiskUser)  
 ) tblD -- Where tblD.rowNo = 1  
)  
  
Select tblD.Id as 'Id', [dbo].ConvertToEncryptedId(tblD.Id) as 'EncId',  
(CASE     
  WHEN ISNULL(tblD.RenterType, 0) = 3 THEN  
  ISNULL(tblD.CompanyName, '')  
  ELSE  
  ISNULL(tblD.FirstName, '') + ' ' + ISNULL(tblD.Surname, '')  
END) as 'RenterAccountHolderFullName',  
  
ISNULL(tblD.RenterPhone, '') as 'RenterPhone',  
ISNULL(tblD.RenterEmail, '') as 'RenterEmail',  
ISNULL(tblD.PostCode, '') as 'LicencePostCode',  
ISNULL(tblD.CountryName, '') as 'CountryName',  
ISNULL(tblD.CountryName, '') as 'CountyName',  
ISNULL(tblD.TownName, '') as 'TownName',  
ISNULL(tblD.ContactPostCode, '') as 'ContactPostCode',  
  
(CASE     
  WHEN ISNULL(tblD.RenterType,0) = 3 THEN  
  'Company'  
  WHEN ISNULL(tblD.RenterType,0) = 1 THEN  
  'Individual / Sole Trade'  
  WHEN ISNULL(tblD.RenterType,0) = 2 THEN  
  'Individual / Sole Trade'  
  ELSE  
  'N/A'  
END) as 'RenterTypeName',  
  
ISNULL(tblD.isHighRiskUser, 0) as 'IshighRiskRenter',  
  
(CASE     
  WHEN ISNULL(tblD.isHighRiskUser, 0) = 1 THEN  
  'Yes'  
  ELSE  
  'No'  
END) as 'HighRiskUsrTxt',  
  
ISNULL(tblD.HighRiskUsrComment, '') as 'HighRiskUsrComment',  
ISNULL(tblD.CompanyAccountNo, '') as 'RenterCompanyAcNumber',  
ISNULL(tblD.CompanyName, '') as 'RenterCompanyName',  
ISNULL(tblD.CompanyDetail, '') as 'RenterCompanyDetails',  
ISNULL(tblD.FirstName, '') as 'RenterFirstName',  
ISNULL(tblD.Surname, '') as 'RenterSurname',  
ISNULL(tblD.FranchiseName, '') as 'FranchiseName',  
ISNULL(tblD.RACount, 0) as 'RACount',  
ISNULL(tblD.ResCount, 0) as 'ResCount',  
count(ISNULL(tblD.Id, 0)) over() as 'TotalCount',  
ISNULL(tblD.FranchiseId, 0) as 'Franchise_Id'  
FROM CTE_RecordRows tblD    
order by tblD.[FranchiseName] asc  
OFFSET (@Take * @Skip) ROWS  
FETCH NEXT @Take ROWS ONLY OPTION (RECOMPILE);  
  
END

and the query plan is mentioned below:

https://1drv.ms/u/s!AtPCgaqki20WhDSTz9CQ7zvZhmsM?e=rldYeJ

See the tables structure below 

 CREATE TABLE [dbo].[Franchise](  
        [Id] [int] IDENTITY(1,1) NOT NULL,  
        [Name] [nvarchar](50) NOT NULL,   
     CONSTRAINT [PK_Franchise] PRIMARY KEY CLUSTERED   
    (  
        [Id] ASC  
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
    ) ON [PRIMARY]  
    GO  
  
CREATE TABLE [dbo].[BookingAgreements](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [FranchiseId] [int] NOT NULL,     
    [ReservationId] [varchar](9) NULL,  
    [RentalAgreementId] [varchar](9) NULL,    
    [AgreementStatus] [int] NULL,     
    [StatusId] [int] NULL,    
    [OrderNumber] [nvarchar](250) NULL,  
    [ChargedDays] [decimal](18, 2) NULL,      
 CONSTRAINT [PK_BookingAgreements] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
CREATE TABLE [dbo].[AgreementDrivers](  
    [AgreementId] [bigint] NOT NULL,  
    [DriverId] [bigint] NOT NULL,         
    [FranchiseId] [bigint] NOT NULL,  
    [StatusId] [int] NULL,  
    [Id] [bigint] IDENTITY(1,1) NOT NULL,  
    [DriverPhone] [varchar](50) NULL,  
    [DriverTitle] [varchar](150) NULL,  
    [DriverFirstName] [varchar](150) NULL,  
    [DriverSurname] [varchar](150) NULL,  
    [DriverDOB] [datetime] NULL,  
    [DriverCompanyName] [varchar](150) NULL,  
    [DriverCompanyDetails] [varchar](500) NULL,  
    [DriverEmail] [varchar](150) NULL,  
    [DriverSources] [varchar](50) NULL,  
    [DriverPostCode] [varchar](50) NULL,  
    [DriverHouse] [varchar](250) NULL,  
    [DriverStreet] [varchar](250) NULL,  
    [DriverVillage] [varchar](50) NULL,  
    [DriverCountry] [int] NULL,  
    [DriverCounty] [int] NULL,  
    [DriverTown] [int] NULL,  
    [DriverDetailTownName] [nvarchar](250) NULL,  
    [DriverDetailCountyName] [nvarchar](250) NULL,  
    [DriverCountryName] [varchar](250) NULL,  
    [LicenceCopyMade] [bit] NULL,  
    [LicenceDetailCountry] [varchar](100) NULL,  
    [LicenceType] [int] NULL,  
    [LicenceTestPassDate] [datetime] NULL,  
    [LicenceExpiryDate] [datetime] NULL,  
    [RenterId] [int] NULL,  
    [MainDriverUserId] [int] NULL,  
 CONSTRAINT [PK_AgreementDrivers] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
CREATE TABLE [dbo].[AgreementRenters](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [AgreementId] [int] NULL,  
    [FranchiseId] [int] NULL,  
    [isRenterAgreedAccHolder] [bit] NULL,  
    [RenterTypeId] [int] NULL,  
    [isHighRiskUser] [bit] NULL,  
    [MainDriverId] [int] NULL,  
    [RenterSources] [varchar](50) NULL,  
    [RenterTitle] [varchar](150) NULL,  
    [RenterFirstName] [varchar](150) NULL,  
    [RenterSurname] [varchar](150) NULL,  
    [RenterDOB] [datetime] NULL,  
    [RenterPhone] [varchar](50) NULL,  
    [RenterEmail] [varchar](150) NULL,  
    [RenterCompanyName] [varchar](150) NULL,  
    [RenterCompanyDetails] [varchar](500) NULL,  
    [RenterCompanyAcNumber] [varchar](50) NULL,  
    [LicencePostCode] [varchar](50) NULL,  
    [LicenceHouse] [varchar](250) NULL,  
    [LicenceStreet] [varchar](250) NULL,  
    [LicenceVillage] [varchar](50) NULL,  
    [LicenceCountry] [int] NULL,  
    [LicenceCounty] [int] NULL,  
    [LicenceTown] [int] NULL,  
    [LicenceTownName] [nvarchar](250) NULL,  
    [LicenceCountyName] [nvarchar](250) NULL,  
    [LicenceCountryName] [nvarchar](250) NULL,  
    [LicenceNumber] [varchar](50) NULL,  
    [LicenceDetails] [bit] NULL,  
    [LicenceType] [int] NULL,  
    [LicenceIssuedBy] [varchar](50) NULL,  
    [LicenceIssuedCountry] [varchar](50) NULL,  
    [LicenceTestPassDate] [datetime] NULL,  
    [LicenceExpiryDate] [datetime] NULL,  
    [LicenceSeen] [bit] NULL,  
    [LicenceWebCheck] [bit] NULL,  
    [LicenceETDSeen] [bit] NULL,  
    [LicenceCopyMade] [bit] NULL,  
    [LicenceGroups] [varchar](50) NULL,  
    [RenterLicenceIsUkAddress] [bit] NOT NULL,  
    [ContactPostCode] [varchar](50) NULL,  
    [ContactAddressType] [varchar](50) NULL,  
    [ContactAddress1] [varchar](250) NULL,  
    [ContactAddress2] [varchar](250) NULL,  
    [ContactAddress3] [varchar](250) NULL,  
    [ContactCountry] [int] NULL,  
    [ContactCounty] [int] NULL,  
    [ContactTown] [int] NULL,  
    [ContactMethod] [varchar](50) NULL,  
    [RenterContactIsUkAddress] [bit] NOT NULL,  
    [ContactTownName] [nvarchar](250) NULL,  
    [ContactCountyName] [nvarchar](250) NULL,  
    [ContactCountryName] [nvarchar](250) NULL,  
    [Occupation] [varchar](50) NULL,  
    [OccEmploymentType] [varchar](150) NULL,  
    [OccName] [nvarchar](250) NULL,  
    [OccPhone] [varchar](50) NULL,  
    [OccPostcode] [nvarchar](50) NULL,  
    [OccAddress1] [varchar](250) NULL,  
    [OccAddress2] [varchar](250) NULL,  
    [OccAddress3] [varchar](250) NULL,  
    [OccCountry] [int] NULL,  
    [OccCounty] [int] NULL,  
    [OccTown] [int] NULL,  
    [OccVerified] [bit] NULL,  
    [RenterOccIsUkAddress] [bit] NOT NULL,  
    [OccTownName] [nvarchar](250) NULL,  
    [OccCountyName] [nvarchar](250) NULL,  
    [OccCountryName] [nvarchar](250) NULL,  
    [RenterIdentification] [varchar](max) NULL,  
    [CoiInsuranceCompany] [varchar](50) NULL,  
    [CoiPolicyNumber] [varchar](50) NULL,  
    [CoiContactName] [varchar](50) NULL,  
    [CoiExpiryDate] [datetime] NULL,  
    [CoiPhone] [varchar](20) NULL,  
    [CoiCertificateCopy] [bit] NULL,  
    [HighRiskUsrComment] [nvarchar](500) NULL,  
    [VatNo] [varchar](50) NULL,  
    [IptNo] [varchar](50) NULL,  
    [StatusId] [int] NULL,  
    [IsPermissionToSpeak] [bit] NULL,  
    [InsuranceProvider] [varchar](100) NULL,  
    [InsuranceCoverNote] [varchar](250) NULL,  
    [InsuranceInsuranceExpiryDate] [datetime] NULL,  
    [InsurancePhone] [varchar](50) NULL,  
    [InsuranceContract] [varchar](250) NULL,  
    [IsInsuranceSubmissionCompleted] [bit] NULL,  
    [RenterUserId] [int] NULL,  
    [Note] [nvarchar](500) NULL,  
    [CreatedBy] [int] NULL,  
    [ModifiedBy] [int] NULL,  
    [CreatedDate] [datetime] NULL,  
    [ModifiedDate] [datetime] NULL,  
    [IsGlobalUserIgnored] [int] NULL,  
 CONSTRAINT [PK_AgreementRenters] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
  
DROP TABLE [dbo].[AgreementUsers]  
GO  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[AgreementUsers](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [IsRenter] [bit] NULL,  
    [IsMainDriver] [bit] NOT NULL,  
    [IsAdditionalDriver] [bit] NULL,  
    [Title] [varchar](150) NULL,  
    [FirstName] [varchar](150) NULL,  
    [MidName] [varchar](150) NULL,  
    [Surname] [varchar](150) NULL,  
    [DOB] [datetime] NULL,  
    [RenterId] [int] NULL,  
    [FranchiseId] [int] NULL,  
    [MainDriverId] [int] NULL,  
    [AdditionalDriverId] [int] NULL,  
    [RentalType] [int] NULL,  
    [RenterType] [int] NULL,  
    [RenterCompanyType] [smallint] NULL,  
    [CompanyAccountNo] [varchar](150) NULL,  
    [CompanyName] [varchar](150) NULL,  
    [CompanyDetail] [varchar](150) NULL,  
    [StatusId] [int] NULL,  
    [CreatedOn] [datetime] NULL,  
    [ModifiedOn] [datetime] NULL,  
    [CreatedUserId] [int] NULL,  
    [ModifiedUserId] [int] NULL,  
    [PostCode] [varchar](150) NULL,  
    [PhoneNo] [varchar](150) NULL,  
    [Email] [varchar](150) NULL,  
    [Street] [varchar](250) NULL,  
    [LicenceNo] [varchar](150) NULL,  
    [Sources] [varchar](50) NULL,  
    [IsApprovedAcountHolder] [bit] NULL,  
    [LicenceIsUkAddress] [bit] NOT NULL,  
    [House] [varchar](250) NULL,  
    [Village] [varchar](50) NULL,  
    [Country] [int] NULL,  
    [County] [int] NULL,  
    [Town] [int] NULL,  
    [CountryName] [varchar](250) NULL,  
    [LicenceDetails] [bit] NULL,  
    [LicenceType] [int] NULL,  
    [LicenceIssuedBy] [varchar](50) NULL,  
    [LicenceIssuedCountry] [varchar](50) NULL,  
    [LicenceTestPassDate] [datetime] NULL,  
    [LicenceExpiryDate] [datetime] NULL,  
    [LicenceSeen] [bit] NULL,  
    [WebCheck] [bit] NULL,  
    [ETDSeen] [bit] NULL,  
    [CopyMade] [bit] NULL,  
    [LicenceGroups] [varchar](50) NULL,  
    [TownName] [nvarchar](250) NULL,  
    [CountyName] [nvarchar](250) NULL,  
    [Occupation] [varchar](50) NULL,  
    [OccEmploymentType] [varchar](150) NULL,  
    [OccName] [nvarchar](250) NULL,  
    [OccPhone] [varchar](50) NULL,  
    [OccPostcode] [nvarchar](50) NULL,  
    [OccHouse] [varchar](250) NULL,  
    [OccStreet] [varchar](250) NULL,  
    [OccVillage] [varchar](250) NULL,  
    [OccCountry] [int] NULL,  
    [OccTown] [int] NULL,  
    [OccVerified] [bit] NULL,  
    [OccIsUkAddress] [bit] NULL,  
    [OccTownName] [nvarchar](250) NULL,  
    [OccCountyName] [nvarchar](250) NULL,  
    [OccCountryName] [nvarchar](250) NULL,  
    [Identification] [varchar](max) NULL,  
    [IsRenterMainDriver] [bit] NULL,  
    [HasMedicalIssue] [bit] NULL,  
    [HasAccident] [bit] NULL,  
    [HasConviction] [bit] NULL,  
    [HasEverRefusedInsurance] [bit] NULL,  
    [HasVehicleOrTrailerForCarriage] [bit] NULL,  
    [OccCounty] [int] NULL,  
    [Note] [nvarchar](500) NULL,  
    [ContactPostCode] [varchar](50) NULL,  
    [ContactAddressType] [varchar](50) NULL,  
    [ContactAddress1] [varchar](250) NULL,  
    [ContactAddress2] [varchar](250) NULL,  
    [ContactAddress3] [varchar](250) NULL,  
    [ContactCountry] [int] NULL,  
    [ContactCounty] [int] NULL,  
    [ContactTown] [int] NULL,  
    [ContactMethod] [varchar](50) NULL,  
    [ContactIsUkAddress] [bit] NOT NULL,  
    [ContactTownName] [nvarchar](250) NULL,  
    [ContactCountyName] [nvarchar](250) NULL,  
    [ContactCountryName] [nvarchar](250) NULL,  
    [CoiInsuranceCompany] [varchar](50) NULL,  
    [CoiPolicyNumber] [varchar](50) NULL,  
    [CoiContactName] [varchar](50) NULL,  
    [CoiExpiryDate] [datetime] NULL,  
    [CoiPhone] [varchar](20) NULL,  
    [CoiCertificateCopy] [bit] NULL,  
    [BKIsOwnInsurance] [bit] NULL,  
    [isHighRiskUser] [bit] NULL,  
    [HighRiskUsrComment] [nvarchar](500) NULL,  
    [VatNo] [varchar](50) NULL,  
    [IptNo] [varchar](50) NULL,  
    [IsPermissionToSpeak] [bit] NULL,  
    [InsuranceProvider] [varchar](100) NULL,  
    [InsuranceCoverNote] [varchar](250) NULL,  
    [InsuranceInsuranceExpiryDate] [datetime] NULL,  
    [InsurancePhone] [varchar](50) NULL,  
    [InsuranceContract] [varchar](250) NULL,  
    [IsInsuranceSubmissionCompleted] [bit] NULL,  
 CONSTRAINT [PK_AgreementUsers] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO

and These are the online plans with query

Newly created plan ( with query) :
https://www.brentozar.com/pastetheplan/?id=HkPsA1dBj

First plan :
https://www.brentozar.com/pastetheplan/?id=HJlN1xuHi

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.1K Reputation points
    2022-11-09T22:20:48.26+00:00

    I'm afraid that there are too many things here to handle of them in a forum. I have the feeling that if I was given the task to tune this procedure as a consultant, it would keep me busy for a few days.

    I agree with LiHong's advice to break this up in multiple statements. That is, use temp table to store intermediate results. That makes it easier to work with the code, and you may be able to isolate where the bottle neck is. Also, you can verify that the intermediate results are correct. Because, this we should not forget: performance is not all. Performance is worth nothing, if the result returned is wrong.

    When it comes to things like:

    (ISNULL(@Firstname ,'') = '' OR isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@Firstname ),' ',''),''),'%'))

    There are better chances, if you write it as:

    @Firstname IS NULL OR FirstName LIKE @Firstname + '%'

    Not because the replace or lower etc takes time, but because the prevent indexes being used. But there has to be an index on FirstName for this to matter. And you would need

    OPTION (RECOMPILE)

    at the end of the query. That is, you need all three to get any effect. Then again, this change will affect the result of the query with some data.

    Turning to the user-defined function. That's not encryption, but rather some form of scrambling that easily can be reversed if you know how it's done. I'm not sure that I see the point with it. Since the UDF is not performning data access, it is not overly expensive, but it still adding insult to injury, so you could try to remove it to see what happens.

    When it comes to UNION, beware that UNION alone will remove duplicates, and that can be expensive. Use UNION ALL instead, unless you need duplicates to be removed.

    1 person found this answer helpful.

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.1K Reputation points
    2022-11-08T23:11:44.13+00:00

    I did not have the time to look at the plans, but I see that in your WHERE clause, you are entangling your columns in functions. This makes it impossible to use indexes, so there will be scans and performance will be horrible.

    You would also need OPTION(RECOMPILE) to deal with the dynamic search conditions, but you need to deal with the arguments first.

    1 person found this answer helpful.
  2. LiHongMSFT-4306 25,651 Reputation points
    2022-11-09T06:51:35.697+00:00

    Hi @mehmood tekfirst
    Here are some possible tips：
    1). Replace a complex statement with multiple simple statements.
    2). Avoid too many OR conditions. Please try to rewrite where clause by removing unnecessary conditions or changing to use a function such as case or decode.
    3). Find out any possible to change from hash join to EXISTS.
    4). Make sure all mentioned columns are indexed.
    5). Check whether it is possible to remove the sort (group by/order by/distinct). Try UNION ALL instead of UNION.

    1 person found this answer helpful.
    0 comments
  3. Erland Sommarskog 107.1K Reputation points
    2022-11-11T23:10:26.307+00:00

    I do have one more suggestion, but from a different angle. I don't know exact what page size you are using, but given the complexity, I don't think you want to run this every time the user presses next page. So even if you only display 20 rows at a time, you should maybe fetch 200 at a time, and buffer rows some where. When/if the user comes to the last page, you fill up another 200 rows in advance and so on.

    In this way, the user may have to wait for the first page, but remaining pages will be swift.

    1 person found this answer helpful.
  4. mehmood tekfirst 771 Reputation points
    2022-11-09T07:38:47.087+00:00

    Thank you @LiHongMSFT-4306 .
    Please see my concerns If someone can recommend

    Point#1 : spGetCommonRenterListHOD is ny new query. Will it effect to rename the stored procedure as it starts from sp (which shouldn't be) .

    Point#2:

    Avoid too many OR conditions. Please try to rewrite where clause by removing unnecessary conditions or changing to use a function such as case or decode.

    Does it mean I need to must need to create a dynamic query to make filter if I remove OR expression

    like I mentioned below 

     (ISNULL(@firstName,'') = '' OR  isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%'))

    and 

    (CASE  
WHEN ISNULL(tblD.RenterType, 0) = 3 THEN

    Second thing is that : Would I need to remove isnull, replace and lower function because It causes a full scan (replace, lower and isnull are the reasons to ignore the indexes) ?

    Point#3:
    I am using a ConvertToEncryptedId scalar function in my query to directly encryption of Id column. Do I need to use it ? If yes then how can I use it efficiently for a single page size ( 25 to 100 records) .
    See my following function If somebody can improve it or suggest something better? 

    ALTER FUNCTION [dbo].[ConvertToEncryptedId](@pId varchar(50))  
RETURNS  varchar(200)  
AS BEGIN  
  
Declare  @fullId varchar(50) = '';  
Declare @localEncId varchar(200)  = '';  
SET @fullId = Concat(@pId,'$',@pId);  
  
SET @localEncId = (SELECT CAST(@fullId as varbinary(max)) FOR XML PATH(''), BINARY BASE64);  
  
Return @localEncId;  
      
END

    Point#4: Some experts said following union is the highest performance issue under CTECount. 

    AgreementRenters ar with (nolock)  
where ar.FranchiseId = ba.FranchiseId and ar.AgreementId = ba.Id  
UNION  
Select ad.AgreementId,ad.MainDriverUserId UserId, ad.FranchiseId  
FROM AgreementDrivers ad with (nolock)

    Union is not an issue, I can use the left joins like as follow 

    Select ba.Id,ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,  
ROW_NUMBER() over (partition by userDetail.UserId order by userDetail.UserId desc) RowNo ,  
(case when ba.RentalAgreementId <> '' then Count(ba.Id)  
OVER (Partition by (case when ba.RentalAgreementId <> '' then ar.RenterUserId,ad.MainDriverUserId else 0 end)) else 0 end) RACount,  
(case when ba.RentalAgreementId = '' then Count(ba.Id)  
OVER (Partition by (case when ba.RentalAgreementId = '' then ar.RenterUserId,ad.MainDriverUserId else 0 end)) else 0 end) ResCount  
from BookingAgreements ba with (nolock)  
left join AgreementRenters ar with (nolock) on ba.Id = ar.AgreementId  
left join AgreementDrivers ad with (nolock) on ba.Id = ad.AgreementId

    Again, there are now two columns ( ar.RenterUserId,ad.MainDriverUserId) which need to be joined uniquely

    Point#5:

    and see partition by Will it give the optimum performance wise results because of using the case statements in it . Kindly suggest or recommend.

    This part is crucial as It give the accurate counts. 

    (case when ba.RentalAgreementId <> '' then Count(ba.Id)  
OVER (Partition by (case when ba.RentalAgreementId <> '' then userDetail.UserId else 0 end)) else 0 end) RACount,

    and

    Point#6:

    The paging related thing . Can We improve it more as well ?

    order by tblD.[FranchiseName] asc
    OFFSET (@タケ * @Hotel ) ROWS
    FETCH NEXT @タケ ROWS ONLY OPTION (RECOMPILE)