Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Optimization of SQL in SQL SERVER

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report