Optimizing Database Queries with Pagination: A Deep Dive into Stored Procedures and Asynchronous Methods

Introduction:

Efficiently fetching data from a database is crucial for the performance of any modern application. When dealing with large datasets, traditional querying methods can become inefficient and slow. In this article, we'll explore how to enhance database query performance using stored procedures and asynchronous methods, with a focus on pagination. We'll delve into the implementation of a stored procedure named GetEntitiesWithTotalCount, paired with an asynchronous method GetAllAsync, to efficiently retrieve paginated data while also obtaining the total count of records matching a search criteria.

Understanding Pagination in Database Queries:

Pagination is a technique used to divide a large dataset into smaller, manageable chunks or pages. It allows users to navigate through data sequentially, improving usability and performance. In database queries, pagination is typically achieved using the OFFSET and FETCH clauses in SQL Server, which enable skipping a specified number of rows and retrieving a specified number of rows, respectively.

Implementing `GetEntitiesWithTotalCount` Stored Procedure:

The GetEntitiesWithTotalCount stored procedure is designed to fetch a paginated list of entities from the database while also providing the total count of records matching a search criteria. Let's break down its key components:

CREATE PROCEDURE dbo.GetEntitiesWithTotalCount
    @SearchText NVARCHAR(100),
    @Take INT,
    @Skip INT,
    @TotalRecords INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables to hold total count
    DECLARE @RowCount INT;

    -- Get total count of records matching the search criteria
    SELECT @RowCount = COUNT(*)
    FROM YourTable
    WHERE YourColumn LIKE '%' + @SearchText + '%';

    -- Set output parameter
    SET @TotalRecords = @RowCount;

    -- Retrieve paginated list of entities
    SELECT *
    FROM YourTable
    WHERE YourColumn LIKE '%' + @SearchText + '%'
    ORDER BY YourColumn
    OFFSET @Skip ROWS
    FETCH NEXT @Take ROWS ONLY;
END

Implementing `GetAllAsync` Asynchronous Method:

The GetAllAsync method is an asynchronous wrapper around the GetEntitiesWithTotalCount stored procedure, designed to provide a seamless and efficient way to fetch paginated data in C# applications. Here's how it's implemented:

public async Task<(List<T> data, int totalRecords)> GetAllAsync(string procedureName, string searchText, int take, int skip)
{
    var parameters = new[]
    {
        new SqlParameter("@SearchText", searchText),
        new SqlParameter("@Take", take),
        new SqlParameter("@Skip", skip),
        new SqlParameter("@TotalRecords", SqlDbType.Int) { Direction = ParameterDirection.Output }
    };

    // Execute stored procedure with parameters and output parameter
    var result = await _context.Set<T>()
                               .FromSqlRaw($"EXEC {procedureName} @SearchText, @Take, @Skip, @TotalRecords OUTPUT", parameters)
                               .ToListAsync();

    // Retrieve total records count from output parameter
    var totalRecords = Convert.ToInt32(parameters[3].Value);

    return (result, totalRecords);
}

Usage Example:

class Program
{
    static async Task Main(string[] args)
    {
        // Assume you have a service or repository class instance
        YourDatabaseService dbService = new YourDatabaseService();

        // Specify the parameters for pagination
        string procedureName = "GetEntitiesWithTotalCount";
        string searchText = "example"; // Your search text
        int take = 10; // Number of records to retrieve per page
        int skip = 0; // Number of records to skip (for the first page)

        try
        {
            // Call GetAllAsync method to fetch paginated data
            var result = await dbService.GetAllAsync(procedureName, searchText, take, skip);

            // Access the paginated data and total records count
            List<MyEntity> paginatedData = result.data; // Replace MyEntity with your entity type
            int totalRecords = result.totalRecords;

            // Process the paginated data
            foreach (var entity in paginatedData)
            {
                Console.WriteLine($"Id: {entity.Id}, Name: {entity.Name}");
                // Process other properties as needed
            }

            Console.WriteLine($"Total Records: {totalRecords}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error occurred: {ex.Message}");
        }
    }
}

Conclusion:

In conclusion, implementing pagination in database queries is essential for optimizing performance and enhancing user experience. By leveraging stored procedures and asynchronous methods, developers can efficiently fetch paginated data while also obtaining the total count of records matching a search criteria. Incorporating these techniques into your application's data access layer can lead to improved scalability, responsiveness, and overall user satisfaction. So why wait? Start implementing pagination in your database queries today and take your application's performance to the next level!

Optimizing Database Queries with Pagination: A Deep Dive into Stored Procedures and Asynchronous Methods