Fast search pages in .NET: stored procedures, Dapper and DataTables

Admin and back-office apps live and die by their search screens: find an order, filter quote requests by status, look up a customer. Done naively, these pages get slower every month as the tables grow.

The pattern we use on .NET and SQL Server is simple and holds up well: a parameterized stored procedure for the search, Dapper to call it, and jQuery DataTables to present it.

Why a stored procedure?

EF Core is great for forms and CRUD. For search screens with half a dozen optional filters, a stored procedure gives you:

• One place to tune. The query, its indexes and its execution plan all live together in SQL where a DBA (or future you) can see them.
• Predictable SQL. No surprise query shapes generated from a LINQ expression.
• Security. Parameters all the way down, and the app account can be granted EXECUTE without broad table access.

The procedure

Optional filters use the (@Param IS NULL OR Column = @Param) pattern. OPTION (RECOMPILE) lets SQL Server build a plan for the filters actually supplied, instead of reusing one cached for a different combination:

CREATE OR ALTER PROCEDURE dbo.usp_QuoteRequest_Search
    @Search        nvarchar(100) = NULL,
    @QuoteStatusId int           = NULL,
    @ServiceTypeId int           = NULL,
    @FromDt        datetime2     = NULL,
    @ToDt          datetime2     = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  q.QuoteRequestId, q.Name, q.Email, q.Company,
            st.Name AS ServiceType, qs.Name AS Status, q.CreatedDt
    FROM    dbo.T_QuoteRequest q
    JOIN    dbo.L_ServiceType  st ON st.ServiceTypeId = q.ServiceTypeId
    JOIN    dbo.L_QuoteStatus  qs ON qs.QuoteStatusId = q.QuoteStatusId
    WHERE   q.IsActive = 1
      AND  (@QuoteStatusId IS NULL OR q.QuoteStatusId = @QuoteStatusId)
      AND  (@ServiceTypeId IS NULL OR q.ServiceTypeId = @ServiceTypeId)
      AND  (@FromDt IS NULL OR q.CreatedDt >= @FromDt)
      AND  (@ToDt   IS NULL OR q.CreatedDt <  DATEADD(day, 1, @ToDt))
      AND  (@Search IS NULL OR q.Name LIKE N'%' + @Search + N'%'
                             OR q.Email LIKE '%' + @Search + '%'
                             OR q.Company LIKE N'%' + @Search + N'%')
    ORDER BY q.CreatedDt DESC
    OPTION (RECOMPILE);
END

Back it with an index that matches the common filters, for example (IsActive, QuoteStatusId, CreatedDt DESC). Check the actual execution plan with realistic data volumes rather than guessing.

Calling it with Dapper

Dapper maps the result set straight to a small row class. It's fast and there's nothing to configure:

public sealed record QuoteSearchRow(int QuoteRequestId, string Name, string Email,
    string? Company, string ServiceType, string Status, DateTime CreatedDt);

public async Task<IReadOnlyList<QuoteSearchRow>> SearchQuotesAsync(QuoteSearchFilter f)
{
    using var conn = _connections.Create();
    var rows = await conn.QueryAsync<QuoteSearchRow>(
        "dbo.usp_QuoteRequest_Search",
        new
        {
            Search = string.IsNullOrWhiteSpace(f.Search) ? null : f.Search.Trim(),
            f.QuoteStatusId,
            f.ServiceTypeId,
            f.FromDt,
            f.ToDt
        },
        commandType: CommandType.StoredProcedure);
    return rows.AsList();
}

Convert empty strings to null before calling, so "no filter" really means no filter.

Presenting it with DataTables

Render the rows into a normal HTML table, then let DataTables add sorting, paging and an instant filter box. The filter form above the table posts back to the controller, so the stored procedure does the heavy filtering and DataTables only refines what's on the page:

<table class="table js-datatable" data-order='[[6,"desc"]]'>
  <thead>
    <tr><th>#</th><th>Name</th><th>Email</th><th>Company</th>
        <th>Service</th><th>Status</th><th>Received</th></tr>
  </thead>
  <tbody>
    @foreach (var r in Model.Rows)
    {
      <tr>
        <td>@r.QuoteRequestId</td><td>@r.Name</td><td>@r.Email</td><td>@r.Company</td>
        <td>@r.ServiceType</td><td>@r.Status</td>
        <td data-order="@r.CreatedDt.ToString("o")">@r.CreatedDt.ToString("MMM d, yyyy")</td>
      </tr>
    }
  </tbody>
</table>

$("table.js-datatable").each(function () {
  $(this).DataTable({
    pageLength: 25,
    order: $(this).data("order") || [],
    language: { search: "", searchPlaceholder: "Filter results..." }
  });
});

The data-order attribute on the date cell lets DataTables sort by the real timestamp while displaying a friendly date.

When the results get big

Client-side DataTables is comfortable up to a few thousand rows per search. Beyond that, move paging into SQL: add @PageNumber and @PageSize parameters with OFFSET ... FETCH NEXT ... ROWS ONLY, return a total count with COUNT(*) OVER(), and switch DataTables to server-side mode. The controller, procedure and Dapper call keep the same shape. Only the paging moves.

The takeaway

Keep searches in stored procedures, call them with Dapper, and let DataTables handle presentation. It's a small amount of code, it's easy to tune, and it stays fast as the data grows.

Every admin list screen on this site is built this way, and so is the upcoming TrussCode Starter Kit.