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
5
  • 1
    since you have to different calls (with MainId and without MainId) you need to have two different queries. To make things worse you have to handle parameter sniffing so two different stored procedures with similar (duplicate) code could be the best choice. You can hide something (tables and join conditions) in the view
    – SergeyA
    Commented Mar 27 at 17:48
  • Please add the actual execution plan to your post via Paste The Plan
    – J.D.
    Commented Mar 27 at 17:48
  • 2
    Just stick an OPTION(RECOMPILE); hint on it. Commented Mar 27 at 18:23
  • WITH (NOLOCK) I hope you really really really know what it does because it's a Very Bad Idea. Commented Mar 28 at 23:02
  • thanks Erik- as this procedure is running about 500 times a minute, i decided just to add Option (RECOMPILE) and it works. I didn't want to add an additional work on two stored procedures. many thanks
    – dexon
    Commented Apr 1 at 11:36

0