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.
TopAnswers
Databases
Top Answer
meme (imported from SE)
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