question

shruti2885 avatar image
shruti2885 asked

Query performance - Creating Indexes

Here is my query select loan_no ,dbo.fxGetScoreValue_PROD(loan_no,1,1) --- 328,862 ran in 6 min 48 seconds ,dbo.fxGetScoreValue_PROD(loan_no,1,2) ,dbo.fxGetScoreValue_PROD(loan_no,1,3) ,dbo.fxGetScoreValue_PROD(loan_no,2,1) ,dbo.fxGetScoreValue_PROD(loan_no,3,1) ,dbo.fxGetScoreValue_PROD(loan_no,4,1) ,dbo.fxGetScoreValue_PROD(loan_no,5,1) from REPL_APM_PROD.dbo.loan WHERE datediff(dd,loan.last_event_dtm, getdate()) < 60 **THE FUNCTION IS** Begin declare @ReturnValue Decimal(18,4) set @ReturnValue = (select Top 1 csm_var_initial from REPL_APM_PROD..loan_score_results results Inner Join REPL_APM_PROD..loan_score_detail detail on results.loan_no = detail.loan_no and results.score_id = detail.score_id and results.csm_id = detail.csm_id WHERE current_flg = 'Y' AND results.loan_no = @Loan_no AND results.csm_id = @csm_id AND detail.csm_var_id = @csm_var_id) return @ReturnValue End What indexes should I create ??
queryquery-analyzerindex-performance
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
annitips avatar image
annitips answered
May be you can create a clustered index on those 3 Ids that you are referring in your JOIN.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.