Skip to main content

Fast SQL Server Stored Procedure with Filtering, Sorting and Paging

OFFSET/FETCH Pagination:- 
With the help of these OFFSET and FETCH keywords inside a CTE I managed to build a SQL Stored procedure that was at least twice as fast in return times as the other average ones found on the internet.

Here I'm sharing 2 examples to achieving the SQL filtering, sorting and paging. Both example are tested and working fine but I will recommend example 1 for you.

Example 1,
--[dbo].[usp_GetemployeeCodes] NULL, 1, 10, 'employee_code', 'DESC'

CREATE PROCEDURE [dbo].[usp_GetEmployeeCodes]
(
    /* Optional Filters for Dynamic Search*/
    @SearchCode NVARCHAR(50) = NULL,

            /*– Pagination Parameters */
            @PageNo INT = 1,
            @PageSize INT = 15,

   /*– Sorting Parameters */
            @SortColumn NVARCHAR(20) = 'employee_code',
            @SortOrder NVARCHAR(4)='ASC'
)
AS
BEGIN
    /*–Declaring Local Variables corresponding to parameters for modification */
    DECLARE
            @lSearchCode NVARCHAR(50),

    @lPageNbr INT,
    @lPageSize INT,

    @lSortCol NVARCHAR(20),

    @lFirstRec INT,
    @lLastRec INT,
    @lTotalRows INT

    /*Setting Local Variables*/
            SET @lSearchCode =LTRIM(RTRIM(@SearchCode))

    SET @lPageNbr = @PageNo
    SET @lPageSize = @PageSize
    SET @lSortCol = LTRIM(RTRIM(@SortColumn))

    SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
    SET @lTotalRows = @lFirstRec - @lLastRec + 1

   ; WITH CTE_Results
    AS (
    SELECT ROW_NUMBER() OVER (
                        ORDER BY
                                    CASE WHEN @lSortCol = 'employee_code' AND @SortOrder='ASC'
                                                  THEN employee_code                                                                                   
                                    END ASC,
                                    CASE WHEN @lSortCol = 'employee_code' AND @SortOrder='DESC'
                                                THEN employee_code                                   
                                    END DESC,

                                    CASE WHEN @lSortCol = 'employee_comp_code' AND @SortOrder='ASC'
                                                THEN employee_comp_code
                                    END ASC,
                                    CASE WHEN @lSortCol = 'employee_comp_code' AND @SortOrder='DESC'
                                                THEN employee_comp_code
                                    END DESC                   
  ) AS ROWNUM,
  Count(*) over () AS MaxRows,
                        LTRIM(RTRIM(employee_code)) AS employee_code
                        ,employee_comp_code         
 FROM [dbo].[employee_codes]
 WHERE           (@lSearchCode IS NULL OR employee_code LIKE '%' + @lSearchCode + '%')
)
SELECT
                        MaxRows,
                        LTRIM(RTRIM(employee_code)) AS employee_code
                        ,employee_comp_code                     
FROM CTE_Results AS CPC
    WHERE ROWNUM > @lFirstRec
     AND ROWNUM < @lLastRec
 ORDER BY ROWNUM ASC
END



Example 2,
--[dbo].[usp_GetEmployeeCodes] 'INC', 3, 15, 'employee_code', 'ASC' 
--[dbo].[usp_GetEmployeeCodes] '', 1, 15, 'employee_code', 'ASC' 
CREATE  PROC [dbo].[usp_GetEmployeeCodes]
(
            @SearchValue NVARCHAR(50) = NULL,

            @PageNo INT = 1,
            @PageSize INT = 10,

            @SortColumn NVARCHAR(20) = 'employee_code',
            @SortOrder NVARCHAR(20) = 'ASC'
)
 AS BEGIN
 SET NOCOUNT ON;
   SET @SearchValue = LTRIM(RTRIM(@SearchValue))
   ; WITH CTE_Results AS
   (
                                    SELECT
                 employee_code
                ,employee_comp_code               
            FROM [dbo].[employee_codes]
            WHERE (@SearchValue IS NULL OR employee_name LIKE '%' + @SearchValue + '%')
            ORDER BY
               CASE WHEN (@SortColumn = 'employee_code' AND @SortOrder='ASC')
                                                            THEN employee_code
               END ASC,

                                       CASE WHEN (@SortColumn = 'employee_code' AND @SortOrder='DESC')
                                                  THEN employee_code
                                       END DESC,

               CASE WHEN (@SortColumn = 'employee_comp_code' AND @SortOrder='ASC')
                    THEN employee_comp_code
               END ASC,

               CASE WHEN (@SortColumn = 'employee_comp_code' AND @SortOrder='DESC')
                   THEN employee_comp_code
               END DESC                  

                                    OFFSET @PageSize * (@PageNo - 1) ROWS
                        FETCH NEXT @PageSize ROWS ONLY
            ),
            CTE_TotalRows AS
            (
             SELECT count(employee_code) as MaxRows
                        FROM [dbo].[employee_codes]
                        WHERE (@SearchValue IS NULL OR employee_name LIKE '%' + @SearchValue + '%')
            )
                                     SELECT MaxRows
                        ,t.employee_code
                        ,t.employee_comp_code                  
             FROM [dbo].[employee_codes] as t, CTE_TotalRows
                         WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.employee_code = t.employee_code)
            OPTION (RECOMPILE)

 END

When to use Option (recompile)?
1.     Unpredictable amount of results returned
2.     Optimized query plan

Use WITH RECOMPILE or OPTION (RECOMPILE)


Using RECOMPILE eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time we execute the query. The disadvantage here is that we lose all benefit from having SQL Server save CPU cycles by caching execution plans.


OPTION (RECOMPILE) is used in the queries with parameters to prevent parameter sniffing issue.


Impact of option (recompile) when joins involved: -
The estimated rows won’t be accounted for the table join. Instead, the recompile will only provide back the count of the rows in the table variable, without accounting for the possible 1 to many. This is important to remember as option (recompile) added to a table variable with a 1-many won’t fix the correct statistics estimation issue.


Disadvantages of Option (recompile) -
1.     Lose DM Exec query stats.
2.     Can have tremendous impact on CPU


I hope it will helps you a lots. If you have another one method to achieve this. Please send me. I will update here as an example 3. Thank you.

By Anil Singh | Rating of this article (*****)

Popular posts from this blog

List of Countries, Nationalities and their Code In Excel File

Download JSON file for this List - Click on JSON file    Countries List, Nationalities and Code Excel ID Country Country Code Nationality Person 1 UNITED KINGDOM GB British a Briton 2 ARGENTINA AR Argentinian an Argentinian 3 AUSTRALIA AU Australian an Australian 4 BAHAMAS BS Bahamian a Bahamian 5 BELGIUM BE Belgian a Belgian 6 BRAZIL BR Brazilian a Brazilian 7 CANADA CA Canadian a Canadian 8 CHINA CN Chinese a Chinese 9 COLOMBIA CO Colombian a Colombian 10 CUBA CU Cuban a Cuban 11 DOMINICAN REPUBLIC DO Dominican a Dominican 12 ECUADOR EC Ecuadorean an Ecuadorean 13 EL SALVADOR

39 Best Object Oriented JavaScript Interview Questions and Answers

Most Popular 37 Key Questions for JavaScript Interviews. What is Object in JavaScript? What is the Prototype object in JavaScript and how it is used? What is "this"? What is its value? Explain why "self" is needed instead of "this". What is a Closure and why are they so useful to us? Explain how to write class methods vs. instance methods. Can you explain the difference between == and ===? Can you explain the difference between call and apply? Explain why Asynchronous code is important in JavaScript? Can you please tell me a story about JavaScript performance problems? Tell me your JavaScript Naming Convention? How do you define a class and its constructor? What is Hoisted in JavaScript? What is function overloadin

React | Encryption and Decryption Data/Text using CryptoJs

To encrypt and decrypt data, simply use encrypt () and decrypt () function from an instance of crypto-js. Node.js (Install) Requirements: 1.       Node.js 2.       npm (Node.js package manager) 3.       npm install crypto-js npm   install   crypto - js Usage - Step 1 - Import var   CryptoJS  =  require ( "crypto-js" ); Step 2 - Encrypt    // Encrypt    var   ciphertext  =  CryptoJS . AES . encrypt ( JSON . stringify ( data ),  'my-secret-key@123' ). toString (); Step 3 -Decrypt    // Decrypt    var   bytes  =  CryptoJS . AES . decrypt ( ciphertext ,  'my-secret-key@123' );    var   decryptedData  =  JSON . parse ( bytes . toString ( CryptoJS . enc . Utf8 )); As an Example,   import   React   from   'react' ; import   './App.css' ; //Including all libraries, for access to extra methods. var   CryptoJS  =  require ( "crypto-js" ); function   App () {    var   data

25 Best Vue.js 2 Interview Questions and Answers

What Is Vue.js? The Vue.js is a progressive JavaScript framework and used to building the interactive user interfaces and also it’s focused on the view layer only (front end). The Vue.js is easy to integrate with other libraries and others existing projects. Vue.js is very popular for Single Page Applications developments. The Vue.js is lighter, smaller in size and so faster. It also supports the MVVM ( Model-View-ViewModel ) pattern. The Vue.js is supporting to multiple Components and libraries like - ü   Tables and data grids ü   Notifications ü   Loader ü   Calendar ü   Display time, date and age ü   Progress Bar ü   Tooltip ü   Overlay ü   Icons ü   Menu ü   Charts ü   Map ü   Pdf viewer ü   And so on The Vue.js was developed by “ Evan You ”, an Ex Google software engineer. The latest version is Vue.js 2. The Vue.js 2 is very similar to Angular because Evan You was inspired by Angular and the Vue.js 2 components looks like -

Encryption and Decryption Data/Password in Angular

You can use crypto.js to encrypt data. We have used 'crypto-js'.   Follow the below steps, Steps 1 –  Install CryptoJS using below NPM commands in your project directory npm install crypto-js --save npm install @types/crypto-js –save After installing both above commands it looks like  – NPM Command  1 ->   npm install crypto-js --save NPM Command  2 ->   npm install @types/crypto-js --save Steps 2  - Add the script path in “ angular.json ” file. "scripts" : [                "../node_modules/crypto-js/crypto-js.js"               ] Steps 3 –  Create a service class “ EncrDecrService ” for  encrypts and decrypts get/set methods . Import “ CryptoJS ” in the service for using  encrypt and decrypt get/set methods . import  {  Injectable  }  from   '@angular/core' ; import   *   as   CryptoJS   from   'crypto-js' ; @ Injectable ({    providedIn:   'root' }) export   class   EncrDecrS