question : how do i use the index seek in this code, without doing code duplication?
I've got this code :
CREATE procedure [dbo].[GetActiveEntitiesByRecord]
( @MainId bigint
, @SystemRecordId UNIQUEIDENTIFIER
, @systemEntityId UNIQUEIDENTIFIER = null
)
as
begin
select e.Id
, e.SystemEntityId
, e.SystemRecordId
, e.FirstMiddleName
, e.EntityTypeId
, e.LastName
, e.OrgName
, e.EntityStatusId
, rba.SystemEntityBankRecordId
, rba.RegionId
, rba.AccountNumber
, rba.BankId
, rba.SortingCode
, rba.BankRecordNo
, rba.RecordDescription
, rba.EntityBankRecordStatusId
, rba.CountryId
, CASE WHEN tp.Id IS NOT NULL THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END AS HasHistory
from DigitalWallet.Entities e with (nolock)
join DigitalWallet.Records r with (nolock) on r.SystemRecordId = e.SystemRecordId
join DigitalWallet.EntityBankRecords rba with (nolock) on e.Id = rba.EntityId
OUTER APPLY (SELECT TOP 1 Id from DigitalWallet.OutgoingTransfers tp with (nolock)
WHERE tp.EntityBankRecordId = rba.Id)tp
WHERE e.SystemRecordId = ISNULL(@SystemRecordId, e.SystemRecordId)
and e.EntityStatusId=1
and rba.EntityBankRecordStatusId=1
and e.SystemEntityId = ISNULL(@systemEntityId, e.SystemEntityId)
and r.MainId = ISNULL(@MainId, r.MainId)
end
due to this line : r.MainId = ISNULL(@MainId, r.MainId) it's doing index scan instead of index seek so it's essentially an OR clause.
due to security reason i can't share the full xml plan and the columns and values have changed.
I want to use the index, but the only solution i found to doing this is this and i find it's bad code due to duplication :
CREATE PROCEDURE [FiatWallet].[GetActivePayeesByAccount]
( @MainId BIGINT
, @SystemRecordId UNIQUEIDENTIFIER
, @systemEntityId UNIQUEIDENTIFIER = NULL
)
AS
BEGIN
IF (@MainId IS NOT NULL)
BEGIN
SELECT e.Id
, e.SystemEntityId
, e.SystemRecordId
, e.FirstMiddleName
, e.EntityTypeId
, e.LastName
, e.OrgName
, e.EntityStatusId
, rba.SystemEntityBankRecordId
, rba.RegionId
, rba.AccountNumber
, rba.BankId
, rba.SortingCode
, rba.BankRecordNo
, rba.RecordDescription
, rba.EntityBankRecordStatusId
, rba.CountryId
, CASE WHEN tp.Id IS NOT NULL THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END AS HasHistory
FROM DigitalWallet.Entities e WITH (NOLOCK)
JOIN DigitalWallet.Records r WITH (NOLOCK) ON r.SystemRecordId = e.SystemRecordId
JOIN DigitalWallet.EntityBankRecords rba WITH (NOLOCK) ON e.Id = rba.EntityId
OUTER APPLY (
SELECT TOP 1 Id
FROM DigitalWallet.OutgoingTransfers tp WITH (NOLOCK)
WHERE tp.EntityBankRecordId = rba.Id
) tp
WHERE e.SystemRecordId = ISNULL(@SystemRecordId, e.SystemRecordId)
AND e.EntityStatusId = 1
AND rba.EntityBankRecordStatusId = 1
AND e.SystemEntityId = ISNULL(@systemEntityId, e.SystemEntityId)
AND r.MainId = @MainId
END
ELSE
BEGIN
SELECT e.Id
, e.SystemEntityId
, e.SystemRecordId
, e.FirstMiddleName
, e.EntityTypeId
, e.LastName
, e.OrgName
, e.EntityStatusId
, rba.SystemEntityBankRecordId
, rba.RegionId
, rba.AccountNumber
, rba.BankId
, rba.SortingCode
, rba.BankRecordNo
, rba.RecordDescription
, rba.EntityBankRecordStatusId
, rba.CountryId
, CASE WHEN tp.Id IS NOT NULL THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END AS HasHistory
FROM DigitalWallet.Entities e WITH (NOLOCK)
JOIN DigitalWallet.Records r WITH (NOLOCK) ON r.SystemRecordId = e.SystemRecordId
JOIN DigitalWallet.EntityBankRecords rba WITH (NOLOCK) ON e.Id = rba.EntityId
OUTER APPLY (
SELECT TOP 1 Id
FROM DigitalWallet.OutgoingTransfers tp WITH (NOLOCK)
WHERE tp.EntityBankRecordId = rba.Id
) tp
WHERE e.SystemRecordId = ISNULL(@SystemRecordId, e.SystemRecordId)
AND e.EntityStatusId = 1
AND rba.EntityBankRecordStatusId = 1
AND e.SystemEntityId = ISNULL(@systemEntityId, e.SystemEntityId)
END
END
