SQL SERVER中SQL優化

Sqlserver中嘗試了一個開發的寫法

實現功能是掃描當前每條記錄時，把下一條記錄合并到當前行。

用自關聯CURR.RN = NEXT.RN +1(能找下一條)的方式查找時30萬的數據就很慢，甚至30分鐘后就內存溢出，最后調試后發現導致慢的根本原因是用表變量存儲了中間結果，然后從表變量里查詢數據時就非常慢。數據量如果很大都緩存到內存里，可能已經占用很多內存，后面再查詢時表自關聯時也要用到很多內存，所以就慢且最后內存溢出了。

解決方法就是把表變量換成臨時表，這樣查詢時有足夠內存可以使用，速度從30分鐘到10秒鐘。

DECLARE @DI_V2_BFGATE TABLE (                     

                      RN              NUMERIC(19,0)

                    , WORKDT           VARCHAR(20)

                    , IDNO             VARCHAR(20)

                    , INOUTTIME          VARCHAR(20)

                    , INOUTGBNCD            VARCHAR(20)

                    , IF_SQ              BIGINT

                    --, WKT_TOT_TM       NUMERIC(19,0)

                    );

      --  INSERT INTO @DI_V2_BFGATE

        SELECT

               ROW_NUMBER()OVER(PARTITION BY T.WORKDT,IDNO ORDER BY INOUTTIME) RN

               --ROW_NUMBER()OVER(ORDER BY IDNO,INOUTTIME) RN OLD WAY

              ,T.WORKDT

              --,T2.OVTM_DT

              --,T3.TMOFF_DT

              --,T3.TMOFF_NM

              --,T3.TMOFF_TYPE

              ,IDNO

              ,INOUTTIME

              ,INOUTGBNCD

              ,T.IF_SQ INTO #DI_V2_BFGATE

         FROM T_DI_V2_BFGATE T

         LEFT JOIN T_SI_GHR_OVTM  T2 ON (

                                         T.IDNO = T2.EMP_ID

                                         AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T2.OVTM_DT,120),'-','')

                                         AND  T2.TIME_WEEK_CD = '1'

                                         AND  T2.SHIFT_TYPE = 'OFMW'

                                        )

         LEFT JOIN T_SI_GHR_TIME_OFF T3 ON (

                                         T.IDNO = T3.EMP_ID

                                         AND T.WORKDT =  REPLACE(CONVERT(VARCHAR(10),T3.TMOFF_DT,120),'-','')

                                         AND  T3.TMOFF_TYPE IN('YC')

                                         )

         WHERE 1=1

      -- AND IDNO = '12587526' --test case

         AND   WORKDT >= '20170101' AND WORKDT < '20170201'

         AND  IsNumeric(IDNO) = 1

         --(1) 插入上午集中工作時間違反記錄

        DECLARE @WKT_TEMP TABLE (

                      WORKDT           VARCHAR(20)

                    , IDNO             VARCHAR(20)

                    , OUT_DT           DATETIME

                    , IN_DT            DATETIME

                    );

        INSERT INTO @WKT_TEMP

        SELECT WORKDT

              ,IDNO

              ,OUT_DT

              ,IN_DT

        FROM(

            SELECT  T1.WORKDT

                   ,T1.IDNO

                   ,CAST(SUBSTRING(T1.INOUTTIME,0,9) AS DATE) WKT_DATE

                   ,CONVERT(DATETIME,SUBSTRING(LEFT(T1.INOUTTIME,8)+' ' + SUBSTRING(T1.INOUTTIME,9,2)+':' + SUBSTRING(T1.INOUTTIME,11,2)+':' + SUBSTRING(T1.INOUTTIME,13,2),1,20))  OUT_DT

                   ,CONVERT(DATETIME,SUBSTRING(LEFT(T2.INOUTTIME,8)+' ' + SUBSTRING(T2.INOUTTIME,9,2)+':' + SUBSTRING(T2.INOUTTIME,11,2)+':' + SUBSTRING(T2.INOUTTIME,13,2),1,20))  IN_DT

                   ,T1.INOUTGBNCD

                   ,T2.INOUTGBNCD INOUTGBNCD1

            FROM #DI_V2_BFGATE T1

            LEFT JOIN #DI_V2_BFGATE T2 ON (T2.IDNO = T1.IDNO

                                       AND T2.WORKDT = T1.WORKDT

                                       AND T2.RN = T1.RN + 1

                                       AND IsNumeric(T2.IDNO) = 1

                                       AND T2.INOUTGBNCD != T1.INOUTGBNCD)

            WHERE T1.INOUTGBNCD = 'OUT'   

            AND   T1.WORKDT >= '20170101' AND T1.WORKDT < '20170201'               

            AND   RIGHT(T1.INOUTTIME,6) >= '090000' AND RIGHT(T1.INOUTTIME,6) <= '110000'

            --OR    RIGHT(T1.INOUTTIME,6) >= '140000' AND RIGHT(T1.INOUTTIME,6) <= '150000')

        )T

        SELECT * FROM @WKT_TEMP

        DROP TABLE #DI_V2_BFGATE

小結

選擇對應的方式：

　　1)使用表變量主要需要考慮的就是應用程序對內存的壓力，如果代碼的運行實例很多，就要特別注意內存變量對內存的消耗。我們對于較小的數據或者是通過計算出來的推薦使用表變量。如果數據的結果比較大，在代碼中用于臨時計算，在選取的時候沒有什么分組的聚合，就可以考慮使用表變量。

　　2)一般對于大的數據結果，或者因為統計出來的數據為了便于更好的優化，我們就推薦使用臨時表，同時還可以創建索引，由于臨時表是存放在Tempdb中，一般默認分配的空間很少，需要對tempdb進行調優，增大其存儲的空間。