Optimasi query: execution plan, SARGable, parameter sniffing, pagination

Ringkasan satu halaman. Dari Lesson 0023. Skrip lab: dotnet/lab/0023-optimasi-query.sql. Index: Reference: Index. N+1 dan AsNoTracking: Reference: EF Core.

Alur "query ini lambat" ^[1]

1. Ambil SQL: log EF Core (LogTo, level Information, ada durasi) / ToQueryString(). Cek N+1, tracking, kolom berlebih (0006).
2. Jalankan dengan actual plan + SET STATISTICS IO ON.
3. Baca plan: operator mahal, scan vs seek, Key Lookup berulang, estimated vs actual rows, warning (mis. CONVERT_IMPLICIT).
4. Perbaiki: SARGable, index (composite/covering), parameter sniffing, keyset pagination.
5. Ukur lagi: logical reads + plan.

Estimated vs actual plan ^{[2] [3]}

Estimated rows jauh dari actual rows → plan dipilih untuk jumlah baris yang salah. Penyebab: statistik, local variable (pakai rata-rata density), parameter sniffing. Scan tidak selalu buruk bila sebagian besar baris diambil.

SARGable ^[4]

• Aturan: jangan bungkus kolom dengan fungsi/perhitungan; pindahkan ke sisi konstanta. Berlaku juga untuk JOIN, HAVING, GROUP BY, ORDER BY.
• Tipe parameter harus cocok dengan kolom (nvarchar vs varchar) supaya tidak ada CONVERT_IMPLICIT pada kolom. ^[5]
• Tanggal: half-open >= awal AND < berikutnya, bukan BETWEEN … '…-30'. EF Core: p.CreatedAt.Year == 2025 → DATEPART pada kolom.

Parameter sniffing ^{[3] [6]}

Plan dikompilasi untuk nilai parameter pertama, disimpan, dipakai ulang. Masalah bila data tidak merata (lab: Category 1 = 90.000, Category 6 = 20). Kena: stored procedure, sp_executesql (EF Core, Dapper), prepared query.

SQL Server 2022 PSP optimization (compat 160, predikat equality): beberapa plan aktif per query. Untuk demo: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF lalu = ON lagi; kosongkan cache database: ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE.

Pagination ^{[8] [9]}

-- OFFSET: memproses semua baris yang dilewati; random access OK
SELECT Id, Name FROM dbo.Product ORDER BY Id OFFSET 90000 ROWS FETCH NEXT 20 ROWS ONLY;

-- Keyset: seek langsung; hanya maju/mundur, tidak bisa lompat halaman
SELECT TOP (20) Id, Name FROM dbo.Product WHERE Id > @lastId ORDER BY Id;

-- Keyset dua kunci (urutan unik: Id sebagai pemecah seri, index (Price) / (Price, Id))
SELECT TOP (20) Id, Name, Price FROM dbo.Product
WHERE Price > @lastPrice OR (Price = @lastPrice AND Id > @lastId)
ORDER BY Price, Id;

• Urutan harus unik; database tidak punya urutan default, bahkan untuk primary key.
• OFFSET bisa melewatkan/menggandakan baris saat data berubah; keyset tidak.
• EF Core: Skip/Take = OFFSET; Where(p => p.Id > lastId).Take(n) = keyset.

SQL dari EF Core ^{[10] [11]}

optionsBuilder.UseSqlServer(cs)
    .LogTo(Console.WriteLine, LogLevel.Information)  // SQL + durasi; jangan dibiarkan di production
    .EnableSensitiveDataLogging();                   // nilai parameter: development saja

var sql = db.Products.Where(p => p.CategoryId == id)
    .TagWith("Products by category")                // komentar di SQL
    .ToQueryString();                               // SQL tanpa eksekusi (EF Core 5+, untuk debugging)

Padanan Node: LogTo ≈ Prisma log: ['query']; ToQueryString ≈ knex .toSQL().

Jawaban 60–90 detik

Ambil SQL dari log EF Core atau ToQueryString, cek N+1 dan tracking. Jalankan dengan actual plan dan STATISTICS IO. Cari scan pada tabel besar, Key Lookup ribuan kali, estimated vs actual yang meleset. Perbaiki: kolom tanpa fungsi (SARGable), index composite/covering, parameter sniffing (RECOMPILE, OPTIMIZE FOR, atau index yang menghilangkan lookup), keyset pagination untuk halaman jauh. Ukur lagi.

Sumber: [1] EF Core: Performance Diagnosis · [2] Display the estimated execution plan · [3] Query processing architecture guide · [4] Troubleshoot high-CPU-usage issues · [5] Dapper README · [6] PSP optimization · [7] Query hints · [8] EF Core: Pagination · [9] ORDER BY (OFFSET/FETCH) · [10] EF Core: Simple logging · [11] ToQueryString