Dapper ve SQL Server: TVP, ANSI String'ler, {=literal} ve Parameter Sniffing

Sürüm notu: Bu yazıdaki kodlar Dapper 2.1 ve .NET 10 içindir.

Önce Tahmin Et

SQL Server'daki books tablosunda 100.000 satır var ve isbn kolonunda bir index duruyor. Tek bir kitabı ISBN'iyle arıyoruz:

var book = conn.QuerySingle<BookTitle>(
    "select id, title from dbo.books where isbn = @isbn",
    new { isbn = "9780441013593" });
Console.WriteLine(book.Title);

public record BookTitle(int Id, string Title);

Index'li bir kolonda eşitlik araması. SQL Server bunun için diskten kaç sayfa (8 KB'lık blok) okur? Birkaç sayfa mı, yüzlerce mi? Tahminini yap:

logical reads: 348
Dune

348 sayfa. Doğru index'le bu sayı 6 olmalıydı. Sorgu doğru sonucu verdi, kimse hata görmedi, ama SQL Server index'in tamamını taradı. Bu yazı Dapper'ı SQL Server'la kullanırken bu tür görünmez maliyetleri anlatıyor: ANSI string'ler, parameter boyutları, IN listeleri, table-valued parameter'lar, {=literal} ve parameter sniffing.

Ölçüm Düzeneği

Bu yazıda 12. yazıdaki docker SQL Server'ı kullanıyoruz; SQLite'ta ne plan cache ne de bu provider'a özgü davranışlar var. Tablo şöyle; ISBN'ler varchar(13), yazar dağılımı bilerek çarpık: Frank Herbert'in 99.990 kitabı, Ursula K. Le Guin'in 10 kitabı var:

create table dbo.books (
    id int identity primary key,
    title nvarchar(200) not null,
    isbn varchar(13) not null,
    author_id int not null references dbo.authors(id),
    price decimal(10, 2) not null,
    is_featured bit not null
);

with n as (
    select top (100000) row_number() over (order by (select null)) as i
    from sys.all_objects a cross join sys.all_objects b
)
insert dbo.books (title, isbn, author_id, price, is_featured)
select concat('Book ', i),
       concat('978', right(concat('0000000000', i), 10)),
       case when i % 10000 = 0 then 2 else 1 end,
       10 + i % 20,
       case when i % 5000 = 0 then 1 else 0 end
from n;

update dbo.books set title = 'Dune', isbn = '9780441013593' where id = 1;

create index ix_books_isbn on dbo.books (isbn);
create index ix_books_author on dbo.books (author_id);
create index ix_books_featured on dbo.books (id) include (title) where is_featured = 1;

create type dbo.IdList as table (id int not null primary key);

Ölçmek için iki araç kullanıyoruz. set statistics io on SQL Server'ın her sorguda kaç sayfa okuduğunu bir info message olarak gönderir; SqlClient bunu InfoMessage event'iyle verir. Plan cache'i görmek için de sys.dm_exec_cached_plans'a bakan küçük bir yardımcı yazdım:

using var conn = new SqlConnection(connectionString);
conn.Open();

// yalnızca deneme sunucusunda: her çalıştırma boş bir plan cache ile başlasın
conn.Execute("dbcc freeproccache with no_infomsgs");

conn.InfoMessage += (_, e) =>
{
    var m = Regex.Match(e.Message, @"Table 'books'.*?logical reads (\d+)");
    if (m.Success) Console.WriteLine($"logical reads: {m.Groups[1].Value}");
};
conn.Execute("set statistics io on");

static void PrintPlans(SqlConnection conn, string marker)
{
    var plans = conn.Query<(string Text, int UseCounts)>("""
        select st.text, cp.usecounts
        from sys.dm_exec_cached_plans cp
        cross apply sys.dm_exec_sql_text(cp.plan_handle) st
        where st.text like '%' + @marker + '%' and st.text not like '%dm_exec%'
        """, new { marker });
    foreach (var plan in plans)
        Console.WriteLine($"{plan.UseCounts}x {plan.Text}");
}

Connection'ı açık tutmak burada önemli: statistics io bir session ayarı ve Dapper kapalı bir connection'ı her çağrıda açıp kapatsaydı ayar kaybolurdu. Her örneği ayrı bir süreçte, boş plan cache ile çalıştırdım.

ANSI ve Unicode: Neden 348 Sayfa?

Açılıştaki sorgudan sonra PrintPlans(conn, "where isbn") çağırınca cevap ortaya çıkıyor:

1x (@isbn nvarchar(4000))select id, title from dbo.books where isbn = @isbn

Dapper .NET string'ini nvarchar olarak gönderir, çünkü .NET string'leri Unicode'dur. Kolon ise varchar. SQL Server iki tarafı karşılaştırabilmek için kolonu nvarchar'a çevirir; cached plan'ın XML'inde bu CONVERT_IMPLICIT(nvarchar(13), ...) olarak görünüyor ve operatör "Index Seek" değil "Index Scan". Sunucunun collation'ı SQL_Latin1_General_CP1_CI_AS olduğu için optimizer bu dönüşümü bir aralık aramasına çeviremiyor ve index'i baştan sona okuyor. Windows collation'larında (Latin1_General_...) zarar daha küçüktür, ama yine de vardır.

Düzeltme, 04. yazıda tanıştığımız DbString:

var book = conn.QuerySingle<BookTitle>(
    "select id, title from dbo.books where isbn = @isbn",
    new { isbn = new DbString { Value = "9780441013593", IsAnsi = true, Length = 13 } });
Console.WriteLine(book.Title);

logical reads: 6
Dune
1x (@isbn varchar(13))select id, title from dbo.books where isbn = @isbn

348'den 6'ya. Bütün şemanın varchar olduğu eski bir veritabanındaysan her çağrıda DbString yazmak yorucu, ve burada bir tuzak var. DbString.IsAnsiDefault = true kulağa global bir ayar gibi geliyor ama yalnızca DbString instance'larının varsayılanını değiştiriyor; düz string parameter'lar etkilenmiyor. Denedim: aynı sorgu yine nvarchar(4000) ve 348 sayfa. Gerçekten global bir değişiklik istiyorsan string'in type map'ini değiştirmen gerekiyor:

SqlMapper.AddTypeMap(typeof(string), DbType.AnsiString);

logical reads: 6
1x (@isbn varchar(4000))select id, title from dbo.books where isbn = @isbn

Bu bir balyoz: bundan sonra uygulamadaki her string varchar gider. Bu ayar açıkken nvarchar olan authors.name kolonuna "Şule Çağlar" yazdım, geri okuduğumda "Sule Çaglar" geldi; sunucu değeri kendi kod sayfasına sığdırmak için sessizce değiştirdi. İki seçeneğin dengesi şöyle: DbString zahmetli ama noktasal ve güvenli; type map tek satır ama yalnızca şeman gerçekten tamamen varchar ise doğru.

String Uzunluğu ve Plan Cache

Plan metnindeki nvarchar(4000) rastgele değil. Parameter tanımı plan cache anahtarının bir parçasıdır; Dapper her string'i kendi uzunluğuyla gönderseydi "Dune" için nvarchar(4), "Dunes" için nvarchar(5) diye ayrı planlar oluşurdu. Dapper'ın kaynak kodundaki kural basit: 4000 karakter ve altı string'lerin boyutu 4000, üstü -1, yani max. Üç farklı uzunlukla denedim:

conn.Query<BookTitle>("select id, title from dbo.books where title = @title", new { title = "Dune" }).AsList();
conn.Query<BookTitle>("select id, title from dbo.books where title = @title", new { title = "Dunes" }).AsList();
conn.Query<BookTitle>("select id, title from dbo.books where title = @title", new { title = new string('x', 4001) }).AsList();
PrintPlans(conn, "where title");

1x (@title nvarchar(max) )select id, title from dbo.books where title = @title
2x (@title nvarchar(4000))select id, title from dbo.books where title = @title

İlk iki çağrı aynı planı paylaştı, 4001 karakterlik string ise kendi planını açtı. DbString'e Length verdiğinde bu kuralı kendin belirlemiş olursun; o yüzden Length'i kolon tanımına eşit tut, her çağrıda değişen bir değer verme.

IN Listeleri ve Plan Çoğalması

04. yazıdaki in @ids genişletmesi her element için ayrı bir parameter üretir. Liste boyu değiştikçe SQL metni de değişir:

foreach (var size in new[] { 6, 7, 8, 9 })
{
    var ids = Enumerable.Range(1, size).ToArray();
    conn.Query<BookTitle>("select id, title from dbo.books where id in @ids", new { ids }).AsList();
}

Plan cache'te bu döngüden sonra dört ayrı plan var. Her boy için bir plan derlemek CPU, her plan da bellek demek; kullanıcıların rastgele sayıda element seçtiği bir ekranda yüzlerce varyant birikebilir. Dapper'ın iki ayarı var. Settings.PadListExpansions listeyi son değeri tekrarlayarak doldurur; kaynak koddaki kurala göre 5 ve altı listeye dokunmaz, 6 ile 150 arasını 10'un katına tamamlar:

SqlMapper.Settings.PadListExpansions = true;

4x (@ids1 int,@ids2 int,@ids3 int,@ids4 int,@ids5 int,@ids6 int,@ids7 int,@ids8 int,@ids9 int,@ids10 int)select id, title from dbo.books where id in (@ids1,@ids2,@ids3,@ids4,@ids5,@ids6,@ids7,@ids8,@ids9,@ids10)

Dört farklı boy, tek plan, dört kullanım. İkinci ayar Settings.InListStringSplitCount: belirlediğin eşiğin üstündeki int, long, short ve byte listelerini virgülle birleştirip tek bir parameter olarak gönderir ve SQL'i SQL Server 2016'nın string_split fonksiyonuna çevirir:

SqlMapper.Settings.InListStringSplitCount = 5;

4x (@ids varchar(max) )select id, title from dbo.books where id in (select cast([value] as int) from string_split(@ids,','))

Bu kez liste boyu ne olursa olsun tek plan. Bedeli, veritabanının compatibility level'ının en az 130 olması ve optimizer'ın string_split'ten kaç satır geleceğini iyi tahmin edememesi. Her iki ayar da süreç geneli; ikisini birden açmana gerek yok.

Table-Valued Parameter'lar

Listeyi tek parça göndermenin SQL Server'a özgü, tipli yolu table-valued parameter (TVP). Önce sunucuda bir tablo tipi tanımlanır; setup'taki dbo.IdList bu iş için. Dapper tarafında bir DataTable doldurup AsTableValuedParameter ile tipini söylüyorsun:

var table = new DataTable();
table.Columns.Add("id", typeof(int));
foreach (var id in new[] { 1, 10000, 20000 })
    table.Rows.Add(id);

var books = conn.Query<BookTitle>(
    "select b.id, b.title from dbo.books b join @ids i on i.id = b.id",
    new { ids = table.AsTableValuedParameter("dbo.IdList") });

foreach (var b in books)
    Console.WriteLine($"{b.Id} {b.Title}");

logical reads: 9
1 Dune
10000 Book 10000
20000 Book 20000

DataTable biraz ağır bir araç. Aynı iş Microsoft.Data.SqlClient'ın SqlDataRecord'u ile de yapılabiliyor; Dapper 2.0.4'ten beri AsTableValuedParameter<T> IDataRecord uygulayan her tipi kabul ediyor:

var meta = new SqlMetaData("id", SqlDbType.Int);
var records = new[] { 1, 10000, 20000 }.Select(id =>
{
    var record = new SqlDataRecord(meta);
    record.SetInt32(0, id);
    return record;
});

var books = conn.Query<BookTitle>(
    "select b.id, b.title from dbo.books b join @ids i on i.id = b.id",
    new { ids = records.AsTableValuedParameter("dbo.IdList") });
Console.WriteLine(books.Count());

logical reads: 9
3

TVP'nin artısı liste boyundan bağımsız tek plan ve primary key sayesinde optimizer'a verdiğin garantiler; eksisi sunucudaki bir tip tanımına bağımlılık. Tipi değiştirmek için önce onu kullanan her procedure'ı bırakman gerekir. PostgreSQL'de TVP yok; orada doğal yol bir array parameter ve = any(@ids).

{=literal}: Değeri SQL'e Gömmek

Setup'taki ix_books_featured bir filtered index: yalnızca is_featured = 1 olan 20 satırı içeriyor. Bu index'i parameter'lı bir sorguyla kullanmaya çalışalım, sonra Dapper'ın literal syntax'ıyla:

var featured = conn.Query<BookTitle>(
    "select id, title from dbo.books where is_featured = {=featured}",
    new { featured = true }).AsList();
Console.WriteLine(featured.Count);

var parameterized = conn.Query<BookTitle>(
    "select id, title from dbo.books where is_featured = @featured",
    new { featured = true }).AsList();
Console.WriteLine(parameterized.Count);

PrintPlans(conn, "where is_featured");

logical reads: 2
20
logical reads: 821
20
1x (@featured bit)select id, title from dbo.books where is_featured = @featured
1x select id, title from dbo.books where is_featured = 1

Aynı 20 satır için 2 sayfaya karşı 821 sayfa. Parameter'lı planın sonraki çağrıda @featured = 0 için de doğru çalışması gerekiyor; bu yüzden optimizer yalnızca 1'leri içeren index'i seçemiyor ve tabloyu tarıyor. {=featured} ise değeri SQL metnine gömüyor, plan "bu sorgu her zaman 1 içindir" diyebiliyor.

Literal'lar yalnızca bool ve sayısal tiplerde çalışır. String vermeye kalkarsan Dapper reddeder:

conn.Query<BookTitle>("select id, title from dbo.books where title = {=title}", new { title = "Dune" }).AsList();

Unhandled exception. System.NotSupportedException: The type 'String' is not supported for SQL literals.

Bu kısıt bilinçli: sayı ve bool'un SQL'e güvenli biçimde yazılmasının tek bir yolu var, string'in yok. Yani {=x} SQL injection kapısı açmaz, ama her farklı değer ayrı bir plan demektir. Az sayıda sabit değer alan kolonlar (durum, tip, bayrak) için doğru araç, kullanıcı girdisi için değil.

Parameter Sniffing

SQL Server parameter'lı bir sorguyu ilk çalıştırdığında, plan'ı o andaki parameter değerine bakarak derler ve sonraki çağrılarda aynı planı kullanır. Çoğu zaman bu tam istediğimiz şey. Çarpık veride ise bir değer için mükemmel olan plan diğeri için felaket olabilir:

foreach (var authorId in new[] { 2, 1 })
{
    var count = conn.Query<BookTitle>(
        "select id, title from dbo.books where author_id = @authorId",
        new { authorId }).Count();
    Console.WriteLine($"author {authorId}: {count} rows");
}

logical reads: 34
author 2: 10 rows
logical reads: 300145
author 1: 99990 rows

İlk çağrı 10 satırlık yazar içindi: ix_books_author'dan seek ve her satır için bir lookup, harika bir plan. İkinci çağrı aynı planla 99.990 satırın her biri için lookup yaptı ve 300.145 sayfa okudu. Tablonun tamamını taramak 821 sayfa tutuyordu. Sırayı ters çevirdiğimde iki yazar da 821 sayfalık scan planına düştü; bu kez de 10 satırlık sorgu gereğinden pahalı oldu.

Dapper bu sorunu çözmez, çünkü sorun Dapper'da değil; ama çözümler SQL metninde olduğu için Dapper'la uygulaması kolay. option (recompile) eklediğinde her çağrı kendi planını derler:

"select id, title from dbo.books where author_id = @authorId option (recompile)"

logical reads: 34
author 2: 10 rows
logical reads: 821
author 1: 99990 rows

Bedeli her çağrıda derleme maliyeti; sık çalışan sorgularda pahalıdır. Değer kümesi küçükse {=authorId} ile her yazara ayrı bir plan vermek de aynı sonucu verdi ve plan cache'te iki ayrı satır oluştu. Üçüncü yol option (optimize for (@authorId unknown)) gibi bir ipucuyla optimizer'ı ortalama bir değere göre plan yapmaya zorlamak. Hangisinin doğru olduğu sorgunun ne sıklıkla çalıştığına ve verinin ne kadar çarpık olduğuna bağlı; tek doğru cevap yok.

Sık Düşülen Tuzaklar

varchar kolonlara düz string göndermek. Hata yok, sonuç doğru, sadece her sorgu index'i baştan sona okuyor. Şüphelendiğinde plan cache'teki parameter tanımına ya da plan'daki CONVERT_IMPLICIT uyarısına bak.

DbString.IsAnsiDefault'u global ayar sanmak. Yalnızca DbString instance'larını etkiler; düz string'ler için type map gerekir.

Kapalı connection'la session ayarı yapmak. set statistics io on, geçici tablolar ya da set arithabort gibi ayarlar session'a aittir. Dapper kapalı connection'ı her çağrıda açıp kapattığı için ayar bir sonraki sorguya ulaşmaz; connection'ı kendin aç.

Plan sorunlarını uygulamada çözmeye çalışmak. Parameter sniffing için koda if'ler eklemek yerine SQL'e ipucu ver ya da sorguyu ikiye böl. Dapper'ın burada tek işi, yazdığın SQL'i olduğu gibi göndermek.

EF Core da string parameter'ları varsayılan olarak nvarchar gönderir; orada kolonun varchar olduğunu modele söylediğinde (IsUnicode(false)) parameter tipi kendiliğinden düzelir. Dapper'da model olmadığı için bu bilgiyi her çağrıda ya da global type map'te sen vermek zorundasın. Bu, iki aracın felsefe farkının küçük ama öğretici bir örneği.

Küçük Bir Deneme

★☆☆ Açılış sorgusunu new DbString { Value = "9780441013593", IsAnsi = true } ile, yani Length vermeden çalıştır. Plan metninde parameter tanımı ne oldu, logical reads değişti mi?

★★☆ PadListExpansions açıkken 11, 15 ve 21 element'li üç liste gönder. Plan cache'te kaç plan ve her birinde kaç parameter olacağını kaynak koddaki kurala göre önce kâğıt üzerinde hesapla, sonra ölç.

★★★ author_id sorgusunu bir stored procedure'a taşı ve parameter sniffing'i orada yeniden üret. Sonra option (optimize for (@AuthorId unknown)) ekleyip iki yazar için logical reads değerlerini ölç ve option (recompile) ile karşılaştır.

SQL Server'ın inceliklerinden sonra tekrar provider'dan bağımsız konulara dönüyoruz. Bir sonraki yazıda dinamik SQL'i string birleştirmeden kurmanın yolu olan Dapper.SqlBuilder'ı ve Dapper.Contrib, Rainbow, Dommel, Dapper Plus gibi ekosistem paketlerini dengeli bir gözle değerlendireceğiz.