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

Question

Post reply

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

mehmood

Old Hand

Points: 336

November 8, 2022 at 12:15 pm

#4110028

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

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 :

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

This topic was modified 2 years, 1 month ago by mehmood.
This topic was modified 2 years, 1 month ago by mehmood.

November 11, 2022 at 8:59 am

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1381 More actions · Answer 1

Duplicate of https://www.sqlservercentral.com/forums/topic/refinement-of-my-query-in-a-best-way-with-optimum-results-for-large-data