Improve OR inside INNER JOINHow 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
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;
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
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
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
edited May 28 at 14:57
Erik Darling
24.1k1375123
asked May 28 at 13:06
Racer SQLRacer SQL
3,17742868
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
sql-server sql-server-2008-r2 performance
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
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
edited May 28 at 14:57
Erik Darling
24.1k1375123
24.1k1375123
asked May 28 at 13:06
Racer SQLRacer SQL
3,17742868
asked May 28 at 13:06
Racer SQLRacer SQL
3,17742868
asked May 28 at 13:06
Racer SQLRacer SQL
3,17742868
3,17742868
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
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
);
);
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
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
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
answered May 28 at 15:02
Erik DarlingErik Darling
24.1k1375123
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
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
answered May 28 at 15:02
Erik DarlingErik Darling
24.1k1375123
24.1k1375123
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
