Posts Tagged With: T-SQL

[T-SQL] 評估 PVIOT 加速資料組成效益


關於 PVIOT 官方說明請參考:

https://docs.microsoft.com/zh-tw/sql/t-sql/queries/from-using-pivot-and-unpivot

前篇 [T-SQL] WITH 通用資料表運算式:暫時資料表 有提到,要來介紹 PVIOT ,我在實際運用時,是測完 WITH 後,直接進入 PVIOT ,所以實際上 WITH 並沒用在系統中,只有測試過。

使用暫存資料庫、WITH 的範例語法請參考前篇,這篇就著重在 PVIOT ,範例也可以看看官方說明。

PVIOT 語法比 LEFT JOIN 簡潔

前篇是使用專案資料庫,資料筆數少,所以很難正確評估效益,這篇改用主資料庫,先用 COUNT 把資料庫筆數撈出來,有 158,673,734 ,也就是說有 1.58 億筆資料。

資料庫筆數

之後同時比較三種查詢方式的執行計畫:

將執行計劃時間整理如下表:

動作時間(秒)估計筆數
插入資料到暫存資料庫2.806202809
從暫存資料表查詢並組合資料0.0923038960
使用 WITH 查詢並組合資料3956.86894E+44
使用 PVIOT 查詢並組合資料2.4524782

我很驚訝 WITH 使用時間是 PVIOT 的 161 倍,我推論 WITH 並非原先想像先完成查詢結果集放在記憶體內的暫時資料表 (@MSSQL2017) ,應該是 T-SQL 字串的變數,因此每次 LEFT JOIN 都從 1.58 億筆內資料撈,導致 Cross 乘積 達到 6.86894E+44 那麼大。

也還好我實際系統沒用 WITH ,直接跳過去用 PVIOT ,少了踩雷,難怪如前篇般,我根本想不起 WITH 這個關鍵字。

去掉 WITH 查詢後,重跑執行計畫:

將執行計劃時間整理如下表:

動作時間(秒)估計筆數
插入資料到暫存資料庫2.956202809
從暫存資料表查詢並組合資料0.0873038960
使用 PVIOT 查詢並組合資料2.4144782

PVIOT 的確有增速效益,但並不如想像中明顯,大概為 1.26 倍。

這篇最大的心得是 WITH 是個雷… PVIOT 加速其實也有限。

Categories: 資料庫, SQL, 工作點滴, 技術分享 | 標籤: | 發表留言

[T-SQL] WITH 通用資料表運算式:暫時資料表


在微軟文件是寫暫存資料表,不過我覺得會跟 tempdb 裡面的暫存資料表混淆,所以我把暫存改為暫時。

WITH 我用到的機會不多,所以我以為我在 blog 上寫過跟 WITH 相關的主題,以便提醒自己。前天我要在 SSMS 下個 T-SQL ,要使用到 WITH ,但太久沒用了,完全想不起來關鍵字,google 暫存資料表不是舊的 tempdb ,不然就是 MSSQL 2016 以後支援的記憶體暫存資料表,根本找不到我想要的,翻了好久才翻到,所以這次要記下來,不然下次找不到。

在一個複雜或是資料龐大的查詢中,如果後續還要繼續對這個查詢做處理,最好把他寫到暫存資料表後再重複利用。

MSSQL 很早就有這功能,就是利用 tempdb ,使用 T-SQL 語法去處理,可以不用刪暫存資料表,SQL Server 會在斷線後自動刪除。

暫存資料表的命名就是以 # 為起頭。

詳見:https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

這裡面的缺點就是建立暫存資料表時,硬碟會有大量的 IO ,所以從 SQL Server 2016 起,支援在記憶體內建立暫存資料表,以節省硬碟 IO 並加速查詢。

另一個在 SQL Server 也支援很久的就是暫時資料表。

這篇的 SQL 語法可改用 PVIOT 加速,下次來說,這篇主要是介紹 WITH。

詳見:https://docs.microsoft.com/zh-tw/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

因為不用建立暫存資料表,所以上圖的 1/2/3/6 行都沒了,只剩下 4/5 行。上圖的第 4 行轉成 WITH 語法就是下圖的 1 ~ 3 行,上圖的第 5 行為了強調重複利用暫存資料表,把它展開成 4 ~ 18 行。WITH 建立的這篇稱為暫時資料表。

從這個案例來說明,原先的資料在 Data_Hour 超過 2 億筆 (row) ,如果每次 JOIN 都從原始資料撈,會浪費大量的 IO 與計算時間。所以透過暫存資料表或暫時資料表,可以加速查詢。

暫時資料表也可以多個,甚至類似巢狀,把上圖 1 ~ 3 行拆成兩個,如 1 ~ 7 行,8 行以後維持不變。

這邊是假設第二次查詢比較複雜,所以先建立 Data_Init 查詢結果後,再由 Data_CTE 經過複雜的 WHERE 運算得到新的暫時資料表,給下方的查詢使用。

我要找 WITH 是想到有個庫齡計算的 T-SQL 語法可以透過 WITH 加速,但太久沒用,一直想不起來關鍵字,自己記錄一篇提醒自己。

Categories: 工作點滴, 技術分享 | 標籤: | 1 則迴響

[T-SQL] 從資料記錄裡面抽出 IP


在自訂的事件紀錄裡面,由程式碼觸發的自動紀錄,原先只是當作備查用,就如同 Windows 內建的事件檢視器,資料庫欄位有 事件類別、事件代碼、事件時間、事件內容等,並沒有把 IP 當作獨立欄位拉出來,IP 會出現在事件內容的任何地方。

最近覺得系統效能不太正常,想確認是否有異常攻擊,打算依照 IP 來做排序計算,就變成一個困擾。

好在,不用管 IPv6 或是 domain name ,原先全部都有轉換成 IP 。

檢視資料紀錄後,發現 IP 大概有幾種紀錄格式存在事件內容中,有可能出現在一開始、中間或最末行:

IP=xx.xx.xx.xx
IP:xx.xx.xx.xx
IP: xx.xx.xx.xx
yy=xx.xx.xx.xx
yy:xx.xx.xx.xx

T-SQL 語法中並沒有正規運算式可用,所以組出來很麻煩,我組出來大概是這樣:

'%[:=]%[0-9]%.[0-9]%.[0-9]%.[0-9]%'
項目說明
%[:=]%先找出 yy: 或 yy= ,並包含可能有空白的項目
[0-9]%. IP 由數字開頭,1~3位數,後接小數點
[0-9]% 第四碼 IP 後方不知道還有多長
條件項目說明

測試的 T-SQL 語法為:

SELECT *, LEFT(cl, PATINDEX('%[^.0-9]%', cl) - 1) AS cIP	
FROM (
	SELECT *, (TRIM(SUBSTRING(cs, PATINDEX('%[:=]%[0-9]%.[0-9]%.[0-9]%.[0-9]%', cs) + 1, 17)) + ' ') AS cl
	FROM (
		SELECT ' IP: 172.16.1.1 User' AS cs
	) AS ts
) AS ts

因為條件式只能抓到滿足的 事件內容 ,我的重點是要抽出 IP ,所以用 SubString 抓出中間字串,利用 PatIndex 抓到符合的開始位置,因為不確定 IP 的具體長度,先抓 17 個長度下來。

接下來要把 IP 尾端無效內容去除,由於 IP 由數字 0 ~ 9 跟小數點組成,所以比較單純,直接把其他字元抓出來砍。由於 IP 可能會出現在最後,所以在前一段先加一個空白字元,確保這段可以找到非 IP 的字元進行運算。

會這麼繞,是真的不知道 T-SQL 可以怎樣抽 IP 出來,只能先抓開頭,再減尾巴,最後把 IP 剪出來,如果有更好的方法,也請不吝分享。

Categories: 工作點滴, 技術分享 | 標籤: | 發表留言

[VSCode] 輕巧的程式碼編輯器


我一直在找一個類似 VBA 環境的 javascript 開發環境,最好是輕巧一點,大部分的環境都搞得很複雜,我只想簡單測試一個 function ,結果得搞到變成一個方案,並透過編譯或轉譯再由其他環境執行。

我自己有在公司的軟體中掛入 VBScript 支援功能,也就是直接呼叫 Script Control ,當然同一個引擎也可以跑 javascript ,理論上,自己寫個 WinForm 包 Script Control 就結束的,但是在編輯環境上會花很多時間,所以最好能找現成的。簡易型的我做成網頁跑:
http://www.tlcheng.tk/TLCheng/Basic/vbs/IDE/Runner.htm
支援 vbscript/javascript ,要用 IE 相容模式跑。

最近逛到 Code Runner 可以直接跑~
Code Runner: https://marketplace.visualstudio.com/items?itemName=formulahendry.code-runner

看了一下,是 Visual Studio Code(VSCode) 外掛套件~
Visual Studio Code: https://code.visualstudio.com/

說實在的,已經有 Visual Studio 2015 Ent. 下,覺得不需要再裝 VSCode ,VSCode 支援 Windows/iOS/Linux ,本來以為是跨 OS 才會想要玩的,但是實在很想測 Code Runner ,又看到 VSCode 有免安裝版,50MB 下載解壓縮就能執行,心動之下就抓下來玩了:
https://code.visualstudio.com/docs/?dv=winzip

執行以後,安裝了兩個擴充套件,Code Runner 及 mssql 。

第一次開啟
第一次開啟

Code Runner 說經過設定,可以讓你在 VSCode 下直接跑以下語言:
C, C++, Java, JavaScript, PHP, Python, Perl, Ruby, Go, Lua, Groovy, PowerShell, BAT/CMD, BASH/SH, F# Script, C# Script, VBScript, TypeScript, CoffeeScript, Scala, Swift, Julia, Crystal, OCaml Script, R, AppleScript, Elixir, 及自訂命令

貼一段過去寫好的 javascript,依據 Code Runner 網頁的範例跑,可以直接測試 javascript:

執行 javascript
執行 javascript

再貼一段過去寫好的 vbscript,測試一下,也可以正常跑:

執行 vbscript
執行 vbscript

javascript 看起來應該是 VSCode 本身就支援,vbscript 則自動呼叫 cscript 編譯執行,結果畫面:

回傳 vbscript 執行結果
回傳 vbscript 執行結果

VSCode 的特色就是超快,自從 VS2013 起,外掛套件一多,開啟速度就慢到不行,安裝 VS2015 時,只裝不到 10 個套件,也是慢到不行,開機後第一次執行大概要 3 分鐘才能進到 VS2015 ,所以忽然發現,VSCode 可以當作平常簡易開發 html / javascript / asp.net 原始碼的環境,因為免安裝,還可以丟到 Embedded OS 內跑。玩玩 Code Runner 忽然發現,還可以連 SQL Server ,想到 SSMS 也越來越慢,簡直是測試 T-SQL 利器。 (需安裝 mssql)

在 VSCode 開啟 .sql 的檔案,會自動載入 mssql 套件環境:

自動載入 mssql 套件環境
自動載入 mssql 套件環境

滑鼠右鍵選執行查詢:

執行查詢
執行查詢

第一次使用須建立連線:

建立連線
建立連線

輸入 server 資訊:

server
server

輸入資料庫:

資料庫
資料庫

選擇驗證方式並輸入驗證資訊:

驗證方式
驗證方式

完成建立連線:

完成建立連線
完成建立連線

第二次使用 (指重開 VSCode) 直接選擇已建立的連線:

選擇已建立的連線
選擇已建立的連線

直接就可以顯示 查詢結果。

查詢結果
查詢結果

VSCode 免安裝真的很方便,速度又快,只是要看原始碼的話,或是簡單開發,完全可以參考使用阿~~~

Categories: 電腦和網際網路, 工作點滴, 技術分享 | 標籤: | 1 則迴響

在 WordPress.com 建立免費網站或網誌.

%d 位部落客按了讚: