How can I refine my query in a best way with optimum results (for large database tables) ?
See the tables structure and the query 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
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 plan is mentioned below:
https://www.brentozar.com/pastetheplan/?id=HJlN1xuHi
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:
