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
Best Answer
You have a problem whenever you join using
ASOFDATE
, such as here:, because
ASOFDATE
is defined asdateadd( 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
.And then involve that in your join clause too.
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.