Return to 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,
    

Im sure the problem is here: [![enter image description here][1]][1] [1]: https://i.sstatic.net/ieMgK.png

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.

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.

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 [Sistema].[TaxaIndice_PagamentoMensal_ContaCartao](
    [CdTaxaIndice_PagamentoMensal] [int] NOT NULL,
    [CdContaCartao] [varchar](16) NOT NULL,
    [SomatorioPagamento] [money] NOT NULL,
    [SomatorioFaturamento] [money] NOT NULL,
    [IndicePagamento]  AS (case when [SomatorioFaturamento]=(0) then NULL else [SomatorioPagamento]/[SomatorioFaturamento] end),
 CONSTRAINT [PK_Sistema.TaxaIndice_PagamentoMensal_ContaCartao - "CdTaxaIndice_PagamentoMensal", "cdContaCartao"] PRIMARY KEY CLUSTERED 
(
    [CdTaxaIndice_PagamentoMensal] ASC,
    [CdContaCartao] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Sistema].[TaxaIndice_PagamentoMensal_ContaCartao]  WITH CHECK ADD  CONSTRAINT [FK_Sistema.TaxaIndice_PagamentoMensal_ContaCartao - "CdTaxaIndice_PagamentoMensal"] FOREIGN KEY([CdTaxaIndice_PagamentoMensal])
REFERENCES [Sistema].[TaxaIndice_PagamentoMensal] ([CdTaxaIndice_PagamentoMensal])
GO

ALTER TABLE [Sistema].[TaxaIndice_PagamentoMensal_ContaCartao] CHECK CONSTRAINT [FK_Sistema.TaxaIndice_PagamentoMensal_ContaCartao - "CdTaxaIndice_PagamentoMensal"]
GO

ALTER TABLE [Sistema].[TaxaIndice_PagamentoMensal_ContaCartao]  WITH CHECK ADD  CONSTRAINT [CK_Sistema.TaxaIndice_PagamentoMensal_ContaCartao - "CdContaCartao" diferente de Branco] CHECK  (([CdContaCartao]<>''))
GO

ALTER TABLE [Sistema].[TaxaIndice_PagamentoMensal_ContaCartao] CHECK CONSTRAINT [CK_Sistema.TaxaIndice_PagamentoMensal_ContaCartao - "CdContaCartao" diferente de Branco]

Im sure the problem is here: [![enter image description here][1]][1] [1]: https://i.sstatic.net/ieMgK.png

I know created this index:

CREATE NONCLUSTERED INDEX DBAIX_TaxaIndice_PagamentoMensal_Conta ON Sistema.TaxaIndice_PagamentoMensal_ContaCartao ( CdContaCartao, CdTaxaIndice_PagamentoMensal )

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

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,
    

Im sure the problem is here: [![enter image description here][1]][1] [1]: https://i.sstatic.net/ieMgK.png

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.