转载

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