CODEWITHSUNDEEP
sql-serverstored-procedures
Craig
Craig

Reputation: 1225

SQL server stored procedure timeouts

The following stored procedure takes almost 2 minutes to run which is causing a time out. Both tables do have primary keys and indexes defined. Is there anyway I can improve the process time?

ALTER PROCEDURE [dbo].[dfc_rpt_validation]
 -- declare
    @region varchar(10) = null,
    @source varchar(10) = null
AS BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
select row_number() OVER(ORDER BY l.loan_code) as Id
    , l.region AS Region
    , l.source AS Source
    , l.loan_code_string AS CL_Loan_Code
    , l.loan_principal_amt AS CL_Loan_Principal_Amt
    , l.loan_amt_due AS CL_Loan_Amt_Due
    , l.date_originated AS CL_Origination_Date
    , l.StoreNumber AS CL_Store_Number

    , v.new_loan_code_string  AS FA_Loan_Code
    , v.loan_principal_amt AS FA_Loan_Principal_Amt
    , v.loan_amt_due AS FA_Loan_Amt_Due
    , v.loan_origination_date AS FA_Origination_Date

    , (select count(*) from [dbo].[dfc_LoanValidatedRecords] s WHERE s.loan_code_string = l.loan_code_string) AS FA_Times_Loan_Document_Processed

    , (CASE WHEN l.rejected_date IS NULL AND l.validated_date IS NULL THEN ''
        WHEN v.pdf_legible = 0 THEN 'operational reject' -- operational error
        WHEN v.loan_code_valid = 1
            AND v.loan_principal_amt_valid = 1
            AND v.loan_origination_date_valid = 1
            AND v.loan_amt_due_valid = 1
            --OR v.pdf_legible = 0  -- operational error
        THEN 'validated' ELSE 'rejected' END
        ) AS [FA_Verification_Status]
            -- 100 delivery method failure
            -- 200 pdf reject codes
            -- 400 borrower info reject codes
            -- 600 loan reject codes

            ,  LTRIM(RTRIM (
              --(CASE WHEN l.rejected_date IS NULL AND l.validated_date IS NULL THEN ''
                --ELSE
                (CASE WHEN v.pdf_legible                   = 0 THEN ' 200'
                 ELSE
                 (CASE WHEN v.loan_code_valid             = 0 THEN ' 600' ELSE '' END)
                + (CASE WHEN v.loan_principal_amt_valid    = 0 THEN ' 610' ELSE '' END)
                + (CASE WHEN v.loan_origination_date_valid = 0 THEN ' 620' ELSE '' END) -- LoanDate
                + (CASE WHEN v.loan_amt_due_valid          = 0 THEN ' 625' ELSE '' END)
                END)  -- operational error
              --END)
            )) AS FA_Reason

            , l.load_file AS load_file

from dfc_LoanRecords AS l
JOIN dfc_LoanValidatedRecords AS v ON v.loan_code_string = l.loan_code_string
WHERE CONVERT (DATE, l.load_date) >= convert(date, dateadd(hh,-8,getdate())) -- handle UTC conversion
        AND l.region = @region AND l.source = @source
ORDER BY FA_Verification_Status, FA_Reason
END

Upvotes: 0

Views: 76

Answers (2)

jthalliens
jthalliens

Reputation: 534

Wouldn't be better if instead of converting the Convert(date, dateadd(hh,-8,getdate())) in the query itself to avoid that function for every record

DECLARE @Date DATE = CAST(DATEADD(HH,-8,GETDATE()) AS DATE)

WHERE CONVERT (DATE, l.load_date) >=  @Date -- handle UTC conversion
    AND l.region = @region AND l.source = @source

With this you are going to get a better execution plan instead of converting the value for every single row.

Also if you can apply the ORDER BY at the application layer... please read 7 Things Developers Should Know About SQL Server item 2 please do so, there's no need to use it on the database side unless we have to, it will reduce extra-cost on tempdb.

Upvotes: 0

Gareth Lyons
Gareth Lyons

Reputation: 1972

Simplest change I can see: There's no reason I can see for this: CONVERT (DATE, l.load_date). Just use l.load_date >= convert(date, dateadd(hh,-8,getdate()))

This should enable SQL query optimiser to use any indexes that might exist on load_date. At the moment it can't do so efficiently as you're using a function on the column.

Upvotes: 1

Related Questions