Ad

Tweaking Stored Procedure For High Performance Across 300,000+ Records

I am trying to build a T-SQL stored procedure for use with a jQuery DataTables front-end API. The front-end UI provides the calling procedure with the orderby, filter, display starting row, and display length as with the arguments you will see below.

Is there anything I am missing in the below query to improve performance?

I need all of the columns to be displayed to the end user but I only need the rows supplied in the @NumberOfRowsToTake argument.

Thank you for your time.

CREATE PROCEDURE [dbo].[usp_Bank_GetAccounts_Paged]
    @SortDirection VARCHAR(4) = 'ASC'
    ,@SortColumn VARCHAR(50) = 'Lead_ID'
    ,@StartingRow INT = 1
    ,@NumOfRowsToTake INT = 5
    ,@Lead_ID INT = NULL
    ,@Account_ID BIGINT = NULL
    ,@Organization VARCHAR(100) = NULL
    ,@LeadNumber VARCHAR(20) = NULL
    ,@Source VARCHAR(50) = NULL
    ,@LeadReceivedDateBeg DATE = NULL
    ,@LeadReceivedDateEnd DATE = NULL
    ,@CalenderYear INT = NULL
    ,@RefundAvailableForRecoveryLowest MONEY = NULL
    ,@RefundAvailableForRecoveryHighest MONEY = NULL
    ,@ReasonForReferral VARCHAR(255) = NULL
    ,@CustomerNumber VARCHAR(11) = NULL
    ,@AccountLastName VARCHAR(20) = NULL
    ,@AccountFirstName VARCHAR(20) = NULL
    ,@NameOnRefundCheck VARCHAR(40) = NULL
    ,@Address VARCHAR(80) = NULL
    ,@City VARCHAR(20) = NULL
    ,@State VARCHAR(10) = NULL
    ,@ZipCode VARCHAR(10) = NULL
    ,@RTN VARCHAR(10) = NULL
    ,@ACCTNumber VARCHAR(20) = NULL
    ,@DebitCardNumber VARCHAR(20) = NULL
    ,@RefundClaimedLowest MONEY = NULL
    ,@RefundClaimedHighest MONEY = NULL
    ,@TreasuryCheckNumber VARCHAR(15) = NULL
    ,@Results VARCHAR(15) = NULL
    ,@SuspenseStatus VARCHAR(20) = NULL
    ,@FundsRequestedOrgLowest MONEY = NULL
    ,@FundsRequestedOrgHighest MONEY = NULL
    ,@FundsReturnedCustomerLowest MONEY = NULL
    ,@FundsReturnedCustomerHighest MONEY = NULL
    ,@VoucherOrMemoApplied VARCHAR(8) = NULL
    ,@Current_User_Name VARCHAR(5) = NULL
    ,@AssigneeFirstName VARCHAR(25) = NULL
    ,@AssigneeLastName VARCHAR(30) = NULL
    ,@AssigneeFullName VARCHAR(64) = NULL
    ,@Assignee_Site VARCHAR(10) = NULL
    ,@Current_Status_ID SMALLINT = NULL
    ,@Status_Name VARCHAR(20) = NULL
    ,@comment VARCHAR(255) = NULL
    ,@OriginalDepositDateBeg DATE = NULL
    ,@OriginalDepositDateEnd DATE = NULL
    ,@OriginalDepositAmountLowest MONEY = NULL
    ,@OriginalDepositAmountHighest MONEY = NULL
AS
BEGIN
    ; WITH ACCOUNTS AS 
    (
        SELECT TOP 99.9999999 PERCENT 
            [A].[Lead_ID]
            ,[A].[Account_ID]
            ,[L].[Organization]
            ,[L].[Lead_Number]
            ,[L].[Source]
            ,[L].[Lead_Received_Date]
            ,[A].[Calender_Year]
            ,[A].[Amount_To_Recover]
            ,[A].[Reason_For_Referral]
            ,[A].[CustomerNumber]
            ,[A].[Last_Name] AS [AccountLastName]
            ,[A].[First_Name] AS [AccountFirstName]
            ,[A].[Name_On_Refund]
            ,[A].[Address]
            ,[A].[City]
            ,[A].[State]
            ,[A].[Zip_Code]
            ,[A].[RTN]
            ,[A].[Audit_Number]
            ,[A].[Scan_Number]
            ,[A].[Amount_Claimed]
            ,[A].[Building_Number]
            ,[A].[Results]
            ,[A].[Date_Voucher_Sent]
            ,[A].[Return_ID_Number]
            ,[A].[Suspense_Status]
            ,[A].[Funds_Requested]
            ,[A].[Funds_Actually_Given]
            ,[A].[Voucher_or_Memo_Applied]
            ,[U].[SEID] AS [Current_User_Name]
            ,[U].[First_Name] AS [AssigneeFirstName]
            ,[U].[Last_Name] AS [AssigneeLastName]
            ,[U].[Last_Name] + ', ' + [U].[First_Name] + '(' + [U].[SEID] + ')' AS [AssigneeFullName]
            ,[U].[Site] AS [Assignee_Site]
            ,[A].[Current_Status_ID]
            ,[S].[Status_Name]
            ,[A].[Deleted_Date]
            ,[A].[comment]
            ,[A].[Original_Deposit_Date]
            ,[A].[Original_Deposit_Amount]
            ,ROW_NUMBER() OVER (
                ORDER BY CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Lead_ID' THEN [A].[Lead_ID] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Lead_ID' THEN [A].[Lead_ID] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Account_ID' THEN [A].[Account_ID] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Account_ID' THEN [A].[Account_ID] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Organization' THEN [L].[Organization] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Organization' THEN [L].[Organization] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Lead_Number' THEN [L].[Lead_Number] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Lead_Number' THEN [L].[Lead_Number] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Source' THEN [L].[Source] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Source' THEN [L].[Source] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Lead_Received_Date' THEN [L].[Lead_Received_Date] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Lead_Received_Date' THEN [L].[Lead_Received_Date] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Calender_Year' THEN [A].[Calender_Year] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Calender_Year' THEN [A].[Calender_Year] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Amount_To_Recover] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Amount_To_Recover] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Reason_For_Referral] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Reason_For_Referral] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'CustomerNumber' THEN [A].[CustomerNumber] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'CustomerNumber' THEN [A].[CustomerNumber] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'AccountLastName' THEN [A].[Last_Name] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'AccountFirstName' THEN [A].[First_Name] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Name_On_Refund' THEN [A].[Name_On_Refund] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Name_On_Refund' THEN [A].[Name_On_Refund] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Address' THEN [A].[Address] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Address' THEN [A].[Address] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'City' THEN [A].[City] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'City' THEN [A].[City] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'State' THEN [A].[State] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'State' THEN [A].[State] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Zip_Code' THEN [A].[Zip_Code] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Zip_Code' THEN [A].[Zip_Code] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'RTN' THEN [A].[RTN] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'RTN' THEN [A].[RTN] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Audit_Number' THEN [A].[Audit_Number] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Audit_Number' THEN [A].[Audit_Number] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Scan_Number' THEN [A].[Scan_Number] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Scan_Number' THEN [A].[Scan_Number] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Amount_Claimed' THEN [A].[Amount_Claimed] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Amount_Claimed' THEN [A].[Amount_Claimed] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Results' THEN [A].[Results] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Results' THEN [A].[Results] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Date_Voucher_Sent' THEN [A].[Date_Voucher_Sent] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Date_Voucher_Sent' THEN [A].[Date_Voucher_Sent] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Return_ID_Number' THEN [A].[Return_ID_Number] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Return_ID_Number' THEN [A].[Return_ID_Number] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Suspense_Status' THEN [A].[Suspense_Status] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Suspense_Status' THEN [A].[Suspense_Status] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Funds_Requested' THEN [A].[Funds_Requested] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Funds_Requested' THEN [A].[Funds_Requested] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Funds_Actually_Given' THEN [A].[Funds_Requested] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Funds_Actually_Given' THEN [A].[Funds_Requested] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Voucher_or_Memo_Applied' THEN [A].[Voucher_or_Memo_Applied] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Voucher_or_Memo_Applied' THEN [A].[Voucher_or_Memo_Applied] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Current_User_Name' THEN [U].[SEID] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Current_User_Name' THEN [U].[SEID] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'AssigneeFirstName' THEN [U].[First_Name] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'AssigneeFirstName' THEN [U].[First_Name] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'AssigneeLastName' THEN [U].[Last_Name] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'AssigneeLastName' THEN [U].[Last_Name] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Assignee_Site' THEN [U].[Site] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Assignee_Site' THEN [U].[Site] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Current_Status_ID' THEN [A].[Current_Status_ID] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Current_Status_ID' THEN [A].[Current_Status_ID] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Status_Name' THEN [S].[Status_Name] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Status_Name' THEN [S].[Status_Name] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Deleted_Date' THEN [A].[Deleted_Date] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Deleted_Date' THEN [A].[Deleted_Date] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'comment' THEN [A].[comment] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'comment' THEN [A].[comment] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Original_Deposit_Date' THEN [A].[Original_Deposit_Date] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Original_Deposit_Date' THEN [A].[Original_Deposit_Date] END DESC
                    ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Original_Deposit_Amount' THEN [A].[Original_Deposit_Amount] END ASC
                    ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Original_Deposit_Amount' THEN [A].[Original_Deposit_Amount] END DESC
                ) AS [ROWNUM]
        FROM
            [dbo].[Bank_Account] AS [A] WITH (NOLOCK)
        INNER JOIN 
            [dbo].[Bank_Lead] AS [L] WITH (NOLOCK) ON [A].[Lead_ID] = [L].[Lead_ID]
        LEFT JOIN 
            [dbo].[Bank_ref_Status] AS [S] WITH (NOLOCK) ON [A].[Current_Status_ID] = [S].[Status_ID]
        LEFT JOIN 
            [dbo].[Bank_Assignee] AS [U] WITH (NOLOCK) ON [A].[Current_User_Name] = [U].[SEID]
        WHERE 
            (@Lead_ID IS NULL OR [A].[Lead_ID] = @Lead_ID) 
            AND (@Account_ID IS NULL OR [A].[Account_ID] = @Account_ID) 
            AND (@Organization IS NULL OR CHARINDEX(UPPER(@Organization), UPPER([L].[Organization])) > 0) 
            AND (@LeadNumber IS NULL OR CHARINDEX(UPPER(@LeadNumber), UPPER([L].[Lead_Number])) > 0) 
            AND (@Source IS NULL OR CHARINDEX(UPPER(@Source), UPPER([L].[Source])) > 0) 
            AND (@LeadReceivedDateBeg IS NULL OR [L].[Lead_Received_Date] >= @LeadReceivedDateBeg) 
            AND (@LeadReceivedDateEnd IS NULL OR [L].[Lead_Received_Date] <= @LeadReceivedDateEnd) 
            AND (@CalenderYear IS NULL OR [A].[Calender_Year] = @CalenderYear) 
            AND (@RefundAvailableForRecoveryLowest IS NULL OR [A].[Amount_To_Recover] >= @RefundAvailableForRecoveryLowest) 
            AND (@RefundAvailableForRecoveryHighest IS NULL OR [A].[Amount_To_Recover] <= @RefundAvailableForRecoveryHighest) 
            AND (@ReasonForReferral IS NULL OR CHARINDEX(UPPER(@ReasonForReferral), UPPER([A].[Reason_For_Referral])) > 0) 
            AND (@CustomerNumber IS NULL OR CHARINDEX(UPPER(@CustomerNumber), UPPER([A].[CustomerNumber])) > 0) 
            AND (@AccountLastName IS NULL OR CHARINDEX(UPPER(@AccountLastName), UPPER([A].[Last_Name])) > 0) 
            AND (@AccountFirstName IS NULL OR CHARINDEX(UPPER(@AccountFirstName), UPPER([A].[First_Name])) > 0) 
            AND (@NameOnRefundCheck IS NULL OR CHARINDEX(UPPER(@NameOnRefundCheck), UPPER([A].[Name_On_Refund])) > 0) 
            AND (@Address IS NULL OR CHARINDEX(UPPER(@Address), UPPER([A].[Address])) > 0) 
            AND (@City IS NULL OR CHARINDEX(UPPER(@City), UPPER([A].[City])) > 0) 
            AND (@State IS NULL OR CHARINDEX(UPPER(@State), UPPER([A].[State])) > 0) 
            AND (@ZipCode IS NULL OR CHARINDEX(UPPER(@ZipCode), UPPER([A].[Zip_Code])) > 0) 
            AND (@RTN IS NULL OR CHARINDEX(UPPER(@RTN), UPPER([A].[RTN])) > 0) 
            AND (@ACCTNumber IS NULL OR CHARINDEX(UPPER(@ACCTNumber), UPPER([A].[Audit_Number])) > 0) 
            AND (@DebitCardNumber IS NULL OR CHARINDEX(UPPER(@DebitCardNumber), UPPER([A].[Scan_Number])) > 0)
            AND (@RefundClaimedLowest IS NULL OR [A].[Amount_Claimed] >= @RefundClaimedLowest) 
            AND (@RefundClaimedHighest IS NULL OR [A].[Amount_Claimed] <= @RefundClaimedHighest) 
            AND (@TreasuryCheckNumber IS NULL OR CHARINDEX(UPPER(@TreasuryCheckNumber), UPPER([A].[Building_Number])) > 0) 
            AND (@Results IS NULL OR CHARINDEX(UPPER(@Results), UPPER([A].[Results])) > 0) 
            AND (@SuspenseStatus IS NULL OR CHARINDEX(UPPER(@SuspenseStatus), UPPER([A].[Suspense_Status])) > 0) 
            AND (@FundsRequestedOrgLowest IS NULL OR [A].[Funds_Requested] >= @FundsRequestedOrgLowest) 
            AND (@FundsRequestedOrgHighest IS NULL OR [A].[Funds_Requested] <= @FundsRequestedOrgHighest) AND (@FundsReturnedCustomerLowest IS NULL OR [A].[Funds_Actually_Given] >= @FundsReturnedCustomerLowest) AND (@FundsReturnedCustomerHighest IS NULL OR [A].[Funds_Actually_Given] <= @FundsReturnedCustomerHighest) AND (@VoucherOrMemoApplied IS NULL OR CHARINDEX(UPPER(@VoucherOrMemoApplied), UPPER([A].[Voucher_or_Memo_Applied])) > 0
                ) AND (@Current_User_Name IS NULL OR CHARINDEX(UPPER(@Current_User_Name), UPPER([U].[SEID])) > 0) AND (@AssigneeFirstName IS NULL OR CHARINDEX(UPPER(@AssigneeFirstName), UPPER([U].[First_Name])) > 0) AND (@AssigneeLastName IS NULL OR CHARINDEX(UPPER(@AssigneeLastName), UPPER([U].[Last_Name])) > 0) AND (@AssigneeFullName IS NULL OR CHARINDEX(UPPER(@AssigneeFullName), UPPER([U].[Last_Name] + ', ' + [U].[First_Name] + '(' + [U].[SEID] + ')')) > 0) AND (@Assignee_Site IS NULL OR CHARINDEX(UPPER(@Assignee_Site), UPPER([U].[Site])) > 0) AND (@Current_Status_ID IS NULL OR @Current_Status_ID = [A].[Current_Status_ID]) AND (@Status_Name IS NULL OR CHARINDEX(UPPER(@Status_Name), UPPER([S].[Status_Name])) > 0) AND ([A].Deleted_Date IS NULL) AND (@comment IS NULL OR CHARINDEX(UPPER(@comment), UPPER([A].[comment])) > 0) AND (@OriginalDepositDateBeg IS NULL OR [A].[Original_Deposit_Date] >= @OriginalDepositDateBeg) 
            AND (@OriginalDepositDateEnd IS NULL OR [A].[Original_Deposit_Date] <= @OriginalDepositDateEnd) AND (@OriginalDepositAmountLowest IS NULL OR [A].[Original_Deposit_Amount] >= @OriginalDepositAmountLowest) AND (@OriginalDepositAmountHighest IS NULL OR [A].[Original_Deposit_Amount] <= @OriginalDepositAmountHighest)
        ORDER BY CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Lead_ID' THEN [A].[Lead_ID] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Lead_ID' THEN [A].[Lead_ID] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Account_ID' THEN [A].[Account_ID] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Account_ID' THEN [A].[Account_ID] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Organization' THEN [L].[Organization] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Organization' THEN [L].[Organization] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Lead_Number' THEN [L].[Lead_Number] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Lead_Number' THEN [L].[Lead_Number] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Source' THEN [L].[Source] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Source' THEN [L].[Source] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Lead_Received_Date' THEN [L].[Lead_Received_Date] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Lead_Received_Date' THEN [L].[Lead_Received_Date] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Calender_Year' THEN [A].[Calender_Year] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Calender_Year' THEN [A].[Calender_Year] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Amount_To_Recover] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Amount_To_Recover] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Reason_For_Referral] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Amount_To_Recover' THEN [A].[Reason_For_Referral] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'CustomerNumber' THEN [A].[CustomerNumber] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'CustomerNumber' THEN [A].[CustomerNumber] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'AccountLastName' THEN [A].[Last_Name] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'AccountFirstName' THEN [A].[First_Name] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Name_On_Refund' THEN [A].[Name_On_Refund] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Name_On_Refund' THEN [A].[Name_On_Refund] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Address' THEN [A].[Address] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Address' THEN [A].[Address] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'City' THEN [A].[City] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'City' THEN [A].[City] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'State' THEN [A].[State] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'State' THEN [A].[State] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Zip_Code' THEN [A].[Zip_Code] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Zip_Code' THEN [A].[Zip_Code] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'RTN' THEN [A].[RTN] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'RTN' THEN [A].[RTN] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Audit_Number' THEN [A].[Audit_Number] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Audit_Number' THEN [A].[Audit_Number] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Scan_Number' THEN [A].[Scan_Number] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Scan_Number' THEN [A].[Scan_Number] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Amount_Claimed' THEN [A].[Amount_Claimed] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Amount_Claimed' THEN [A].[Amount_Claimed] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Results' THEN [A].[Results] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Results' THEN [A].[Results] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Date_Voucher_Sent' THEN [A].[Date_Voucher_Sent] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Date_Voucher_Sent' THEN [A].[Date_Voucher_Sent] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Return_ID_Number' THEN [A].[Return_ID_Number] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Return_ID_Number' THEN [A].[Return_ID_Number] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Suspense_Status' THEN [A].[Suspense_Status] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Suspense_Status' THEN [A].[Suspense_Status] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Funds_Requested' THEN [A].[Funds_Requested] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Funds_Requested' THEN [A].[Funds_Requested] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Funds_Actually_Given' THEN [A].[Funds_Requested] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Funds_Actually_Given' THEN [A].[Funds_Requested] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Voucher_or_Memo_Applied' THEN [A].[Voucher_or_Memo_Applied] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Voucher_or_Memo_Applied' THEN [A].[Voucher_or_Memo_Applied] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Current_User_Name' THEN [U].[SEID] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Current_User_Name' THEN [U].[SEID] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'AssigneeFirstName' THEN [U].[First_Name] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'AssigneeFirstName' THEN [U].[First_Name] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'AssigneeLastName' THEN [U].[Last_Name] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'AssigneeLastName' THEN [U].[Last_Name] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Assignee_Site' THEN [U].[Site] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Assignee_Site' THEN [U].[Site] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Current_Status_ID' THEN [A].[Current_Status_ID] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Current_Status_ID' THEN [A].[Current_Status_ID] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Status_Name' THEN [S].[Status_Name] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Status_Name' THEN [S].[Status_Name] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Deleted_Date' THEN [A].[Deleted_Date] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Deleted_Date' THEN [A].[Deleted_Date] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'comment' THEN [A].[comment] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'comment' THEN [A].[comment] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Original_Deposit_Date' THEN [A].[Original_Deposit_Date] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Original_Deposit_Date' THEN [A].[Original_Deposit_Date] END DESC
            ,CASE WHEN UPPER(@SortDirection) = 'ASC' AND @SortColumn = 'Original_Deposit_Amount' THEN [A].[Original_Deposit_Amount] END ASC
            ,CASE WHEN UPPER(@SortDirection) = 'DESC' AND @SortColumn = 'Original_Deposit_Amount' THEN [A].[Original_Deposit_Amount] END DESC
        )
    SELECT [Lead_ID]
        ,[Account_ID]
        ,[Organization]
        ,[Lead_Number]
        ,[Source]
        ,[Lead_Received_Date]
        ,[Calender_Year]
        ,[Amount_To_Recover]
        ,[Reason_For_Referral]
        ,[CustomerNumber]
        ,[AccountLastName]
        ,[AccountFirstName]
        ,[Name_On_Refund]
        ,[Address]
        ,[City]
        ,[State]
        ,[Zip_Code]
        ,[RTN]
        ,[Audit_Number]
        ,[Scan_Number]
        ,[Amount_Claimed]
        ,[Building_Number]
        ,[Results]
        ,[Date_Voucher_Sent]
        ,[Return_ID_Number]
        ,[Suspense_Status]
        ,[Funds_Requested]
        ,[Funds_Actually_Given]
        ,[Voucher_or_Memo_Applied]
        ,[Current_User_Name]
        ,[AssigneeFirstName]
        ,[AssigneeLastName]
        ,[AssigneeFullName]
        ,[Assignee_Site]
        ,[Current_Status_ID]
        ,[Status_Name]
        ,[Deleted_Date]
        ,[comment]
        ,[Original_Deposit_Date]
        ,[Original_Deposit_Amount]
    FROM ACCOUNTS
    WHERE (@StartingRow IS NULL OR ([ROWNUM] BETWEEN @StartingRow AND ((@StartingRow - 1) + @NumOfRowsToTake)))
END
GO
Ad

Answer

That SP is never going to be fast the way it is; you're in for a rewrite. The only thing you can do with it that's an easy hack is try WITH RECOMPILE/OPTION (RECOMPILE), and that looks like one nasty compilation.

Consider reading Erland Sommarskog's article Dynamic Search Conditions in T‑SQL, which discusses this general case in detail.

Honestly, the way you're structuring it, you probably ought to look at sp_executesql and have EVERY VARIABLE BE A PARAMETER passed in to sp_executesql.

That way, you'll at least get a dedicated query plan for each combination of arguments, and there won't be extraneous crap.

When building the dynamic SQL statement, ONLY use the parts of the WHERE and ORDER BY you need to, but DO NOT CONCATENATE values passed in directly to the SQL itself (which would allow SQL injection by a malicious user); use them as parameters, or as part of IF statements that put in chunks of SQL.

Then get rid of your non-SARGable JOIN and WHERE portions, like those CHARINDEX()s - no functions in WHERE or JOIN (or even ORDER BY, if possible) clauses, please, and then your indexes can do their job!

For your @StartingRow and @NumRowsToTake paging, please consider upgrading to SQL Server 2012 or later and read Pagination with Offset Fetch by Aaron Bertrand; he's got some good benchmarks and metrics on some options in there. Note that you should even read the first couple paragraphs for SQL Server 2008, in particular

Based on the indexing of the table, the columns needed, and the sort method chosen, paging can be relatively painless. If you're looking for the "first" 20 customers and the clustered index supports that sorting (say, a clustered index on an IDENTITY column or DateCreated column), then the query is going to be pretty efficient. If you need to support sorting that requires non-clustered indexes, and especially if you have columns needed for output that aren't covered by the index (never mind if there is no supporting index), the queries can get more expensive. And even the same query (with a different @PageNumber parameter) can get much more expensive as the @PageNumber gets higher – since more reads may be required to get to that "slice" of the data.

Ad
source: stackoverflow.com
Ad