Sql-server – Hash Match inner join in simple query with in statement

execution-plansql servertuning

I am running the execution plan for the following query:

select m_uid from EmpTaxAudit 
where clientid = 91682 
and  empuid = 42100176452603
and newvalue in('Deleted','DB-Deleted','Added')

Here is the execution plan:

Execution Plan

I have a non clustered index on EmpTaxAudit Table on ClientId and NewValue columns which shows above as 14.9% of the execution:

CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].

[EmpTaxAudit]
(
    [ClientID] ASC,
    [NewValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I also have a non clustered unique index PK as follows:

ALTER TABLE [dbo].[EmpTaxAudit] ADD  CONSTRAINT [PK_EmpTaxAudit] PRIMARY KEY NONCLUSTERED 
(
    [ClientID] ASC,
    [EmpUID] ASC,
    [m_uid] ASC,
    [m_eff_start_date] ASC,
    [ReplacedOn] ASC,
    [ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Trigger code in source table EmpTax:

CREATE                trigger [dbo].[trins_EmpTax]
on [dbo].[emptax]
for insert
as
begin
  declare 
    @intRowCount  int,
      @user          varchar(30)

  select @intRowCount = @@RowCount
  IF @intRowCount > 0 
  begin
     select @user = suser_sname()

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Added'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit 
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is null 

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Deleted'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit 
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is not null 


  end
end

What can I do to avoid the high cost of Hash Match (Inner Join)?

Thanks!

Best Answer

For the 1st query, an index that uses all three columns from the WHERE clause and includes the column from the SELECT list would be much more useful:

-- index suggestion A
(clientid, empuid, newvalue) INCLUDE (m_uid)

or an index targeted specifically for this query:

-- index suggestion B
(clientid, empuid, m_uid)
WHERE newvalue in ('Deleted', 'DB-Deleted', 'Added')

Regarding the trigger, some comments:

  • The first query you show does not appear in the trigger. What appears is a join from that table to the inserted rows to another table (which has the trigger).
  • My suggestion B above seems better suited to be used by the trigger.

  • The trigger has 2 almost identical insert statements. Why? I think they could be combined in one - and simpler - insert and using NOT EXISTS instead of NOT IN:

    insert EmpTaxAudit 
  ( Clientid, empuid, m_uid, m_eff_start_date, ColumnName, 
    ReplacedOn, ReplacedBy, OldValue, dblogin, 
    newvalue
  )
select 
    Clientid, empuid, m_uid, m_eff_start_date, 'taxcode',
    getdate(), IsNull(userid,@user), '', Left(@user,15),
    case when m_eff_end_date is null 
        then 'Added' else 'Deleted'
    end
from inserted i
where not exists 
      ( select 1 
        from EmpTaxAudit 
        where m_uid = i.m_uid 
          and clientid = i.clientid  
          and empuid = i.empuid
          and newvalue in ('Deleted', 'DB-Deleted', 'Added')
      ) ;
Related Question