我的编程空间,编程开发者的网络收藏夹
学习永远不晚

SQL SERVER中SQL优化

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

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

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

SQL SERVER中SQL优化

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

SQL Server优化:SQL Server中Nolock关键字的用法

SQL Server没创建一个查询,都相当于创建一个查询会话,在不同的查询分析器里面进行的查询操作,可能会影响别的查询会话。
SQLServer优化2024-12-02

SQL Server性能怎么优化

本篇内容主要讲解“SQL Server性能怎么优化”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“SQL Server性能怎么优化”吧!数据库性能诊断和优化是提高数据库性能和稳定性的关键技术之一。
2023-06-27

SQL Server性能优化工具Profiler

SQL Server Profiler是一个界面,用于创建和管理跟踪并分析和重播跟踪结果。 这些事件保存在一个跟踪文件中,稍后试图诊断问题时,可以对该文件进行分析或用它来重播一系列特定的步骤。

在SQL Server中如何优化存储过程

要优化存储过程的性能,可以考虑以下几个方面:使用正确的索引:确保存储过程中涉及的表有合适的索引,以提高查询效率。可以使用SQL Server Management Studio的查询执行计划工具来分析查询执行计划,查看是否有适当的索引。避免
在SQL Server中如何优化存储过程
2024-06-03

SQL Server索引优化常用查询

1.1、查找缺失索引SELECT A.USER_SEEKS 查找次数,A.USER_SCANS 扫描次数, ROUND(A.AVG_TOTAL_USER_COST,2) 减少的用户查询的平均成本,A.AVG_USER_IMPACT 可能获得的平均百分比收
SQL Server索引优化常用查询
2014-10-16

SQL Server高级进阶之索引优化

1.1、查找缺失索引SELECT A.USER_SEEKS 查找次数,A.USER_SCANS 扫描次数, ROUND(A.AVG_TOTAL_USER_COST,2) 减少的用户查询的平均成本,A.AVG_USER_IMPACT 可能获得的平均百分比收
SQL Server高级进阶之索引优化
2016-07-26

SQL优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。          2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:     selec
2018-09-04

编程热搜

目录