Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Practice

Performance Best Practices

Fast data access is mostly about doing less: fewer round trips, fewer rows, fewer columns and well-indexed queries.

Advanced 5 min Lesson 30 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:Connection PoolingAsync Data Access
0/32 in ADO.NET
On this page · 5 min · Advanced

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Advanced
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

Fast data access is like efficient grocery shopping: fewer trips (round trips), smaller bags (fewer columns), and the right aisle first (indexes).

Round trip
One request–response to the database.
Paging
OFFSET/FETCH — fetch only one page.
Typed parameters
Match column types to avoid conversions.

Learning objectives#

  • Reduce round trips and data volume
  • Use parameters that match column types
  • Page large results
  • Find slow queries

Explanation#

Connections: always dispose (pooling), keep one connection string, open late.

Queries: select only needed columns, filter and page in SQL (OFFSET/FETCH), avoid N+1 queries, batch related queries.

Parameters: match SQL types and sizes (VARCHAR vs NVARCHAR) to avoid implicit conversions that stop indexes being used.

Readers: use typed getters with ordinals; use CommandBehavior.SequentialAccess for large binary or text columns.

Measure with execution plans, Query Store and Application Insights/OpenTelemetry dependency timings.

Code example: A fast, paged query#

C#PagedProducts.cs Illustrative
static async Task<(List<Product> Items, int Total)> GetPageAsync(SqlConnection conn, int page, int size, CancellationToken ct)
{
    size = Math.Clamp(size, 1, 100);
    await using var cmd = new SqlCommand("""
        SELECT COUNT(*) FROM Products;
        SELECT Id, Name, Price, Stock FROM Products
        ORDER BY Name
        OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;
        """, conn);
    cmd.Parameters.Add("@skip", SqlDbType.Int).Value = (Math.Max(page, 1) - 1) * size;
    cmd.Parameters.Add("@take", SqlDbType.Int).Value = size;


    await using var r = await cmd.ExecuteReaderAsync(ct);
    await r.ReadAsync(ct);
    int total = r.GetInt32(0);


    await r.NextResultAsync(ct);
    var items = new List<Product>(size);
    while (await r.ReadAsync(ct))
        items.Add(new Product(r.GetInt32(0), r.GetString(1), r.GetDecimal(2), r.GetInt32(3)));
    return (items, total);
}
Expected output
Page 1 of 20: 20 items, total 400 — one round trip, only four columns, and SQL Server does the paging.

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. The page size is capped at 100 rows.
  2. Count and page are fetched in one batch.
  3. OFFSET/FETCH pages in SQL rather than in C#.
  4. An index on Name keeps the ORDER BY fast.

Real-world use case#

A product listing that loaded 50,000 rows into memory to show 20 becomes 200 times faster after moving paging into SQL.

Common mistakes#

SELECT * and filtering in C#

You transfer data you throw away. Filter, sort and page in SQL.

NVARCHAR parameter against a VARCHAR indexed column

The implicit conversion can turn an index seek into a scan.

Best practices#

  • Dispose everything (pooling)
  • Async with cancellation
  • Typed, sized parameters
  • Page and index; measure with execution plans

Remember this#

Fewer round trips, rows and columns.

Page in SQL with OFFSET/FETCH.

Parameter types must match column types.

Practice exercise#

Your task: Which index would help ORDER BY Name with OFFSET/FETCH on Products?

Show solution
SQL Illustrative
CREATE INDEX IX_Products_Name ON Products (Name) INCLUDE (Price, Stock);

Mini quiz#

Performance Best Practices quizMultiple choice Question 1 of 1

Where should paging of a large table happen?

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.