Improve OR inside INNER JOIN

Question

I have this query. this is the second query. the first one was using the left/or outside the subquery, and the query plan was horrible.( Edited the question with the full sintax ):

https://www.brentozar.com/pastetheplan/?id=HJEioh56N

I have a nested loop (inner join) with 97% in the query plan.

I'm sure the problem is the OR inside the second join because I changed something here and there and I could get rid of them but I would like to be sure what would be the best way to deal with data like these. also all those tables has millions of rows.

Table Definition:

CREATE TABLE [DBO].[TABLE1](
    [F1] [int] NOT NULL,
    [F1] [varchar](16) NOT NULL,
    [F3] [money] NOT NULL,
    [F4] [money] NOT NULL,

I know created this index:

CREATE NONCLUSTERED INDEX IX_TB1 ON DBO.TABLE1 ( F1, F2)

That index seek is now 14%, but got a HASH MATCH with 82% cost.

Answer 1

A common query rewrite that helps with OR predicates looks like this:

SELECT tTitulo.CdContaCartao,
       tTitulo.CdStatus,
       MAX(DiariaMaxima)
INTO   #DiariaMaxima
FROM   Sistema.Titulo AS tTitulo
CROSS APPLY
       (   
           SELECT MAX(DiariaMaxima)
               FROM (
               SELECT     tTIPM.DtDiaria
               FROM       Sistema.TaxaIndice_PagamentoMensal_ContaCartao AS tTIPM_Cartao
               INNER JOIN Sistema.TaxaIndice_PagamentoMensal AS tTIPM
                   ON tTIPM.CdTaxaIndice_PagamentoMensal = tTIPM_Cartao.CdTaxaIndice_PagamentoMensal
               WHERE      tTIPM_Cartao.CdContaCartao = tTitulo.CdContaCartao

               UNION ALL

               SELECT     tTIPM.DtDiaria
                          FROM       Sistema.TaxaIndice_PagamentoMensal_ContaCartao AS tTIPM_Cartao
                          INNER JOIN Sistema.TaxaIndice_PagamentoMensal AS tTIPM
                              ON tTIPM.CdTaxaIndice_PagamentoMensal = tTIPM_Cartao.CdTaxaIndice_PagamentoMensal
                          WHERE      tTIPM_Cartao.CdContaCartao = tTitulo.CdContaCartao_Visa
               ) AS x (DiariaMaxima)        
           ) AS DiariaMaxima (DiariaMaxima)

Apply is not always the best method for this, though I've often had success with it over using a regular derived JOIN.

Some background on similar problems here:

• Analysing A Query Plan
• How to Optimise Query