What indexes am I missing
2016-03-28 20:20:51
USE [REPL_APM_PROD]
GO
CREATE NONCLUSTERED INDEX [IDX_loan_last_event_date] ON [dbo].[loan]
(
[loan_no] ASC
)
INCLUDE ( [last_event_dtm]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
GO
CREATE NONCLUSTERED INDEX [IDX_loan_last_event_date] ON [dbo].[loan]
(
[loan_no] ASC
)
INCLUDE ( [last_event_dtm]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
RichardBrianSmith 2016-03-30 14:34:58
Please supply more info on index "IDX_loan_last_event_date" (on dbo.loan).
2016-04-01 15:28:18
The index looks fine. You could add OPTION (RECOMPILE) to fix cardinality estimations.
2016-04-02 18:53:31
The biggest problem is likely your use of a scalar user-defined function. The query behind the function is fully executed for each row presented to it. Try the query without the function:
SELECT L.loan_no FROM dbo.loan AS L CROSS APPLY ( SELECT TOP (1) -- needs an ORDER BY clause at the end! csm_var_initial -- needs an alias or table name! FROM dbo.loan_score_results AS R JOIN dbo.loan_score_detail AS D ON R.loan_no = D.loan_no AND R.score_id = D.score_id AND R.csm_id = D.csm_id WHERE current_flg = 'Y' -- needs an alias or table name! AND R.loan_no = L.loan_no AND R.csm_id = 1 AND D.csm_var_id = 1 ) WHERE L.last_event_dtm >= DATEADD(DAY, 60, GETDATE());
Note the comments there, and the rearrangement of the WHERE clause to be in a form that can use an index. An index with last_event_dtm as the key, and loan_no as a key or included column would be useful, for example:
CREATE INDEX index_name ON dbo.loan (last_event_dtm, loan_no);
If you must use a function, the APPLY clause above can be straightforwardly converted to an in-line TVF. This will perform the same as the fully-inline query above. Scalar functions are to be avoided.