or
racer sql imported from SE
sql-server sql-server-2008-r2
I have [this query plan](https://www.brentozar.com/pastetheplan/?id=HJEioh56N). It shows a *nested loop (inner join)* with 97% cost.

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 it, but I would like to be sure what would be the best way to deal with data like these. These tables have 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 created this index:

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

The index seek is now 14% cost, but got a *Hash Join* with 82% cost.
Top Answer
Erik Darling
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
        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
        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 join.

Some background on similar problems here:

- [Analysing A Query Plan][1]
- [How to Optimise Query][2]


  [1]: https://dba.stackexchange.com/questions/23773/analysing-a-query-plan
  [2]: https://dba.stackexchange.com/questions/23766/how-to-optimise-query
Improve OR inside INNER JOIN

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.