Bits of .NET

📄 Don’t Load 10,000 Rows at Once

Loading all rows is slow. LIMIT and OFFSET load only needed rows. Faster queries, better user experience.

📝 Basic Pagination

-- PostgreSQL / MySQL / SQLite
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 0;  -- Page 1 (rows 1-10)

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 10; -- Page 2 (rows 11-20)

-- SQL Server (OFFSET FETCH)
SELECT * FROM users
ORDER BY id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL alternative
SELECT * FROM users
ORDER BY id
LIMIT 10, 10;  -- LIMIT offset, count

🎯 Cursor-Based Pagination (Better for Large Data)

-- Instead of OFFSET (which scans rows), use WHERE
SELECT * FROM users
WHERE id > 100  -- last seen ID
ORDER BY id
LIMIT 10;

-- With date cursor
SELECT * FROM orders
WHERE order_date > '2024-01-15'
ORDER BY order_date
LIMIT 20;

-- Performance: OFFSET 10000 is slow (scans 10000 rows)
-- Cursor: only scans 10 rows (much faster)

💡 Best Practices

• Always use ORDER BY with LIMIT (consistent results)
• Small page sizes (10-50 rows for UI, 100-500 for APIs)
• For large datasets, use cursor-based pagination (keyset pagination)
• Index the ORDER BY column for fast pagination