Son işlemler raporu açılmıyordu. Ekran ya timeout'a düşüyor ya da 3 dakika sonra geliyordu, kullanıcı o arada çoktan sayfayı yenilemiş oluyordu. Bu yazıda o rapor sorgusunu EF Core tarafında Include yerine Select kullanarak nasıl 9 saniyeye indirdiğimizi anlatıyorum.
Sorgu SQL Server'da yavaştı ama sorun onu üreten EF Core koduydu. Tablo çok genişti, rapor da tek bir işlemi göstermek için işyerine, karta, bankaya, iadelere ve durum geçmişine uzanıyordu. Kodda bunların hepsi birer Include olarak duruyordu.
EF Core'un log'unu açınca sebebi gördük: gereksiz bir sürü select, gereksiz subquery'ler. Rapor ekranda birkaç kolon gösteriyordu, sorgu ise o işlemle ilişkili her tablonun bütün kolonlarını getiriyordu.
Kısa cevap: raporda Include kullanma, Select ile projeksiyon yap. Include ilişkili entity'leri bütün kolonlarıyla yükler; koleksiyon Include'ları ana satırı çoğaltır. Select ise yalnızca raporun gösterdiği alanları ister, toplamları da SQL'de hesaplatır. Bizde rapor bu değişiklikle 3 dakikadan 9 saniyeye düştü.
Bu yazıda o raporun nasıl düzeldiğini, projeksiyona geçerken çıkan tuzağı, EF Core'un kendi çözümü olan AsSplitQuery'yi ve hâlâ çözmediğimiz kısmı anlatacağım. Örnekler sadeleştirilmiş, alan adları jenerik; mantık aynı.
Include sana ne getiriyor?
Include, bir entity'yi yüklerken ilişkili entity'leri de aynı sorguda yüklemeyi söyler (eager loading). Entity'nin tamamını, tablonun bütün kolonlarıyla yükler. Bir ödemeyi işyeriyle birlikte istediğinde işyeri tablosunun adresi, vergi bilgisi, ayarları da gelir.
Raporu ilk yazdığımızda sorgu kabaca şöyleydi:
var payments = await db.Payments
.Include(p => p.Merchant)
.Include(p => p.Card).ThenInclude(c => c.Bank)
.Include(p => p.Refunds)
.Include(p => p.StatusHistory).ThenInclude(h => h.ChangedBy)
.Where(p => p.PaymentDate >= from && p.PaymentDate < to)
.OrderByDescending(p => p.PaymentDate)
.Skip(page * PageSize).Take(PageSize)
.ToListAsync(ct);
var rows = payments.Select(p => new PaymentRow(
p.Id,
p.PaymentDate.ToString("dd.MM.yyyy HH:mm"),
MoneyFormat.Format(p.Amount, p.Currency),
p.Merchant.Name,
p.Card.MaskedPan,
p.Card.Bank.Name,
p.Refunds.Sum(r => r.Amount),
p.StatusHistory.Max(h => h.ChangedAt)));
Okurken masum görünüyor. Sayfalama var, filtre var, 20 satır geliyor. Sorun SQL tarafında.
Tekil ilişkiler (Merchant, Card, Bank) birer JOIN olur ve her biri kendi tablosunun bütün kolonlarını sonuca ekler. Koleksiyonlar (Refunds, StatusHistory) daha pahalıdır: EF Core varsayılan olarak her şeyi tek sorguda getirdiği için iki koleksiyon da ödeme satırına JOIN'lenir ve satırlar çarpılır. İki iadesi ve üç durum kaydı olan bir ödeme veritabanından altı satır olarak döner; ödemenin, işyerinin, kartın ve bankanın bütün kolonları bu altı satırın her birinde tekrar eder. Buna cartesian explosion deniyor.
Örnek bir ödeme: iki iadesi ve üç durum kaydı var. Include'lu sorgu bu ödeme için altı satır ve on dört kolon döndürür; ödemenin, işyerinin, kartın ve bankanın kolonları her satırda tekrar eder, satırlar iki iade ile üç durum kaydının çarpımından gelir. Select projeksiyonu aynı ödeme için tek satır ve yalnızca raporun gösterdiği kolonları döndürür; iade toplamı ve son durum tarihi SQL'de hesaplanır.
Bir de sayfalama var. Satırlar çarpılınca Skip/Take'i doğrudan sonuca uygulamak yanlış olurdu (20 ödeme yerine 20 JOIN satırı alırdın). EF Core bunu doğru yapar: önce 20 ödemeyi bir alt sorguda seçer, sonra ilişkileri o alt sorguya bağlar. Sonuç doğru, ama log'da gördüğümüz subquery'ler buradan geliyordu. Sadeleştirilmiş hali kabaca şöyle:
SELECT p.*, m.*, c.*, b.*, r.*, h.*, u.* -- her tablonun bütün kolonları
FROM (
SELECT * FROM Payments
WHERE PaymentDate >= @from AND PaymentDate < @to
ORDER BY PaymentDate DESC
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY
) AS p
JOIN Merchants m ON ...
JOIN Cards c ON ...
JOIN Banks b ON ...
LEFT JOIN Refunds r ON ...
LEFT JOIN (SELECT ... FROM StatusHistory h JOIN Users u ON ...) AS h ON ...
ORDER BY p.PaymentDate DESC, p.Id, m.Id, c.Id, b.Id, r.Id, h.Id
Ekranın gösterdiği sekiz alan için bu kadar veri geliyordu. Sorguda AsNoTracking() yoksa C# tarafı bir de her entity'yi change tracker'a kaydeder; raporda hiçbir şeyi değiştirmediğin halde.
Üretilen SQL'i görmek
Biz sorunu EF Core'un log'unda gördük. İki yolu var. Geliştirme ortamında context'e log bağlarsın:
builder.Services.AddDbContext<AppDbContext>(o => o
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information));
Ya da tek bir sorgunun SQL'ini çalıştırmadan alırsın: ToListAsync'ten önceki IQueryable üzerinde ToQueryString() çağırırsın. Rapor sorgusunu yazarken bir kez bakmak çoğu zaman yeter.
Log'da parametre değerlerini de görmek için EnableSensitiveDataLogging() diye bir seçenek var. Ödeme sisteminde bunu yalnızca yerelde aç; parametrelerin içinde kart ya da müşteri bilgisi olabilir ve log'a düşen veriyi geri toplamak zor. Bunu log maskeleme yazısında ayrıca anlattım.
Rapor ne istiyorsa onu iste: Select ile projeksiyon
Çözümün ilk ve en büyük adımı Include'ları atıp sorguyu doğrudan raporun ihtiyacına göre yazmak oldu:
var data = await db.Payments
.Where(p => p.PaymentDate >= from && p.PaymentDate < to)
.OrderByDescending(p => p.PaymentDate)
.ThenByDescending(p => p.Id)
.Select(p => new PaymentRowData(
p.Id,
p.PaymentDate,
p.Amount,
p.Currency,
p.Merchant.Name,
p.Card.MaskedPan,
p.Card.Bank.Name,
p.Refunds.Sum(r => (decimal?)r.Amount) ?? 0m,
p.StatusHistory.Max(h => (DateTime?)h.ChangedAt)))
.Skip(page * PageSize).Take(PageSize)
.ToListAsync(ct);
Burada birkaç şey birden değişiyor:
-
Navigation'lar hâlâ kullanılabiliyor.
p.Merchant.Nameyazdığında EF Core JOIN'i kendisi kurar, ama yalnızcaNamekolonunu seçer.Includeyazmana gerek yok. Sorgu artık entity değil DTO döndürdüğü için yazsan da EF CoreInclude'u yok sayar. EF Core 10'da denedim: ikiIncludeeklenmiş sorgunun SQL'inde yine yalnızca seçilen kolonlar vardı. -
Koleksiyonlar satır çoğaltmıyor. İade toplamı ve son durum tarihi SQL'de alt sorgu olarak hesaplanıyor; her ödeme tek satır dönüyor.
(decimal?)ve?? 0miadesi olmayan ödemedeSUM'ınNULLdönmesine karşı. -
Change tracking yok. Projeksiyon entity döndürmediği için EF Core sonucu takip etmez. Raporda
AsNoTracking()yazmayı unutup unutmadığını düşünmene gerek kalmaz. -
Sıralama tekil.
ThenByDescending(p => p.Id)aynı saniyede düşmüş ödemeler arasında sırayı sabitliyor. Neden önemli olduğu aşağıda,AsSplitQuerybölümünde.
Bu değişiklikle rapor 3 dakikadan 9 saniyeye indi. SP'ye ya da ham SQL'e geçmeye gerek kalmadı. Mimaride EF Core kullanıyoruz; projeksiyonla yetinebildiğimiz sürece orada kalıyoruz.
EF Core "could not be translated" hatası neden çıkar?
Projeksiyona geçerken ilk denemede formatlama gibi C# metotları da sorgunun içindeydi ve "could not be translated" hatası aldık. Hangi satırın patlattığını bugün tam hatırlamıyorum, bir C# fonksiyonuydu. Kural ise belli: EF Core, SQL'e çeviremediği bir C# metodunu yalnızca en dıştaki Select'te kabul eder. O durumda gereken kolonları SQL'den alır, metodu bellekte çalıştırır. Client evaluation dokümanı buna "top-level projection" diyor. Metodun sonucu Where'e ya da OrderBy'a girerse, ya da metot en dıştaki Select'in dışında bir yerde kalırsa EF Core sorguyu SQL'e çeviremez ve hata verir.
Bu yazı için davranışı EF Core 10'da küçük bir örnekle yeniden denedim. Metot en dıştaki Select'te kaldıkça arkasından Skip/Take gelmesi sorun çıkarmadı: EF Core sayfalamayı SQL'de, formatlamayı bellekte yaptı. Formatlanmış alana göre sıralamak istediğimde hata geldi:
.Select(p => new
{
p.Id,
Tutar = MoneyFormat.Format(p.Amount, p.Currency), // C# metodu
p.Merchant.Name,
})
.OrderBy(r => r.Tutar) // metodun sonucuna göre sıralama: SQL'e çevrilemez
.Skip(page * PageSize).Take(PageSize)
.ToListAsync(ct); // InvalidOperationException: ... could not be translated.
MoneyFormat.Format'ın SQL karşılığı yok, sıralamayı veritabanına yaptıramazsın. Sıralama ve filtre her zaman ham değer üzerinden yapılmalı.
Çözüm sırayı ayırmak oldu. SQL'den ham değerleri sayfalanmış olarak al, formatlamayı sonra bellekte yap:
// data: yukarıdaki projeksiyon sorgusundan gelen 20 satır
return data.Select(d => new PaymentRow(
d.Id,
d.PaymentDate.ToString("dd.MM.yyyy HH:mm"),
MoneyFormat.Format(d.Amount, d.Currency),
d.MerchantName,
d.MaskedPan,
d.BankName,
MoneyFormat.Format(d.RefundTotal, d.Currency),
d.LastStatusAt)).ToList();
Formatlama artık yalnızca sayfadaki 20 kayıt üzerinde çalışıyor; maliyeti ölçülemeyecek kadar küçük. Bu ayrımın bir faydası daha var: tarih ve para biçimi kültüre bağlı bir sunum işi. SQL'e taşınsaydı bile orada durmaması gerekirdi.
AsSplitQuery: EF Core'un kendi çözümü
"Include yavaş" diye aradığında karşına çıkan ilk öneri büyük ihtimalle AsSplitQuery() olur. EF Core 5'ten beri var ve koleksiyon Include'larında satır çarpılmasını çözüyor: tek sorgu yerine her koleksiyon için ayrı bir SQL sorgusu çalıştırır. Ödemeler bir sorguda, iadeler ikinci sorguda, durum geçmişi üçüncü sorguda gelir; satırlar çarpılmaz.
Ama yalnızca satır çarpılmasını çözer. Her entity yine bütün kolonlarıyla gelir, change tracking yine çalışır, veritabanına bir yerine üç kez gidilir. Sorgular arasında veri değişirse (transaction içinde değilsen) ödeme bir anın, iadeleri bir sonraki anın verisi olabilir.
Raporu düzelttiğimizde AsSplitQuery'yi bilmiyordum. Bilseydim de projeksiyonu seçerdim; projeksiyon bence daha esnek. Raporun hangi alanı istediğine sen karar veriyorsun, toplamı SQL'de mi bellekte mi yapacağını sen seçiyorsun. AsSplitQuery ise entity'yi tam yüklemenin gerçekten gerektiği yerde, örneğin bir ödemeyi ilişkileriyle düzenlemek için açtığın ekranda anlamlı.
Sayfalamada sıralama tekil olmalı
AsSplitQuery'yi Skip/Take ile kullanıyorsan bilmen gereken bir tuzak var. Bölünmüş sorguların her biri "hangi 20 ödeme?" sorusunu veritabanına yeniden sorar: iadeleri getiren sorgu, ödemeleri seçen filtreyi, sıralamayı ve OFFSET'i bir alt sorgu olarak tekrarlar. Cevabın her seferinde aynı çıkması gerekir.
Raporu yalnızca PaymentDate'e göre sıralıyorsan ve aynı saniyede birden fazla ödeme düşmüşse (ödeme sisteminde sık olur), veritabanı eşit satırları sorgudan sorguya farklı sırada döndürebilir. Ana sorgu 20. sıraya A ödemesini koyar, iade sorgusu B'yi. Bir ödemenin iadeleri boş gelir ya da sayfada olmayan bir ödemenin iadeleri yüklenir. Hata almazsın, rapor sessizce yanlış olur.
EF Core 10 bu konuda bir adım attı: sıralama tekil değilse primary key'i ORDER BY'a kendisi ekliyor (dotnet/efcore#26808). Ama Ekim'de açılan ve hâlâ açık olan #39206 numaralı kayda göre bir durumda düzeltme işlemiyor: split query, projeksiyon, Skip/Take ve projeksiyonda bir tekil ilişki (p.Merchant.Name gibi) bir aradaysa sayfalama alt sorgusunda key yine yok ve bazı satırların koleksiyonları boş geliyor. Bu tarif bizim rapora birebir uyuyor.
Hangi sürümde olursan ol, sıralamanın sonuna ThenBy(p => p.Id)'yi elle yaz. Hiçbir bedeli yok ve sürümün ne yaptığını düşünmek zorunda kalmazsın.
.NET 8'den 10'a geçince
Biz bu süreçte .NET 8'den .NET 10'a da geçtik ve EF Core'un bazı ifadeleri SQL'e farklı çevirdiğini gördük. Bizim için iyi yönde oldu, sorgular daha temiz çevrilmeye başladı. Listeyle yapılan filtrelerde EF Core 8 gereksiz bir subquery kuruyordu, EF Core 10 bunun yerine IN'li yaklaşımı kullandı.
Bu değişiklik belgelenmiş: EF Core 10'da ids.Contains(p.Id) gibi bir listeyle filtreleme artık varsayılan olarak tek bir JSON parametresinden okuyan alt sorgu (OPENJSON) yerine ayrı ayrı parametrelerle (IN (@ids1, @ids2, ...)) çevriliyor. EF Core 10'un kırıcı değişiklikler listesinde "düşük etkili" diye geçiyor. Yalnız rapor filtresinde binlerce id gönderiyorsan dikkat et: her id ayrı bir parametre oluyor ve SQL Server bir sorguda en fazla 2.100 parametre kabul ediyor.
Ders basit: sürüm yükseltirken rapor sorgularının SQL'ine bir kez daha bak. LINQ aynı kalır, altındaki SQL değişebilir.
Excel'e alma hâlâ yavaş
Ekrandaki rapor düzeldi, ama aynı raporun Excel'e alınması hâlâ yavaş. Orada sayfalama yok: 100 bin satırın üstünde veri tek seferde isteniyor ve ToList() ile önce belleğe alınıyor, sonra dosyaya yazılıyor.
Burada iki ayrı darboğaz var ve ikisini karıştırmamak gerekiyor. Birincisi veritabanı: 20 satırlık sayfada fark edilmeyen bir tarama, 100 bin satırda kendini gösterir. Bunun için PaymentDate, Status ve birkaç kolon daha üzerinde index düşünüyoruz; filtrenin ve sıralamanın kullandığı kolonlar. Bileşik indeksin ve sayfalamanın SQL Server'daki etkisini yavaş sorgu yazısında ölçmüştüm. İkincisi bellek: bütün sonucu ToList() ile listeye alıp sonra yazmak, satır sayısı kadar nesneyi aynı anda bellekte tutmak demek. EF Core'da AsAsyncEnumerable() sonuçları satır satır akıtır; ama bunun faydası, Excel'i yazan kütüphanenin de satır satır yazabilmesine bağlı.
Bunu henüz çözmedik.
Kendi raporunda nasıl fark edersin?
Rapor sorgunda Include görüyorsan dur ve sor: bu entity'nin tamamına gerçekten ihtiyacım var mı? Sorgunun ToQueryString() çıktısında JOIN sayısına ve SELECT'teki kolon sayısına bak. Veritabanından dönen satır sayısı ekranda gösterdiğin satır sayısından fazlaysa koleksiyonlar satırları çarpıyordur. Select içinde bir C# metodu çağırıyorsan sonucunu sıralamada ya da filtrede kullanma, formatlamayı sayfalamadan sonra bellekte yap.
Raporun sorusu birkaç kolondur; sorguyu o soruya göre yaz, entity modeline göre değil.
Bu yazı ilk olarak huseyinbozdemir.com'da yayımlandı.
Top comments (0)