Veritabanında 10 kat hızlı rasgele kayıt seçim yöntemi.

[ssahinoglu]

Veritabanından rasgele kayıt seçerken her kayıt için bellekte değer üretip, bir de bunları sıralamayı fazla külfetli buluyorum. Büyük tablolar için tabi.

Eğer sık ihtiyaç oluyorsa, 0'dan başlayan kesintisiz sayaç numarasını garanti edip, 0 - MAX(SAYAÇ) aralığında rasgele sayı üretmeyi tercih ederim.

Aşamalar:
* SAYAÇ adlı bir alan oluşturulup başlangıçta tüm kayıtlar sıraya bakılmaksızın 0'dan itibaren numaralandırılır. Sonra bu alan için ters sıralı (descending) INDEX oluşturulur.
* Tabloda DELETE işlemi için TRİGGER oluşturulur. Silinen kaydın SAYAÇ numarası alınır. Sonra tablonun MAX(SAYAÇ) olan kaydına gidilip MAK SAYAÇ = SİLİNEN SAYAÇ yapılır.
* Rasgele değer bulmak istediğinizde 0 - MAX(SAYAÇ) arasında RANDOM() tamsayı değer üretmeniz yeterlidir.

Böylelikle rasgele bulduğunuz her sayı için, tabloda bir kaydın bulunması garantidir. Yani asla tüm kayıtlar üzerinden geçmezsiniz ve doğrudan tek bir satıra gidersiniz.

Fakat TABLODAN KAYIT SİLME İŞLEMİ ADEDİ, RASGELE KAYIT BULMA İŞLEMİ ADEDİ'nden çok fazlaysa bu yönteme gerek olmayabilir. Bu durumda klasik rasgele bulma yöntemi kullanmak muhtemelen daha uygundur.

Biraz önce 1 MİLYON kayıt için deneme yaptım, gayet performanslı çalışıyor.

* SAYAÇ yöntemi (100 deneme): 2 saniye
* Klasik RAND yöntemi (100 deneme): 25 saniye

Her şeye rağmen çoklu seçim yaparken aynı kayıtların gelmesi gibi ufak bir handikapı da vardır. Başka bir dezavantajı da tüm kayıtlar yerine koşula göre seçim yapmak gerektiğinde sıra sayacı tam olmaz ama bunun da koşula göre sayaç verme gibi bir çözümü mümkündür.


Trigger:

CREATE TRIGGER trgTablo_Sayac
   ON  dbo.Tablo
   AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @SilinenSayac INT
    DECLARE @MakSayac INT

    SELECT @SilinenSayac = Sayac FROM deleted
    SELECT @MakSayac = Max(Sayac) FROM Tablo

    UPDATE Tablo SET Sayac=@SilinenSayac WHERE Sayac=@MakSayac
END

Sayaç yöntemimle rasgele kayıt seçimi: 20 ms.

DECLARE @Mak INT = (SELECT Max(Sayac) FROM Tablo)

SELECT TOP 1 * FROM Tablo with(nolock)
WHERE Sayac = ROUND(((@Mak-1) * RAND()), 0)
-- TOP N için WHERE Sayac in (...)

Klasik yöntemle rasgele kayıt seçimi (sayaç ve trigger olmadan): 250 ms.

SELECT TOP 1 *, RAND(Kod) AS Say 
FROM Tablo with(nolock) ORDER BY Say

Yeri gelmişken mevcut bir tablonun sayaç alanına 0'dan itibaren sıra numaralarını yazacak CTE kodunu da vereyim. Bu da 1 milyon kayıt için 20 saniye sürdü.

WITH cteTablo(Kod,Sayac,YeniSayac)
AS
(
  SELECT Kod,Sayac,
         (row_number() OVER(order by (select 1))-1) as YeniSayac
  FROM tablo       
)
UPDATE cteTablo SET Sayac = YeniSayac

Güncellemeyi CURSOR ile yaptığımda ise 3 dakika sürdü. Kullanımını öğrenmek isteyecekler için o kodu da veriyorum.

SET NOCOUNT ON

DECLARE @Kod INT
DECLARE @Sayac INT

DECLARE CR CURSOR FOR
  SELECT Kod FROM Tablo with(nolock)

OPEN CR
FETCH NEXT FROM CR INTO @Kod

SET @Sayac=0

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE Tablo SET Sayac=@Sayac
  WHERE Kod=@Kod

  SET @Sayac = @Sayac+1
  FETCH NEXT FROM CR INTO @Kod
END

CLOSE CR
DEALLOCATE CR

[Bay_Y]

Teşekkürler, ayrıntılı bir bilgilendirme olmuş.

[Tuğrul HELVACI]

Bir de ORDER BY NEWID() deneyin ?

[Ahmet İPEKÇİ]

Hocam Stored Procedure kullanımında işlemlere göre procedurleri ayrı ayrı yazıp kullanmakla, toplu komutlar şeklinde kullanmanın performans açısından çok farkı varmı. Düşünceleriniz nedir bu konuda.

[ssahinoglu]

(16-12-2018, Saat: 18:58)Tuğrul HELVACI Adlı Kullanıcıdan Alıntı: Bir de ORDER BY NEWID() deneyin ?

Aynı yapı için farklı bir bilgisayarda denedim ve RAND(alan) %20 daha hızlı çıktı.
(Intel I5 işlemci, 1 milyon kayıt)

* RAND, 100 deneme: 25 saniye
* NEWID, 100 deneme: 31 saniye

  SELECT TOP 1 * FROM Tablo with(nolock) ORDER BY RAND(Kod)
 SELECT TOP 1 * FROM Tablo with(nolock) ORDER BY NEWID()

---

(16-12-2018, Saat: 20:26)Ahmet İPEKÇİ Adlı Kullanıcıdan Alıntı: Hocam Stored Procedure kullanımında işlemlere göre procedurleri ayrı ayrı yazıp kullanmakla, toplu komutlar şeklinde kullanmanın performans açısından çok farkı varmı. Düşünceleriniz nedir bu konuda.

Düz mantıkla bakınca fark olmaması lazım ama kullanım şekline göre bakarsın bir şeyler değişebilir de.