SQL SERVER中SQL优化

简介: Sqlserver中尝试了一个开发的写法 实现功能是扫描当前每条记录时,把下一条记录合并到当前行。 用自关联CURR.RN = NEXT.RN +1(能找下一条)的方式查找时30万的数据就很慢,甚至30分钟后就内存溢出,最后调试后发现导致慢的根本原因是用表变量存储了中间结果,然后从表变量里查询数据时就非常慢。

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进行调优,增大其存储的空间。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
19小时前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
2天前
|
SQL 数据库 索引
sql深度优化
sql深度优化
10 1
|
4天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
32 0
|
16天前
|
SQL 存储 关系型数据库
【MySQL】七种SQL优化方式 你知道几条
【MySQL】七种SQL优化方式 你知道几条
26 0
|
20天前
|
SQL 存储 关系型数据库
MySQL(终结篇二)- SQL 语句分析与优化
MySQL(终结篇二)- SQL 语句分析与优化
74 0
|
20天前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
73 0
|
20天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
21 0
|
20天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
44 0
|
20天前
|
SQL 数据库 数据库管理
【数据库SQL server】关系数据库标准语言SQL的基本知识
【数据库SQL server】关系数据库标准语言SQL的基本知识
33 0
|
20天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
34 0