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進行調優，增大其儲存的空間。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2148402/，如需轉載，請註明出處，否則將追究法律責任。

SQL SERVER中SQL優化

相關文章