November 8, 2022 at 12:16 pm
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
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
November 8, 2022 at 2:43 pm
Is your data really so bad that all those predicates could be null or empty strings? Do they really contain invalid commas?
All the ISNULL(), REPLACE(), & LOWER() functions make your indexes non-sargable,
If you can't fix the data, can you add persisted indexed computed columns that do? Then you could use those in the search w/o the functions that fix the data.
Is your collation case sensitive? If not, you don't need the LOWER functions.
What is the most common query pattern? i.e., which parameters are most frequently populated? You might be able to eliminate the many plan-confounding OR statements by using dynamic sql to query only on the columns for which parameters were specified. If one combination of parameters is used 90% of the time, you might call out to a procedure specifically tuned for that pattern.
See https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ and https://www.sommarskog.se/dyn-search.html for good discussions of catch-all queries.
November 8, 2022 at 3:10 pm
REPLACE(), & LOWER() are being used for matching in all cases.
ISNULL() can be ignored except in where conditions.
and These are the online plans with query
Newly created plan ( with query) :
https://www.brentozar.com/pastetheplan/?id=HkPsA1dBj
First plan :
November 9, 2022 at 5:32 am
You are creating a "Catch-All" query. The pattern used in your WHERE clause is non-sargeable, forcing SQL to do a full scan.
With the following pattern,
AND (ISNULL(@email,'') = '' OR r.[Email] = ISNULL(@email,''))
SQL has to validate the ISNULL(@email,'') = '' part against every record.
Here are 3 articles by Gail Shaw on this pattern
https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
https://www.sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/
November 9, 2022 at 8:21 am
Thank you
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) .
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?
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.
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 (@Take * @Skip) ROWS
FETCH NEXT @Take ROWS ONLY OPTION (RECOMPILE)**
November 14, 2022 at 1:21 am
REPLACE(), & LOWER() are being used for matching in all cases.
You asked for help with performance... those are performance issues that need to be fixed... "in all cases" when used as criteria.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply