Query speed optimization
I have several tables that I'm trying to combine with the below query. I'm using the following tables:
LoanOrigination
: This contains loan characteristics such as asset value, loan term, etc. There is a single unique observation per loan. This table is indexed onLOAN_ID
,SUB_SAMPLE
andCOLLATERAL_TYPE
.
LoanPerformance
: This contains the performance of all loans inLoanOrigination
. Each row is a uniqueMONTHLY_REPORTING_PERIOD
andLOAN_ID
combination, and the table has been indexed on both.
CollateralData
: This contains historical values based on theCOLLATERAL_TYPE
. The purpose of this is to estimate what loan's current value to the collateral.
The purpose of the query below is to combine these tables so that each row contains loan characteristics along with the delinquency statuses in the current and following month. However, the query is extremely slow. Is there anything that can be done to speed it up?
with
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
from LoanData.CollateralData
),
SAMPLE_LOANS as (
select
a.*,
b.INDEX as INDEX_T0
from LoanData.LoanOrigination a
join COLLATERAL_VALUES b on b.ASOFDATE = a.ORIG_DATE and b.COLLATERAL_TYPE = a.COLLATERAL_TYPE
where SUB_SAMPLE = 0
),
LOAN_STATE as (
select
a.LOAN_ID,
MONTHLY_REPORTING_PERIOD AS CUR_DATE,
CURRENT_ACTUAL_UPB as CUR_UPB,
LOAN_AGE,
cast(CURRENT_LOAN_DELINQUENCY as smallint) AS CUR_DLQ_STATUS
from LoanData.LoanPerformance a
where
CURRENT_LOAN_DELINQUENCY <> 'XX' and
exists ( select LOAN_ID from SAMPLE_LOANS )
),
LOAN_TRANSITION as (
select
c.*,
a.CUR_DATE,
a.CUR_DLQ_STATUS,
a.CUR_UPB,
a.LOAN_AGE,
b.NEXT_DLQ_STATUS
from LOAN_STATE a
join (
-- adding next state transition
select
LOAN_ID,
DATEADD( month, -1, CUR_DATE ) as PRIOR_DATE,
CUR_DLQ_STATUS as NEXT_DLQ_STATUS
from LOAN_STATE
) b on a.LOAN_ID = b.LOAN_ID and a.CUR_DATE = b.PRIOR_DATE
join SAMPLE_LOANS c on a.LOAN_ID = c.LOAN_ID
)
select
a.*,
CUR_UPB / ( ASSET_VALUE * ( b.INDEX / a.INDEX_T0 )) AS LTV
from LOAN_TRANSITION a
join COLLATERAL_VALUES b on a.CUR_DATE = b.ASOFDATE and a.COLLATERAL_TYPE = b.COLLATERAL_TYPE
sql-server query-performance optimization
New contributor
add a comment |
I have several tables that I'm trying to combine with the below query. I'm using the following tables:
LoanOrigination
: This contains loan characteristics such as asset value, loan term, etc. There is a single unique observation per loan. This table is indexed onLOAN_ID
,SUB_SAMPLE
andCOLLATERAL_TYPE
.
LoanPerformance
: This contains the performance of all loans inLoanOrigination
. Each row is a uniqueMONTHLY_REPORTING_PERIOD
andLOAN_ID
combination, and the table has been indexed on both.
CollateralData
: This contains historical values based on theCOLLATERAL_TYPE
. The purpose of this is to estimate what loan's current value to the collateral.
The purpose of the query below is to combine these tables so that each row contains loan characteristics along with the delinquency statuses in the current and following month. However, the query is extremely slow. Is there anything that can be done to speed it up?
with
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
from LoanData.CollateralData
),
SAMPLE_LOANS as (
select
a.*,
b.INDEX as INDEX_T0
from LoanData.LoanOrigination a
join COLLATERAL_VALUES b on b.ASOFDATE = a.ORIG_DATE and b.COLLATERAL_TYPE = a.COLLATERAL_TYPE
where SUB_SAMPLE = 0
),
LOAN_STATE as (
select
a.LOAN_ID,
MONTHLY_REPORTING_PERIOD AS CUR_DATE,
CURRENT_ACTUAL_UPB as CUR_UPB,
LOAN_AGE,
cast(CURRENT_LOAN_DELINQUENCY as smallint) AS CUR_DLQ_STATUS
from LoanData.LoanPerformance a
where
CURRENT_LOAN_DELINQUENCY <> 'XX' and
exists ( select LOAN_ID from SAMPLE_LOANS )
),
LOAN_TRANSITION as (
select
c.*,
a.CUR_DATE,
a.CUR_DLQ_STATUS,
a.CUR_UPB,
a.LOAN_AGE,
b.NEXT_DLQ_STATUS
from LOAN_STATE a
join (
-- adding next state transition
select
LOAN_ID,
DATEADD( month, -1, CUR_DATE ) as PRIOR_DATE,
CUR_DLQ_STATUS as NEXT_DLQ_STATUS
from LOAN_STATE
) b on a.LOAN_ID = b.LOAN_ID and a.CUR_DATE = b.PRIOR_DATE
join SAMPLE_LOANS c on a.LOAN_ID = c.LOAN_ID
)
select
a.*,
CUR_UPB / ( ASSET_VALUE * ( b.INDEX / a.INDEX_T0 )) AS LTV
from LOAN_TRANSITION a
join COLLATERAL_VALUES b on a.CUR_DATE = b.ASOFDATE and a.COLLATERAL_TYPE = b.COLLATERAL_TYPE
sql-server query-performance optimization
New contributor
2
The problem you're likely to run into is that CTEs aren't materialized, so all those expressions you're joining and filtering on aren't likely to use indexes well, etc. You'd be better off using#temp
tables instead, most likely.
– sp_BlitzErik
7 hours ago
4
If you need more help after switching to#temp
tables, read this: Getting Help With A Slow Query.
– sp_BlitzErik
7 hours ago
2
An execution plan and/or some sample data & the table definition could also be of great help. You could share the plan via pastetheplan.com
– Randi Vertongen
7 hours ago
Please don't use aliases likea
andb
in real queries. Readability counts.
– jpmc26
2 hours ago
add a comment |
I have several tables that I'm trying to combine with the below query. I'm using the following tables:
LoanOrigination
: This contains loan characteristics such as asset value, loan term, etc. There is a single unique observation per loan. This table is indexed onLOAN_ID
,SUB_SAMPLE
andCOLLATERAL_TYPE
.
LoanPerformance
: This contains the performance of all loans inLoanOrigination
. Each row is a uniqueMONTHLY_REPORTING_PERIOD
andLOAN_ID
combination, and the table has been indexed on both.
CollateralData
: This contains historical values based on theCOLLATERAL_TYPE
. The purpose of this is to estimate what loan's current value to the collateral.
The purpose of the query below is to combine these tables so that each row contains loan characteristics along with the delinquency statuses in the current and following month. However, the query is extremely slow. Is there anything that can be done to speed it up?
with
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
from LoanData.CollateralData
),
SAMPLE_LOANS as (
select
a.*,
b.INDEX as INDEX_T0
from LoanData.LoanOrigination a
join COLLATERAL_VALUES b on b.ASOFDATE = a.ORIG_DATE and b.COLLATERAL_TYPE = a.COLLATERAL_TYPE
where SUB_SAMPLE = 0
),
LOAN_STATE as (
select
a.LOAN_ID,
MONTHLY_REPORTING_PERIOD AS CUR_DATE,
CURRENT_ACTUAL_UPB as CUR_UPB,
LOAN_AGE,
cast(CURRENT_LOAN_DELINQUENCY as smallint) AS CUR_DLQ_STATUS
from LoanData.LoanPerformance a
where
CURRENT_LOAN_DELINQUENCY <> 'XX' and
exists ( select LOAN_ID from SAMPLE_LOANS )
),
LOAN_TRANSITION as (
select
c.*,
a.CUR_DATE,
a.CUR_DLQ_STATUS,
a.CUR_UPB,
a.LOAN_AGE,
b.NEXT_DLQ_STATUS
from LOAN_STATE a
join (
-- adding next state transition
select
LOAN_ID,
DATEADD( month, -1, CUR_DATE ) as PRIOR_DATE,
CUR_DLQ_STATUS as NEXT_DLQ_STATUS
from LOAN_STATE
) b on a.LOAN_ID = b.LOAN_ID and a.CUR_DATE = b.PRIOR_DATE
join SAMPLE_LOANS c on a.LOAN_ID = c.LOAN_ID
)
select
a.*,
CUR_UPB / ( ASSET_VALUE * ( b.INDEX / a.INDEX_T0 )) AS LTV
from LOAN_TRANSITION a
join COLLATERAL_VALUES b on a.CUR_DATE = b.ASOFDATE and a.COLLATERAL_TYPE = b.COLLATERAL_TYPE
sql-server query-performance optimization
New contributor
I have several tables that I'm trying to combine with the below query. I'm using the following tables:
LoanOrigination
: This contains loan characteristics such as asset value, loan term, etc. There is a single unique observation per loan. This table is indexed onLOAN_ID
,SUB_SAMPLE
andCOLLATERAL_TYPE
.
LoanPerformance
: This contains the performance of all loans inLoanOrigination
. Each row is a uniqueMONTHLY_REPORTING_PERIOD
andLOAN_ID
combination, and the table has been indexed on both.
CollateralData
: This contains historical values based on theCOLLATERAL_TYPE
. The purpose of this is to estimate what loan's current value to the collateral.
The purpose of the query below is to combine these tables so that each row contains loan characteristics along with the delinquency statuses in the current and following month. However, the query is extremely slow. Is there anything that can be done to speed it up?
with
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
from LoanData.CollateralData
),
SAMPLE_LOANS as (
select
a.*,
b.INDEX as INDEX_T0
from LoanData.LoanOrigination a
join COLLATERAL_VALUES b on b.ASOFDATE = a.ORIG_DATE and b.COLLATERAL_TYPE = a.COLLATERAL_TYPE
where SUB_SAMPLE = 0
),
LOAN_STATE as (
select
a.LOAN_ID,
MONTHLY_REPORTING_PERIOD AS CUR_DATE,
CURRENT_ACTUAL_UPB as CUR_UPB,
LOAN_AGE,
cast(CURRENT_LOAN_DELINQUENCY as smallint) AS CUR_DLQ_STATUS
from LoanData.LoanPerformance a
where
CURRENT_LOAN_DELINQUENCY <> 'XX' and
exists ( select LOAN_ID from SAMPLE_LOANS )
),
LOAN_TRANSITION as (
select
c.*,
a.CUR_DATE,
a.CUR_DLQ_STATUS,
a.CUR_UPB,
a.LOAN_AGE,
b.NEXT_DLQ_STATUS
from LOAN_STATE a
join (
-- adding next state transition
select
LOAN_ID,
DATEADD( month, -1, CUR_DATE ) as PRIOR_DATE,
CUR_DLQ_STATUS as NEXT_DLQ_STATUS
from LOAN_STATE
) b on a.LOAN_ID = b.LOAN_ID and a.CUR_DATE = b.PRIOR_DATE
join SAMPLE_LOANS c on a.LOAN_ID = c.LOAN_ID
)
select
a.*,
CUR_UPB / ( ASSET_VALUE * ( b.INDEX / a.INDEX_T0 )) AS LTV
from LOAN_TRANSITION a
join COLLATERAL_VALUES b on a.CUR_DATE = b.ASOFDATE and a.COLLATERAL_TYPE = b.COLLATERAL_TYPE
sql-server query-performance optimization
sql-server query-performance optimization
New contributor
New contributor
New contributor
asked 7 hours ago
MrT
111
111
New contributor
New contributor
2
The problem you're likely to run into is that CTEs aren't materialized, so all those expressions you're joining and filtering on aren't likely to use indexes well, etc. You'd be better off using#temp
tables instead, most likely.
– sp_BlitzErik
7 hours ago
4
If you need more help after switching to#temp
tables, read this: Getting Help With A Slow Query.
– sp_BlitzErik
7 hours ago
2
An execution plan and/or some sample data & the table definition could also be of great help. You could share the plan via pastetheplan.com
– Randi Vertongen
7 hours ago
Please don't use aliases likea
andb
in real queries. Readability counts.
– jpmc26
2 hours ago
add a comment |
2
The problem you're likely to run into is that CTEs aren't materialized, so all those expressions you're joining and filtering on aren't likely to use indexes well, etc. You'd be better off using#temp
tables instead, most likely.
– sp_BlitzErik
7 hours ago
4
If you need more help after switching to#temp
tables, read this: Getting Help With A Slow Query.
– sp_BlitzErik
7 hours ago
2
An execution plan and/or some sample data & the table definition could also be of great help. You could share the plan via pastetheplan.com
– Randi Vertongen
7 hours ago
Please don't use aliases likea
andb
in real queries. Readability counts.
– jpmc26
2 hours ago
2
2
The problem you're likely to run into is that CTEs aren't materialized, so all those expressions you're joining and filtering on aren't likely to use indexes well, etc. You'd be better off using
#temp
tables instead, most likely.– sp_BlitzErik
7 hours ago
The problem you're likely to run into is that CTEs aren't materialized, so all those expressions you're joining and filtering on aren't likely to use indexes well, etc. You'd be better off using
#temp
tables instead, most likely.– sp_BlitzErik
7 hours ago
4
4
If you need more help after switching to
#temp
tables, read this: Getting Help With A Slow Query.– sp_BlitzErik
7 hours ago
If you need more help after switching to
#temp
tables, read this: Getting Help With A Slow Query.– sp_BlitzErik
7 hours ago
2
2
An execution plan and/or some sample data & the table definition could also be of great help. You could share the plan via pastetheplan.com
– Randi Vertongen
7 hours ago
An execution plan and/or some sample data & the table definition could also be of great help. You could share the plan via pastetheplan.com
– Randi Vertongen
7 hours ago
Please don't use aliases like
a
and b
in real queries. Readability counts.– jpmc26
2 hours ago
Please don't use aliases like
a
and b
in real queries. Readability counts.– jpmc26
2 hours ago
add a comment |
1 Answer
1
active
oldest
votes
You have a problem whenever you join using ASOFDATE
, such as here:
on a.CUR_DATE = b.ASOFDATE
, because ASOFDATE
is defined as dateadd( day, 1-day(AsOfDate), AsOfDate)
. To me this seems to mean “first of the month”, and hopefully there’s no time component involved.
So, I’d add a new column to the first CTE, called maybe AsOfDateOrig
.
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),
And then involve that in your join clause too.
on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)
It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.
Now do the same for everywhere else you join using that.
I’d consider doing the same for PRIOR_DATE
too, basically avoiding joins that involve an expression unless you have the inverse of it too.
Finally, look at your indexing strategy.
You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value)
, for example, and similar indexes on the other tables.
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
});
}
});
MrT is a new contributor. Be nice, and check out our Code of Conduct.
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%2f225817%2fquery-speed-optimization%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
You have a problem whenever you join using ASOFDATE
, such as here:
on a.CUR_DATE = b.ASOFDATE
, because ASOFDATE
is defined as dateadd( day, 1-day(AsOfDate), AsOfDate)
. To me this seems to mean “first of the month”, and hopefully there’s no time component involved.
So, I’d add a new column to the first CTE, called maybe AsOfDateOrig
.
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),
And then involve that in your join clause too.
on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)
It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.
Now do the same for everywhere else you join using that.
I’d consider doing the same for PRIOR_DATE
too, basically avoiding joins that involve an expression unless you have the inverse of it too.
Finally, look at your indexing strategy.
You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value)
, for example, and similar indexes on the other tables.
add a comment |
You have a problem whenever you join using ASOFDATE
, such as here:
on a.CUR_DATE = b.ASOFDATE
, because ASOFDATE
is defined as dateadd( day, 1-day(AsOfDate), AsOfDate)
. To me this seems to mean “first of the month”, and hopefully there’s no time component involved.
So, I’d add a new column to the first CTE, called maybe AsOfDateOrig
.
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),
And then involve that in your join clause too.
on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)
It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.
Now do the same for everywhere else you join using that.
I’d consider doing the same for PRIOR_DATE
too, basically avoiding joins that involve an expression unless you have the inverse of it too.
Finally, look at your indexing strategy.
You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value)
, for example, and similar indexes on the other tables.
add a comment |
You have a problem whenever you join using ASOFDATE
, such as here:
on a.CUR_DATE = b.ASOFDATE
, because ASOFDATE
is defined as dateadd( day, 1-day(AsOfDate), AsOfDate)
. To me this seems to mean “first of the month”, and hopefully there’s no time component involved.
So, I’d add a new column to the first CTE, called maybe AsOfDateOrig
.
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),
And then involve that in your join clause too.
on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)
It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.
Now do the same for everywhere else you join using that.
I’d consider doing the same for PRIOR_DATE
too, basically avoiding joins that involve an expression unless you have the inverse of it too.
Finally, look at your indexing strategy.
You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value)
, for example, and similar indexes on the other tables.
You have a problem whenever you join using ASOFDATE
, such as here:
on a.CUR_DATE = b.ASOFDATE
, because ASOFDATE
is defined as dateadd( day, 1-day(AsOfDate), AsOfDate)
. To me this seems to mean “first of the month”, and hopefully there’s no time component involved.
So, I’d add a new column to the first CTE, called maybe AsOfDateOrig
.
COLLATERAL_VALUES as (
select
COLLATERAL_TYPE,
dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
Value as INDEX
,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),
And then involve that in your join clause too.
on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)
It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.
Now do the same for everywhere else you join using that.
I’d consider doing the same for PRIOR_DATE
too, basically avoiding joins that involve an expression unless you have the inverse of it too.
Finally, look at your indexing strategy.
You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value)
, for example, and similar indexes on the other tables.
edited 47 mins ago
Paul White♦
48.8k14259414
48.8k14259414
answered 7 hours ago
Rob Farley
13.6k12447
13.6k12447
add a comment |
add a comment |
MrT is a new contributor. Be nice, and check out our Code of Conduct.
MrT is a new contributor. Be nice, and check out our Code of Conduct.
MrT is a new contributor. Be nice, and check out our Code of Conduct.
MrT is a new contributor. Be nice, and check out our Code of Conduct.
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f225817%2fquery-speed-optimization%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
2
The problem you're likely to run into is that CTEs aren't materialized, so all those expressions you're joining and filtering on aren't likely to use indexes well, etc. You'd be better off using
#temp
tables instead, most likely.– sp_BlitzErik
7 hours ago
4
If you need more help after switching to
#temp
tables, read this: Getting Help With A Slow Query.– sp_BlitzErik
7 hours ago
2
An execution plan and/or some sample data & the table definition could also be of great help. You could share the plan via pastetheplan.com
– Randi Vertongen
7 hours ago
Please don't use aliases like
a
andb
in real queries. Readability counts.– jpmc26
2 hours ago