Güncel
Loading...

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]
AS
BEGIN


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]
AS
BEGIN
      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]
AS
BEGIN
      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]
AS
BEGIN
      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.




Share on Google Plus

About Sema KUDU

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

0 yorum :

Yorum Gönder

Türkiye yenilmez,Millet Eğilmez!