SQL SERVER中SQL优化-阿里云开发者社区

开发者社区> flzhang> 正文

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Feed系统架构与Feed缓存模型
Feed系统架构与Feed缓存模型
4 0
tddl连接池获取和释放流程疑问
最近遇到线上机器连接池满的情况,排查发现tddl里面在连接池获取和释放流程中有些问题
9 0
【Elastic Engineering】Elasticsearch:使用 Elasticsearch 在键入时实现类似 Linkedin 的搜索
Elasticsearch:使用 Elasticsearch 在键入时实现类似 Linkedin 的搜索
2 0
Aerospike在实时竞价广告中的应用
Aerospike在实时竞价广告中的应用
5 0
从传统银行到互联网,异地多活难不难?(3)
从传统银行到互联网,异地多活难不难?(3)
7 0
什么是软件实施?软件实施前景几何?软件实施的面试题有那些?
事情是这样的,由于自己目前还没有对象,就想着在兰州找一份还不错的工作,于是投了一家在我的家乡还算不错的公司,对方却说有可能是软件实施岗位,于是趁机了解了一下, 什么是软件实施? 软件实施掌握的基础知识有哪些? 软件实施前景几何?
6 0
宝塔面板如何为网站配置SSL证书?
原文链接:https://blog.csdn.net/JunyouYH/article/details/120734870(我的不属于转载) 代码复制不过来请转原文。。代码复制不过来请转原文。。代码复制不过来请转原文
7 0
使用APICloud AVM框架开发预约应用
前段时间跟朋友一起搞了一个预约的项目,前端用的APICloud的AVM框架做的,后端用的php开发的,用的tp5框架,没几天就搞出来了。简单跟大家分享一下开发中的一些功能点的实现吧。也欢迎大家一起探讨。
5 0
+关注
flzhang
某外企dba一枚,擅长java,shell自动化运维与SQL优化等,乐于钻研技术和分享技术
81
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载