Otdfbt

Why is it bad to use your whole foot in rock climbing

Purpose of cylindrical attachments on Power Transmission towers

Am I allowed to determine tenets of my contract as a warlock?

How to soundproof the Wood Shop?

What's the difference between DHCP and NAT? Are they mutually exclusive?

Is there a frequency comparator device?

In The Incredibles 2, why does Screenslaver's name use a pun on something that doesn't exist in the 1950s pastiche?

Can I attach a DC blower to intake manifold of my 150CC Yamaha FZS FI engine?

Which are the methodologies for interpreting Vedas?

Can you open the door or die? v2

In American Politics, why is the Justice Department under the President?

Is it true that "only photographers care about noise"?

Print "N NE E SE S SW W NW"

If absolute velocity does not exist, how can we say a rocket accelerates in empty space?

A team managed by my peer is close to melting down

Placement of positioning lights on A320 winglets

Why would a home insurer offer a discount based on credit score?

What is the language spoken in Babylon?

Nth term of Van Eck Sequence

Why is my Taiyaki (Cake that looks like a fish) too hard and dry?

My mom's return ticket is 3 days after I-94 expires

When editor does not respond to the request for withdrawal

What does BREAD stand for while drafting?

How can calculate the turn-off time of an LDO?

Improve OR inside INNER JOIN

How to Optimise QueryAnalysing A Query PlanStream Aggregate for Sort?How to improve the execution plan to speed up the queryRow estimates always too lowSpeeding up COUNT(*) with multiple equality, in and join conditionsSQL Server chooses Nested Loop join with dimensional table and make seek for each rowSpecify build and probe tables to hash join?Strange query plan when using OR in JOIN clause - Constant scan for every row in tablewhy is this left join faster than an inner join?SQL Server expensive nested loops join and lazy table spoolForce using an Index in Inner Join SELECT

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

1

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:

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.

sql-server sql-server-2008-r2 performance

share|improve this question

edited May 28 at 14:57

Erik Darling

24.1k1375123

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

add a comment | 

2

1

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:

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.

sql-server sql-server-2008-r2 performance

share|improve this question

edited May 28 at 14:57

Erik Darling

24.1k1375123

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

add a comment | 

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:

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.

sql-server sql-server-2008-r2 performance

share|improve this question

edited May 28 at 14:57

Erik Darling

24.1k1375123

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

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

share|improve this question

edited May 28 at 14:57

Erik Darling

24.1k1375123

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

share|improve this question

edited May 28 at 14:57

Erik Darling

24.1k1375123

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

edited May 28 at 14:57

Erik Darling

24.1k1375123

edited May 28 at 14:57

Erik Darling

24.1k1375123

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

asked May 28 at 13:06

Racer SQLRacer SQL

3,17742868

1 Answer
1

active

oldest

votes

5

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

share|improve this answer

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f239210%2fimprove-or-inside-inner-join%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

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

share|improve this answer

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123

add a comment | 

5

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

share|improve this answer

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123

add a comment | 

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

share|improve this answer

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123

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)

share|improve this answer

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123

answered May 28 at 15:02

Erik DarlingErik Darling

24.1k1375123