SQLSERVER中SQL优化

Sqlserver中尝试了一个开发的写法

三水ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18982081108（备注：SSL证书合作）期待与您的合作！

实现功能是扫描当前每条记录时，把下一条记录合并到当前行。

用自关联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进行调优，增大其存储的空间。