SQL Server'da yavaş sorgu nasıl hızlandırılır?

POS hareketlerini listeleyen bir endpoint'imiz vardı. Tabloya her gün milyonlarca satır ekleniyordu ve ekranın en sık kullanılan hâli gün bazlı sorguydu: bir işyerinin belli günlerdeki hareketleri, sayfa sayfa. Bu sorgu dakikalar sürüyordu. CreatedDate kolonunda indeks vardı, ama sorgu onu kullanmıyor gibiydi.

Dört değişiklikle sorgu dakikalardan saniyelere indi. Bu yazı için o dört adımı 10 milyon satırlık sentetik bir tabloda yeniden ölçtüm. Ölçümlerden biri, o gün suçladığımız şeyin asıl suçlu olmadığını gösterdi.

Kısa cevap: SQL Server'da yavaş bir listeleme sorgusunu hızlandırırken önce indeksin gerçekten kullanılıp kullanılmadığına bak. Kolona uygulanan fonksiyonlar (CONVERT, DATEDIFF) indeksi devre dışı bırakır; filtrelediğin kolonlar için bileşik indeks gerekir. Derin sayfalarda OFFSET atlanan her satırı okur; cursor (keyset) sayfalama her sayfada aynı maliyettedir. Geniş listelerde önce yalnızca Id'leri seç, kolonları sonra getir. Join'deki bir CTE her satır için yeniden çalışabilir; ara sonucu temp tabloya almak bunu bir kez çalışır hâle getirir.

Ölçüm düzeneği

Gerçek tabloyu ve verisini kullanamam, o yüzden benzerini kurdum. PosHareketleri tablosunda 10 milyon satır var: 30 gün, günde yaklaşık 330 bin hareket. Yanında işyerleri (20 bin), terminaller (60 bin) ve iadeler (200 bin) duruyor. CreatedDate kolonunda tek kolonlu bir indeks var. Bizim tablomuz 30-40 kolondu; buradaki 10 kolon, yani gerçekte farklar daha da büyür.

Ortam SQL Server 2022 Express. Sorgular uygulamanın göndereceği gibi parametreli (sp_executesql), her biri iki kez çalışıyor ve ikincisi sayılıyor; yani önbellek sıcak.

Süreyi raporluyorum ama asıl baktığım sayı mantıksal okuma (logical reads): sorgunun okuduğu 8 KB'lık sayfa sayısı. Süre makineye, önbelleğe ve o anki yüke göre oynar; okuma sayısı oynamaz. İkisini de şöyle açarsın:

SET STATISTICS IO, TIME ON; -- sorgu SET STATISTICS IO, TIME OFF; 

Messages sekmesinde her tablo için logical reads ve sorgunun CPU ve geçen süresi görünür. Yanına gerçek çalışma planını (actual execution plan, SSMS'te Ctrl+M) açarsan hangi operatörün bu okumaları yaptığını da görürsün.

Tarih filtresi: kolona fonksiyon uygulamak

Gün bazlı sorguda tarih filtresi CreatedDate'i güne çeviriyordu:

WHERE IsyeriId = @isyeri AND CAST(CreatedDate AS date) BETWEEN @bas AND @bit 

Bir kolona fonksiyon uyguladığında veritabanı genellikle o kolonun indeksinde arama (seek) yapamaz. İndeks CreatedDate'in kendisine göre sıralı; CAST(CreatedDate AS date) ya da CONVERT(char(8), CreatedDate, 112) sonucuna göre değil. Bu durumda her satır için fonksiyonu hesaplayıp sonucu karşılaştırmak zorunda kalır. Bu tür koşullara sargable olmayan (non-sargable) koşul denir.

Biz bunu düzelttik: gelen tarihleri bir gün kaydırıp aralık olarak yazdık.

WHERE IsyeriId = @isyeri AND CreatedDate >= @bas AND CreatedDate < DATEADD(day, 1, @bit) 

Bu yazı için ölçerken şunu gördüm: SQL Server CAST(... AS date) için bir istisna yapıyor. Kolonu date'e çevirmeyi tanıyor ve indekste yine aralık araması yapabiliyor. Tek günlük, ilk 50 satırlık sorguda dört farklı yazımın sonucu:

DATEDIFF indeksi baştan sona tarıyor, CONVERT bütün tabloyu okuyor. CAST ise aralıkla neredeyse aynı. Yani bizim sorgudaki asıl yük CAST değildi.

Aralıkla yazmak yine de doğru alışkanlık. CAST istisnası SQL Server'a özgü ve yalnızca date'e çevirmede geçerli; aynı kod başka bir veritabanında ya da başka bir fonksiyonla aynı şansı bulamaz. Aralık her yerde indeksi kullanır.

Asıl fark işyeri filtresinde çıktı. Aynı sorguyu bir işyeri ve yedi gün için üç farklı indeks durumunda çalıştırdım:

Yalnızca tarih indeksi varken yedi günlük aralık 2,3 milyon satıra denk geliyor. Optimizer bu kadar satır için indeksten tek tek tabloya gitmektense bütün tabloyu taramayı seçiyor; CAST'lı ve aralıklı yazımın ikisi de aynı taramaya düşüyor. Bileşik indekste önce işyerine, sonra o işyerinin tarih aralığına iniliyor ve sorgu yalnızca gösterilecek satırları okuyor.

Bileşik indekste kolon sırası önemli: eşitlikle filtrelenen kolon (IsyeriId) önce, aralıkla filtrelenen kolon (CreatedDate) sonra. Ters sırada indeks yine tarih aralığını tarar, işyerini her satırda ayrıca kontrol eder.

Derin sayfada OFFSET neden yavaşlar

Listede sayfalama vardı, ama yetersizdi. Klasik OFFSET ... FETCH sayfalaması şöyle görünür:

SELECT h.Id, h.CreatedDate, h.Tutar, h.KartMaskeli, i.Ad, t.SeriNo FROM PosHareketleri h JOIN Isyerleri i ON i.Id = h.IsyeriId JOIN Terminaller t ON t.Id = h.TerminalId WHERE h.CreatedDate >= @bas AND h.CreatedDate < @bit ORDER BY h.CreatedDate DESC, h.Id DESC OFFSET @atla ROWS FETCH NEXT 50 ROWS ONLY; 

OFFSET 50000 "50 bininci satırdan başla" demek değil, "50 bin satırı oku, at, sonra 50 satır ver" demek. Veritabanının atlanacak satırların yerini bilmesinin başka yolu yok. Sayfa ilerledikçe okunan ve atılan satır sayısı artar.

Bir günün hareketlerinde (yaklaşık 330 bin satır) 50'lik sayfalarla ölçünce:

1.000. sayfada işyeri ve terminal tablolarından da 103'er bin okuma var: sorgu atlanacak satırlar için de join yapıyor. 6.000. sayfada optimizer indeksi bırakıp tabloyu taramaya geçiyor. Bizim tablomuz 30-40 kolon olduğu için atılan her satır daha pahalıydı.

Geçici çözümümüz cursor sayfalama oldu. Fikir, "kaçıncı sayfadayım" yerine "en son hangi satırda kaldım" bilgisini taşımak. İstemci bir sonraki sayfayı isterken önceki sayfanın son satırının CreatedDate'ini ve Id'sini gönderir; sorgu oradan devam eder:

SELECT TOP (50) h.Id, h.CreatedDate, h.Tutar, h.KartMaskeli, i.Ad, t.SeriNo FROM PosHareketleri h JOIN Isyerleri i ON i.Id = h.IsyeriId JOIN Terminaller t ON t.Id = h.TerminalId WHERE h.CreatedDate >= @bas AND h.CreatedDate < @bit AND (h.CreatedDate < @sonTarih OR (h.CreatedDate = @sonTarih AND h.Id < @sonId)) ORDER BY h.CreatedDate DESC, h.Id DESC; 

Bu sorgu indekste doğrudan kaldığı yere iner ve 50 satır okur. Ölçümde her sayfa 220 civarı okuma ve 2-3 ms; 1. sayfayla 6.000. sayfa arasında fark yok.

Bedeli de var: kullanıcı "47. sayfaya git" diyemez, yalnızca ileri ve geri gidebilir. Listede toplam sayfa sayısı gösteriliyorsa onu ayrı bir COUNT ile hesaplamak gerekir. Hareket listesi gibi kullanıcının genellikle baştan aşağı kaydırdığı ekranlarda bu kayıp küçük.

GUID'li tenant'larda cursor

Bizde bazı tenant'ların Id'leri long, bazılarınınki GUID'di. GUID'li tenant'larda Id üzerinden büyüktür/küçüktür yapamıyorduk, o yüzden cursor'dan yalnızca long Id'li tenant'lar yararlanabildi. Aslında sıralama zaten CreatedDate'e göre; Id yalnızca aynı tarihli satırları ayırmaya yarıyor. Bu iş için Id'nin sıralı olması değil, tekil olması ve her seferinde aynı sırayı vermesi yeterli. SQL Server'da GUID'ler de kendi (tuhaf ama tutarlı) sıralarıyla karşılaştırılabildiği için yukarıdaki sorgu GUID'le de çalışır. Tek dikkat: son Id'yi uygulamada karşılaştırma, olduğu gibi SQL'e geri gönder; .NET'teki Guid sıralaması SQL Server'ınkiyle aynı değil.

Önce Id'leri seç, kolonları sonra getir

Cursor'ın kullanılamadığı tenant'lar için başka bir yol bulduk: önce olabildiğince filtreleyip yalnızca Id'leri seçmek, join'leri ve kolonları ikinci adımda yapmak.

WITH sayfa AS ( SELECT Id, CreatedDate FROM PosHareketleri WHERE CreatedDate >= @bas AND CreatedDate < @bit ORDER BY CreatedDate DESC, Id DESC OFFSET @atla ROWS FETCH NEXT 50 ROWS ONLY ) SELECT h.Id, h.CreatedDate, h.Tutar, h.KartMaskeli, i.Ad, t.SeriNo FROM sayfa s JOIN PosHareketleri h ON h.Id = s.Id JOIN Isyerleri i ON i.Id = h.IsyeriId JOIN Terminaller t ON t.Id = h.TerminalId ORDER BY s.CreatedDate DESC, s.Id DESC; 

İç sorgu yine OFFSET kullanıyor ve yine atlanan satırları okuyor, ama bunu yalnızca CreatedDate indeksinde yapıyor. İndeks dar: içinde tarih ve Id var, 30-40 kolon yok. Join'ler ve geniş satırlar yalnızca seçilen 50 Id için yapılıyor. Bu kalıba deferred join de deniyor.

Grafik: bir günlük hareketlerde (yaklaşık 330 bin satır, 50 satırlık sayfa) sayfa numarasına göre süre. OFFSET: 1. sayfa 2 ms, 100. sayfa 21 ms, 1.000. sayfa 176 ms, 6.000. sayfa 918 ms. Önce Id, sonra join: 6.000. sayfa 28 ms. Cursor: her sayfa 2-3 ms.

6.000. sayfada OFFSET 918 ms'ten bu yazımla 28 ms'e iniyor, okuma 170 binden 998'e düşüyor. Cursor kadar düz değil ama sayfa numarasına göre gitme imkânını koruyor.

Join'deki CTE her satırda çalışınca

Filtrelenmiş Id'leri önce bir CTE'de toplayıp ana sorguya join'liyorduk. Planı incelediğimizde CTE'nin her satır için yeniden çalıştığını gördük ve temp tabloya geçmek zorunda kaldık.

Sebebi, SQL Server'ın CTE'yi bir ara tablo gibi saklamaması. CTE, sorguya gömülen bir alt sorgudur (inline). Optimizer onu nested loops join'in iç tarafına koyarsa, dış taraftan gelen her satır için o alt sorgu yeniden çalışır. Sentetik tabloda aynı şekli kurdum: bir işyeri aralığı ve bir haftalık hareketlerin Id'leri CTE'de, dışarıda join'ler ve iade toplamı.

WITH secilen AS ( SELECT h.Id FROM PosHareketleri h WHERE h.IsyeriId BETWEEN @isyeriBas AND @isyeriBit AND h.CreatedDate >= @bas AND h.CreatedDate < @bit ) SELECT h.Id, h.CreatedDate, h.Tutar, h.KartMaskeli, i.Ad, t.SeriNo, (SELECT SUM(r.IadeTutar) FROM Iadeler r WHERE r.HareketId = h.Id) AS IadeToplam FROM PosHareketleri h JOIN Isyerleri i ON i.Id = h.IsyeriId JOIN Terminaller t ON t.Id = h.TerminalId WHERE h.Id IN (SELECT Id FROM secilen) ORDER BY h.CreatedDate DESC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY; 

Gerçek planda optimizer dış tarafta tarih indeksini sondan başa tarıyor ve CTE'nin PosHareketleri aramasını her satır için bir kez çalıştırıyor. Plandaki "Number of Executions" değeri 5.002.003. Sonuç 40,6 milyon okuma ve 9 saniye.

Temp tabloyla aynı iş iki adım:

SELECT h.Id, h.CreatedDate INTO #secilen FROM PosHareketleri h WHERE h.IsyeriId BETWEEN @isyeriBas AND @isyeriBit AND h.CreatedDate >= @bas AND h.CreatedDate < @bit; SELECT h.Id, h.CreatedDate, h.Tutar, h.KartMaskeli, i.Ad, t.SeriNo, (SELECT SUM(r.IadeTutar) FROM Iadeler r WHERE r.HareketId = h.Id) AS IadeToplam FROM #secilen s JOIN PosHareketleri h ON h.Id = s.Id JOIN Isyerleri i ON i.Id = h.IsyeriId JOIN Terminaller t ON t.Id = h.TerminalId ORDER BY h.CreatedDate DESC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY; 

Filtre bir kez çalışıyor, sonuç #secilen'e yazılıyor. SQL Server temp tabloya istatistik de oluşturduğu için ikinci sorguda kaç satırla çalışacağını biliyor. İki adım toplam 0,85 saniye; CTE'li hâlin onda biri. Bizde de temp tablo sorguya özeldi; sorgu bitince düşüyordu, ayrıca indeks koymadık.

Temp tablonun bedeli, sorgunun artık tek bir ifade olmaması. ORM'in ürettiği tek bir SELECT ile yapılamıyor; ya ham SQL ya da stored procedure gerekiyor. Ara sonuç çok büyükse tempdb'ye yazmanın maliyeti de hesaba girer.

Kendi sisteminde nasıl fark edersin

Yavaş bir sorguyu SET STATISTICS IO ON ile çalıştır ve okuma sayısını, sorgunun döndürdüğü satır sayısıyla karşılaştır. 50 satır gösteren bir liste için yüz binlerce okuma görüyorsan sorgu ihtiyacından fazlasını okuyor.

Sonra gerçek çalışma planında iki şeye bak: indeks üzerinde Seek yerine Scan olan operatörler ve "Number of Executions" değeri 1'den çok büyük olan operatörler. İlki indeksin kullanılmadığını, ikincisi bir alt sorgunun ya da CTE'nin her satırda yeniden çalıştığını gösterir. EF Core kullanıyorsan önce üretilen SQL'i log'dan al; Include ve Select yazısında ORM tarafındaki karşılığını anlatmıştım.

Bizim sorguyu dakikalardan saniyelere indiren tek bir değişiklik olmadı. Ölçümde dört adımın ortak noktası aynı çıktı: ekranda gösterilmeyecek satırı okumamak.

Bu yazı ilk olarak huseyinbozdemir.com'da yayımlandı.