What indexes am I missing

Shruti2885 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

/storage/attachments/1543-Plan.queryanalysis
RichardBrianSmith 2016-03-30 14:34:58
Please supply more info on index "IDX_loan_last_event_date" (on dbo.loan).
RichardBrianSmith 2016-04-01 15:28:18
The index looks fine. You could add OPTION (RECOMPILE) to fix cardinality estimations.
SQLkiwi 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.

March 28, 2016
No Comments