Hcfyk

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.

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