Optimization of SQL in SQL SERVER

Tried to write a developer in Sqlserver.

The implementation function is to merge the next record into the current row when scanning each record.

It is very slow to find 300000 of the data in the way of self-correlation CURR.RN = NEXT.RN + 1 (can find the next one), or even memory overflow after 30 minutes. Finally, after debugging, it is found that the root cause of the slow is that the intermediate results are stored in table variables, and then it is very slow to query data from table variables. If most of the data is cached in memory, it may already take up a lot of memory, and a lot of memory will be used for table self-association when querying later, so it will be slow and eventually overrun.

The solution is to replace the table variables with temporary tables so that there is enough memory to use when querying, with speeds ranging from 30 minutes to 10 seconds.

DECLARE @ DI_V2_BFGATE TABLE (

RN NUMERIC (19pr 0)

, WORKDT VARCHAR (20)

, IDNO VARCHAR (20)

, INOUTTIME VARCHAR (20)

, INOUTGBNCD VARCHAR (20)

, IF_SQ BIGINT

-, WKT_TOT_TM NUMERIC (1910)

);

-- 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.OVTMM DTP 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),'-','')

AND T3.TMOFF_TYPE IN ('YC')

)

WHERE 1 # 1

-- AND IDNO = '12587526'-- test case

AND WORKDT > = '20170101' AND WORKDT = '20170101' AND T1.WORKDT = '090000' AND RIGHT (T1.INOUTIME6) =' 140000' AND RIGHT (T1.INOUTIME6)