0

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.

here is the execution plan : enter image description here

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

dexon
  • 67
  • 1
  • 6

0 Answers0