Improve OR inside INNER JOINHow to Optimise QueryAnalysing A Query Planquery performance gains by removing operator hash match inner joinTuning a query with temp table joinHow to improve the execution plan to speed up the queryRow estimates always too lowSQL 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?
Implementation of A* algorithm in C++
How would a race of humanoids with tails design [vehicle] seats?
Why didn't Petunia know that Harry wasn't supposed to use magic out of school?
Which person is telling the truth?
Is my translation correct?
What do you do when brand new pads are rubbing on new rotor? It smokes and smells so bad and it seems like it is going to catch on fire
According to Book of Exalted Deeds, how to choose between two evils?
Is this really played by 2200+ players?
Teaching asymptotic notations at the beginning of Calculus
Remove x last elements of an array and reinsert them before position y
Why apt asking to uninstall GIMP when installing ardour?
How to include conditional statements in NIntegrate?
How to test a complex method (without rewriting it in the test)?
Is success due to hard work sustainable in academic research?
What is an idiom, phrase or expression for situation such as "throw a pigeon among cats"
Would fantasy dwarves be able to invent and/or manufacture the radio?
Calculating the required motor for a mobility scooter
Matrix class in C#
When and why did the House rules change to permit an inquiry without a vote?
How do social media apps notify you when someone else takes a screenshot of your profile?
What plausible reasons why people forget they didn't originally live on this new planet?
How do I redirect a subdomain with a wildcard?
As a vegetarian, how can I deal with microwaves smelling of meat and fish?
Modeling the Round (Nearest Integer) function
Improve OR inside INNER JOIN
How to Optimise QueryAnalysing A Query Planquery performance gains by removing operator hash match inner joinTuning a query with temp table joinHow to improve the execution plan to speed up the queryRow estimates always too lowSQL 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?
.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,
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
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
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,
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
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
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,
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
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
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.
sql-server sql-server-2008-r2 performance
sql-server sql-server-2008-r2 performance
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
edited Jun 14 at 16:39
Racer SQL
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
asked May 28 at 13:06
Racer SQLRacer SQL
3,5315 gold badges31 silver badges75 bronze badges
3,5315 gold badges31 silver badges75 bronze badges
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
28k13 gold badges86 silver badges143 bronze badges
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/4.0/"u003ecc by-sa 4.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
28k13 gold badges86 silver badges143 bronze badges
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
28k13 gold badges86 silver badges143 bronze badges
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
28k13 gold badges86 silver badges143 bronze badges
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
28k13 gold badges86 silver badges143 bronze badges
answered May 28 at 15:02
Erik DarlingErik Darling
28k13 gold badges86 silver badges143 bronze badges
answered May 28 at 15:02
Erik DarlingErik Darling
28k13 gold badges86 silver badges143 bronze badges
answered May 28 at 15:02
Erik DarlingErik Darling
28k13 gold badges86 silver badges143 bronze badges
28k13 gold badges86 silver badges143 bronze badges
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
