Query Optimization for if exists sub query

Question

I am trying to optimize the query below

if exists (select 1 
           from GHUB_DISCREPANCY_REPORT (NOLOCK) 
           where PARTNO = @currentpn and orderID = @oldorderid + 1 
             and (Discr_Fox_Available = 'Y' 
                  or Discr_Fox_NC = 'Y' or Discr_FOC_Available = 'Y' 
                  or Discr_FOC_NC = 'Y' or Discr_Cpa_Available = 'Y' 
                  or Discr_Cpa_NC = 'Y' or Discr_Fox_Tot = 'Y' 
                  or Discr_FOC_Tot = 'Y' or Discr_Cpa_Tot = 'Y'))

I indexed the primary key, PartNo, Aging and OrderID columns.

Is there any other way I can optimize this query ?

Please suggest!

Answer 1

First, try an index on GHUB_DISCREPANCY_REPORT(PARTNO, orderId). This may be a big help for you query.

If you still have performance problems, one method is to use to separate queries, each of which can be optimized with a separate index.

if exists (select 1 
           from GHUB_DISCREPANCY_REPORT (NOLOCK) 
           where PARTNO = @currentpn and orderID = @oldorderid and Discr_Fox_Available = 'Y' 
          ) or
    . . .

And then having a separate composite index for each combination: GHUB_DISCREPANCY_REPORT(PARTNO, orderId, Discr_Fox_Available). This is a lot of index overhead, but could be worth it.

Another idea is to combine all the flags into one:

alter table GHUB_DISCREPANCY_REPORT 
    add Any_Flags as (case when (Discr_Fox_Available = 'Y' 
                  or Discr_Fox_NC = 'Y' or Discr_FOC_Available = 'Y' 
                  or Discr_FOC_NC = 'Y' or Discr_Cpa_Available = 'Y' 
                  or Discr_Cpa_NC = 'Y' or Discr_Fox_Tot = 'Y' 
                  or Discr_FOC_Tot = 'Y' or Discr_Cpa_Tot = 'Y' then 'Y' else 'N' end);

You can add an index on a computed column and then use the value in your query:

create index idx_GHUB_DISCREPANCY_REPORT_anyflags on GHUB_DISCREPANCY_REPORT(PARTNO, OrderId, AnyFlags);

if exists (select 1 
           from GHUB_DISCREPANCY_REPORT (NOLOCK) 
           where PARTNO = @currentpn and orderID = @oldorderid and AnyFlags = 'Y' 
          )