星期五, 3月 13, 2020

[SQL] LIKE 沒有使用 %

發現一段 TSQL 語法,WHERE 條件使用 LIKE 要進行模糊搜尋,但是卻沒有加上 % 符號,猜測是手誤沒有加上去的,語法如下
WHERE 單號欄位1 LIKE 單號 OR 單位欄位2 LIKE 單號
手動測試從輸出結果感覺是 [LIKE 沒有 % 符號],似乎等於使用 [= 號],從 Seek Predicates 發現是 [單號欄位 >= 單號參數 AND 單號欄位 <= 單號參數]

[SQL] LIKE 沒有使用 %-4

確認條件後,那就直接用 [= 號] 就行,統計資訊和 CPU 時間差異不大,但執行計畫成本就有差異

[SQL] LIKE 沒有使用 %-3

星期六, 2月 15, 2020

[SQL] 效能觀察-依需求執行

觀察 Top 10 Logical Read 時發現,有段 TSQL 語法的執行次數過份極端,詳見下圖

[SQL] 有條件執行
確認後發現該語法是應用在廠內全部工作站,了解 TSQL 和實務應用商業邏輯後,發現
  • 該資料只是為 GridView 變色機制使用
  • TSQL 有關鍵條件寫死,其實只有三個工作站需要該資料
暫時解決方法:目前先加上工作站判斷,避免不需要資料的工作站也執行,減少被呼叫次數

因為工作站有 Timer 定時會重新抓取資料來呈現,頗有 DDoS 自己的味道,難怪執行次數爆表,哈

星期四, 1月 30, 2020

[SQL] Trigger 應用 - 跨 DB 條件約束

實務上有跨 DB 建立條件約束需求,因為 Foregin Key 限制 Table 彼此必須在相同 DB 內,轉向利用 Trigger 來達到該需求,剛好官方文件上也有類似範例

官方文件內容 - 建立外部索引鍵關聯性
FOREIGN KEY 條件約束只能參考在相同伺服器之相同資料庫內的資料表。 跨資料庫參考完整性必須利用觸發程序來實作
官方文件內容 - CREATE TRIGGER - C. 使用 DML AFTER 觸發程序在 PurchaseOrderHeader 與 Vendor 資料表之間執行商務規則
CREATE TRIGGER Purchasing.LowCredit 
ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  

    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    IF EXISTS 
        (
            SELECT *  
            FROM Purchasing.PurchaseOrderHeader AS p   
                JOIN inserted AS i ON p.PurchaseOrderID = i.PurchaseOrderID   
                JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p.VendorID  
            WHERE v.CreditRating = 5  
        )  
        BEGIN  
            RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);  
            ROLLBACK TRANSACTION;  
            RETURN   
        END;  
GO  
自行整理的 Trigger 語法
CREATE TRIGGER [dbo].[Trigger4FK]
    ON [dbo].[TableName]
    AFTER INSERT
AS 
    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    IF EXISTS
        (
            SELECT L.KeyColumn
            FROM inserted AS L
            WHERE L.KeyColumn IS NOT NULL
                AND NOT EXISTS
                    (
                        SELECT 1
                        FROM CrossDB.dbo.CrossDBTable AS C
                        WHERE L.KeyColumn = C.KeyColumn
                    )
        )
        BEGIN
            -- SQL Server 2012 之前使用 RAISERROR
            RAISERROR ('TableName.KeyColumn 違反 FK' , 16 , 1)
            ROLLBACK TRANSACTION;  
            RETURN

            -- SQL Server 2012 開始使用 THROW
            THROW 50000 , 'TableName.KeyColumn 違反 FK' , 1 ;
        END  
GO
故意引發的錯誤訊息,訊息如下
訊息 50000,層級 16,狀態 1,程序 Trigger4FK,行 27 [批次開始行 44]
TableName.KeyColumn 違反 FK
訊息 3609,層級 16,狀態 1,行 45
交易在觸發程序中結束。已中止批次。
閱讀 Trigger 文件時,注意到這段說明:最佳化 DML 觸發程序
觸發程序會在交易中運作 (隱含或其他方式),並在開啟時鎖定資源。 這項鎖定會持續,直到確認 (使用 COMMIT) 或拒絕 (使用 ROLLBACK) 交易為止。 觸發程序執行時間越長,封鎖其他處理序的機率越高。因此,撰寫觸發程序時,請盡可能降低其持續時間。 要確保較短持續時間的方法之一,是在 DML 陳述式變更零個資料列時釋放觸發程序。若要針對不會變更任何資料列的命令釋放觸發程序,請使用系統變數 ROWCOUNT_BIG。

下列 T-SQL 程式碼片段示範如何針對不會變更任何資料列的命令釋放觸發程序。 此程式碼應該出現在每個 DML 觸發程序的開頭:

IF (ROWCOUNT_BIG() = 0)
RETURN;
RAISERROR 和 THROW 相關重點
  • THROW 預設的嚴重性層級為 16
  • THROW 會回復整個批次,但是 RAISERROR 不會

星期日, 1月 19, 2020

[VFP] CursorAdapter 和參數相依

VFP CursorAdapter SelectCommand 內的 TSQL 語法,可以直接抓參數塞入,參數和 CursorAdapter 有了相依,萬一參數名稱要進行修正,還要進到 SelectCommand 內去修正,那不幸該 CursorAdapter 用在多個地方,就變成一件大工程,同事修改 Code 時就遇上該情況

[VFP] CursorAdapter 和變數相依-3
loDemoTable = Createobject("caCollection.caDemoTable")
liID = 2
lbDemoTable = loDemoTable.CursorFill(.T.)
討論時,直覺是在 CursorAdapter 建立 Property,外面針對 Property 來進行設定,CursorAdapter SelectCommand 就直接抓該 Property 就行

[VFP] CursorAdapter 和變數相依-2

[VFP] CursorAdapter 和變數相依-4
loDemoTable = Createobject("caCollection.caDemoTable")
loDemoTable.ID = 2
lbDemoTable = loDemoTable.CursorFill(.T.)
經過測試,確認 SelectCommand 是可以直接抓 Property 來當成參數,SQL Profile 內都是相同的 TSQL 語法

[VFP] CursorAdapter 和變數相依-1

VFP 寫久麻痺,沒有考慮到兩者相依性問題

星期五, 1月 10, 2020

電源插頭轉換器

研討會問券禮,實用

電源插頭轉換器