SQL While Cursor Bulk Insert Kullanımı ve Performans Karşılaştırması

Merhaba Sevgili Okurlar,
Bu yazımda SQL sorgu performansımızı artıran yazı dizisine bir yenisini daha ekliyorum. SQL sorgularımızda Cursor ,While ve Bulk Insert  kullanımını görüp karşılaştırması yapacağız.

Okurken ne dinlemeli ?


Senaryomuzda süper kahramanlar veri tabanımız ve detaylarının güncellenmesi,yeni kahramanların kaydedilmesi gibi düşünebiliriz.Bunun için SQL update ,insert komutları gerekli olacak.Döngüyü Cursor ile While sağlayıp ya da aynı iş  Bulk olarak gerçekleştireceğiz.


İlk olarak Cursor ile başlayalım :
Bir cursor tanımlayıp SELECT ile istediğimiz tabloyu cursor'e tanımlıyoruz ve FETCH ... INTO komutu ile satır satır tabloyu dolaşıyoruz.

CREATE PROCEDURE [HEROES].[HERO_CREATE]
ASBEGIN

BEGIN TRAN    

    
      DECLARE SUPERHEROES_LIST_CURSOR CURSOR FOR      SELECT S.BATMAN_ID, S.IRONMAN_ID, S.HULK_ID
      FROM HEROES.SUPERHEROES S (NOLOCK)      WHERE DETAIL_CREATED = 'N'      ORDER BY S.BATMAN_ID, S.IRONMAN_ID

      OPEN SUPERHEROES_LIST_CURSOR
      FETCH SUPERHEROES_LIST_CURSOR INTO @BATMAN_ID, @IRONMAN_ID, @HULK_ID
      BEGIN
    

                  ---------------UPDATE PROCESSES -------------------
                  UPDATE HEROES.OLD_HEROES
                  SET BATMAN_ID = @BATMAN_ID
                  WHERE                  IS_CURRENTLY_WORKING = 'Y' AND                  HERO_STATUS IN (1, 5) AND                  HULK_ID = @HULK_ID AND                  IRONMAN_ID = @IRONMAN_ID AND                                    
                  BATMAN_ID IS NULL                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                
    

                  ---------------INSERT PROCESSES -------------------                  INSERT INTO [HEROES].[HEROES_DETAIL]
                  ([BATMAN_ID], [NOMINATE_DATE]
                  SELECT                              [HEROES].[CURRENT_HEROES].BATMAN_ID,                              convert(varchar,CREATED_DATE, 104),                  

                  FROM HEROES.REWARDED_TEAM RT(NOLOCK)                  INNER JOIN COMMON.[HERO_CORE] HC (NOLOCK) ON RT.HULK_ID = HC.HULK_ID
            INNER JOIN [HEROES].[CURRENT_HEROES] CH  ON CH.BATMAN_ID=RT.BATMAN_ID
                  WHERE  EXISTS(select 1 from CH where RT.BATMAN_ID=CH.BATMAN_ID AND RT.IRONMAN_ID = CH.IRONMAN_ID)                  AND RT.AVENGERS_ID IS NULL                  GROUP BY CH.BATMAN_ID,Year(CREATED_DATE), Month(CREATED_DATE),Day(CREATED_DATE),HC.IS_ACTIVE
                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                
                
    
            FETCH SUPERHEROES_LIST_CURSOR INTO @BATMAN_ID, @IRONMAN_ID, @HULK_ID
      END      CLOSE SUPERHEROES_LIST_CURSOR
      DEALLOCATE SUPERHEROES_LIST_CURSOR

 Son olarak Close ve Deallocate ile Cursoru kapatıp bellekteki alanını serbset bırakıyoruz.

While ile örneğimize devam edelim:

While 'da ise temp table oluşturuyoruz döngüye sokmak istediğimiz tablonunun tüm kolonlarına ihtiyacımız yoksa ve fazlaca property 'si olan bir tablo ise böyle yapmak faydalı olabilir.Temp tablomuza ID tanımlıyoruz ve Bu ID yi hızlı olması için Identity ve PK tanımlıyoruz. Ayrıca döngüde artıracağımız MyCount değişkenini de kontrol etmek için gerekli olacak.

CREATE PROCEDURE [HEROES].[HERO_CREATE]
ASBEGIN      DECLARE @BATMAN_ID INT      DECLARE @IRONMAN_ID INT      DECLARE @HULK_ID SMALLINT
    
      Declare @MyCount int      set @MyCount = 0
BEGIN TRAN    
      CREATE TABLE #MyTmpData
      (      ID INT IDENTITY(1, 1) primary key ,      BATMAN_ID INT,      IRONMAN_ID INT,      HULK_ID INT,
      )
                  IF(@@ERROR > 0)            BEGIN                  IF(@@TRANCOUNT > 0)                  BEGIN                        ROLLBACK TRAN                        RETURN                  END            END
insert into #MyTmpData (BATMAN_ID,IRONMAN_ID, HULK_ID)Select S.BATMAN_ID, S.IRONMAN_ID, S.HULK_ID
from HEROES.SUPERHEROES S (NOLOCK)WHERE DETAIL_CREATED = 'N'

            IF(@@ERROR > 0)            BEGIN                  IF(@@TRANCOUNT > 0)                  BEGIN                        ROLLBACK TRAN                        RETURN                  END            END          
                              IF(@@TRANCOUNT > 0)

      while exists  (Select ID from #MyTmpData a where a.ID > @MyCount)      BEGIN
       SET @MyCount = @MyCount + 1     SET @BATMAN_ID= (Select S.BATMAN_ID from #MyTmpData S (NOLOCK) where ID = @MyCount)       SET @IRONMAN_ID=( Select S.IRONMAN_ID from #MyTmpData S (NOLOCK) where ID = @MyCount)      SET @HULK_ID= (Select S.HULK_ID from #MyTmpData S (NOLOCK) where ID = @MyCount)


                  ---------------UPDATE PROCESSES -------------------
                  UPDATE HEROES.OLD_HEROES
                  SET BATMAN_ID = @BATMAN_ID
                  WHERE                  IS_CURRENTLY_WORKING = 'Y' AND                  HERO_STATUS IN (1, 5) AND                  HULK_ID = @HULK_ID AND                  IRONMAN_ID = @IRONMAN_ID AND                                    
                  BATMAN_ID IS NULL                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                 
                  ---------------INSERT PROCESSES -------------------                  INSERT INTO [HEROES].[HEROES_DETAIL]
                  ([BATMAN_ID], [NOMINATE_DATE],[HERO_HEIGHT],[HERO_WEIGHT]
                  SELECT                              @BATMAN_ID,                              convert(varchar,CREATED_DATE, 104),      
                    SUM((RT.HEIGHT_RATE * HC.HEIGHT)) * 2.0,
                              POWER(10, HC.WEIGHT),                         

                  FROM HEROES.REWARDED_TEAM RT(NOLOCK)                  INNER JOIN COMMON.[HERO_CORE] HC (NOLOCK) ON RT.HULK_ID = HC.HULK_ID
                  WHERE EXISTS(select 1 from HEROES.VALIDATE_TEAM VT where VT.BATMAN_ID=@BATMAN_ID AND VT.IRONMAN_ID = @IRONMAN_ID)                  AND RT.AVENGERS_ID IS NULL AND RT.BATMAN_ID=@BATMAN_ID
                  GROUP BY #MyTmpData.BATMAN_ID,Year(CREATED_DATE), Month(CREATED_DATE),Day(CREATED_DATE),HC.IS_ACTIVE
                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                
                
                
                
                        IF(@@TRANCOUNT > 0)          COMMIT TRAN       

      END

 Son olarak aynı işlevi görebilen fakat döngü içermeyen bir yöntem deneyeceğiz Bulk Insert Koşullara göre eşleştirdiğimiz tabloları update ve insert edeceğiz. Daha önce koşullara uyan veriyi ilk olarak çekip içinde dönerek insert ya da update işlemini gerçekleştirdik fakat bu aşamada update ya da insert esnasında koşul sağlama işlemini gerçekleştireceğiz.Elbette bu durumda bazı sorunlar da ortaya çıkacak.İlk olarak yanlış senaryo ile başlayalım.

CREATE PROCEDURE [HEROES].[HERO_CREATE]
ASBEGIN      DECLARE @BATMAN_ID INT      DECLARE @IRONMAN_ID INT      DECLARE @HULK_ID SMALLINT

BEGIN TRAN    
      CREATE TABLE #MyTmpData
      (      ID INT IDENTITY(1, 1) primary key ,      BATMAN_ID INT,      IRONMAN_ID INT,      HULK_ID INT,
      )
                  IF(@@ERROR > 0)            BEGIN                  IF(@@TRANCOUNT > 0)                  BEGIN                        ROLLBACK TRAN                        RETURN                  END            END
insert into #MyTmpData (BATMAN_ID,IRONMAN_ID, HULK_ID)Select S.BATMAN_ID, S.IRONMAN_ID, S.HULK_ID
from HEROES.SUPERHEROES S (NOLOCK)WHERE DETAIL_CREATED = 'N'

            IF(@@ERROR > 0)            BEGIN                  IF(@@TRANCOUNT > 0)                  BEGIN                        ROLLBACK TRAN                        RETURN                  END            END          

    



                  ---------------UPDATE PROCESSES -------------------
                  UPDATE OH
                  SET OH.BATMAN_ID = #MyTmpData.BATMAN_ID
                  FROM HEROES.OLD_HEROES AS OH
                  INNER JOIN #MyTmpData  ON #MyTmpData.BATMAN_ID=OH.BATMAN_ID
                  WHERE                  OH.IS_CURRENTLY_WORKING = 'Y' AND                  OH.HERO_STATUS IN (1, 5) AND                  OH.HULK_ID = #MyTmpData.HULK_ID AND                  OH.IRONMAN_ID = #MyTmpData.IRONMAN_ID AND                                    
                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                


                  ---------------INSERT PROCESSES -------------------                  INSERT INTO [HEROES].[HEROES_DETAIL]
                  ([BATMAN_ID], [NOMINATE_DATE]
                  SELECT                              #MyTmpData.BATMAN_ID,                              convert(varchar,CREATED_DATE, 104),                  

                  FROM HEROES.REWARDED_TEAM RT(NOLOCK)                  INNER JOIN COMMON.[HERO_CORE] HC (NOLOCK) ON RT.HULK_ID = HC.HULK_ID
            INNER JOIN #MyTmpData  ON #MyTmpData.BATMAN_ID=RT.BATMAN_ID
                  WHERE  EXISTS(select 1 from #MyTmpData where RT.BATMAN_ID=#MyTmpData.BATMAN_ID AND RT.IRONMAN_ID = #MyTmpData.IRONMAN_ID)                  AND RT.AVENGERS_ID IS NULL                  GROUP BY #MyTmpData.BATMAN_ID,Year(CREATED_DATE), Month(CREATED_DATE),Day(CREATED_DATE),HC.IS_ACTIVE
                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                
                
                        IF(@@TRANCOUNT > 0)          COMMIT TRAN  

          
          

      END

Kullandığımız döngülere göre sorgu süresinin çok çok azaldığını göreceğiz fakat bu sql sorusunda BEGIN TRAN ve COMMIT TRAN komutlarının arasında oldukça fazla sorgu olduğunu düşünelim. Bulk insertler işi bitene kadar tabloyu full kilitleyeceği için araya başka SQL komutlarının girmesine izin vermeyecekler. Ör: Yukarıdaki sorgumuzun bir rapor sorgusu olduğunu düşünürsek rapor çalıştığı esnada bu sorgudaki tüm tablolar kilitlenecek araya başka transaction almayacaktır. Öyleyse BEGIN TRAN ve COMMIT TRAN komutlarının arasını kısa tutmak gerektiği sonucu ortaya çıkıyor. 

Araya işlem alabilen uzun SQL lock 'a sebep oılmayan sorgu aşağıdaki gibi olmalı.

CREATE PROCEDURE [HEROES].[HERO_CREATE]
ASBEGIN      DECLARE @BATMAN_ID INT      DECLARE @IRONMAN_ID INT      DECLARE @HULK_ID SMALLINT

BEGIN TRAN    
      CREATE TABLE #MyTmpData
      (      ID INT IDENTITY(1, 1) primary key ,      BATMAN_ID INT,      IRONMAN_ID INT,      HULK_ID INT,
      )
                  IF(@@ERROR > 0)            BEGIN                  IF(@@TRANCOUNT > 0)                  BEGIN                        ROLLBACK TRAN                        RETURN                  END            END
insert into #MyTmpData (BATMAN_ID,IRONMAN_ID, HULK_ID)Select S.BATMAN_ID, S.IRONMAN_ID, S.HULK_ID
from HEROES.SUPERHEROES S (NOLOCK)WHERE DETAIL_CREATED = 'N'

            IF(@@ERROR > 0)            BEGIN                  IF(@@TRANCOUNT > 0)                  BEGIN                        ROLLBACK TRAN                        RETURN                  END            END          
                              IF(@@TRANCOUNT > 0)                  COMMIT TRAN    



      BEGIN TRAN                  ---------------UPDATE PROCESSES -------------------
                  UPDATE OH
                  SET OH.BATMAN_ID = #MyTmpData.BATMAN_ID
                  FROM HEROES.OLD_HEROES AS OH
                  INNER JOIN #MyTmpData  ON #MyTmpData.BATMAN_ID=OH.BATMAN_ID
                  WHERE                  OH.IS_CURRENTLY_WORKING = 'Y' AND                  OH.HERO_STATUS IN (1, 5) AND                  OH.HULK_ID = #MyTmpData.HULK_ID AND                  OH.IRONMAN_ID = #MyTmpData.IRONMAN_ID AND                                    
                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                
                  IF(@@TRANCOUNT > 0)            COMMIT TRAN          
          
                    BEGIN TRAN
                  ---------------INSERT PROCESSES -------------------                  INSERT INTO [HEROES].[HEROES_DETAIL]
                  ([BATMAN_ID], [NOMINATE_DATE]
                  SELECT                              #MyTmpData.BATMAN_ID,                              convert(varchar,CREATED_DATE, 104),                  

                  FROM HEROES.REWARDED_TEAM RT(NOLOCK)                  INNER JOIN COMMON.[HERO_CORE] HC (NOLOCK) ON RT.HULK_ID = HC.HULK_ID
            INNER JOIN #MyTmpData  ON #MyTmpData.BATMAN_ID=RT.BATMAN_ID
                  WHERE  EXISTS(select 1 from #MyTmpData where RT.BATMAN_ID=#MyTmpData.BATMAN_ID AND RT.IRONMAN_ID = #MyTmpData.IRONMAN_ID)                  AND RT.AVENGERS_ID IS NULL                  GROUP BY #MyTmpData.BATMAN_ID,Year(CREATED_DATE), Month(CREATED_DATE),Day(CREATED_DATE),HC.IS_ACTIVE
                  IF(@@ERROR > 0)                  BEGIN                        IF(@@TRANCOUNT > 0)                        BEGIN                              ROLLBACK TRAN                              RETURN                        END                  END                
                
                        IF(@@TRANCOUNT > 0)          COMMIT TRAN  

          
          

      END

 Görüldüğü gibi bu sorguda BEGIN TRAN ve COMMIT TRAN komutları aralara serpiştirilmiş boylece uzun kilitlere sebep olmuyor fakat Unit of Work olarak düşündüğümüzde birbiri ile ilişkili işlemlerimiz de olabilir örneğin update sonrası insert olmalı update 'de hata olursa insert olmasın gibi istekler olabilir işte bu durumda ya Unit Of Work 'e öncelik vermeliyiz ya da Araya transaction alıp almayacağımıza öncelik vermeliyiz. İsteklere göre değişen bir yapı.

Yaptığım işlemlerde en performanslı çalışan sorgunun Bulk insert olduğunu gördüm.While ve Cursor uzun işlemlerde 50dk ~40 dk sürerken Bulk insert'in 2 dk sürdüğünü gözlemledim. Cursor ve While karşılaştırmasında ise kazanan While oldu. Elbette performans yaptığımız işlere ve isteklere göre değişebilir.

Hoşça kalın ,sağlıkla kalın.