最近剛好發生跟 MS SQL 有關的狀況，所以就記錄一下

1. where 字串欄位要不要加 N''  (unicode)
    範例: Select * from OrderDetail where OrderDetailId= N'20220923084533'
    基本上，欄位是 char、varchar 就不需要。nvarchar nchar 就要加 N
    這好像是廢話，但往往寫程式會沒注意，甚至是會覺得通通加 N 比較沒問題
    但如果欄位牽涉到 index 就會影響搜尋 ，所以寫程式當下要注意

-- 假設 OrderDetailId 是 varchar(20)

-- OrderDetail 有千萬筆資料



Select * from OrderDetail where OrderDetailId= N'20220923084533'

-- 結果跑超級久 (未使用 PK 找，掃整個 table)

Select * from OrderDetail where OrderDetailId= '20220923084533'

-- 結果一下就出來 (使用 PK 找)

-- **資料庫設計最好以數值來當 key 比較好

    
2.  當欄位有 null 值，where [clumnName] not in ('A')  也會排除 null 的狀況
     直覺上，只要下 not in (..)  那會其他項目都會抓出，但是實際上，該欄位是 null的資料也會被排除
     因為資料庫一旦你對某欄位下條件，他就會排除 null 的欄位，除非你指定  [clumnName] is null 才會有資料
 

今天發生一個SQL奇怪的現象，記錄一下
就是訂單列表(使用分頁的SQL 找出 top 20)跑很久才出來，大約10多秒 
SQL指令條件僅有訂單日期(預設是1年內訂單，訂單總數有130萬筆)，而訂單日期也有做index
之前都大約1~2秒就出來，這一兩天卻要10秒才跑出來

用執行計畫評估功能查也看不出哪裡有問題，把一些子查詢拿掉也沒有改善
所以就改一下搜尋條件，改搜尋半年內訂單，大約 5秒
改搜尋兩年內訂單，1秒!!! 真是神奇，把條件區間拉大反而很快???????
直覺上覺得索引table是不是太大太亂了，剛好看到CreateDate索引有個重建按鈕
就勇敢把他按下去，想說會不會跑很久，結果重建只要不到1秒就好了
然後神奇的事發生了，訂單列表不須一秒就顯示出來啦!

SQL 指令大概長這樣

Select * from (

Select Top 20 * from (

select Top 20 a.*

--, CompelteOrderId= (select top 1 K.Id from packing_list_main k With(NoLock) join Order_Main p With(NoLock) on k.Order_Id=p.Id where k.En_Packing_List_Status=300 and P.MEmber_Id=a.Member_Id )

--, [MemoCount_前台問題] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 1)

--, [MemoCount_業務備註] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 3)

--, [MemoCount_系統檢查] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 4)

--, QnACount = (select count(*) from Order_QnA With(NoLock) where Order_Id = a.Id)

from V_Order_Main_For_Admin_List a With(NoLock) left join Member b with(NoLock) on a.Member_Id=b.Id where (1=1) and En_Order_Status <> -300 and a.Create_Date >='2018-06-20' order by Id Desc

) AS T1 order by Id

) AS T2 order by Id Desc

參考 will will web 的舊文章，原來定期把索引整理整理，也是要記得做的。
不過週期上我想 1季或半年跑一次應該就可以了
-------------------------------------------------------------------
後記: 2020/6/22 -  一年後又發生一樣的狀況，真是神奇
這次 OrderMain CreateDate 沒有很分散。只有 2.X %
想說其他 index 是不是該重建，但是都沒有效果
最後只好重建 CreateDate --> 結果居然解決了

看 2021年會在發生嗎?

 

當我們在本地的SQL以 Linked Server方式存取遠端的資料庫，有時會出現這樣的訊息
不允許遠端資料表值函數呼叫
通常是使用 stored procedure 或是 自定義的function 會出現這樣的錯誤
這時候可以有個變通方法，就是使用 OPENQUERY 方法

原寫法 (UNTDB 是 Linked Server的 Name)

mySQL = "Select count(*) as Cnt from [UNTDB].[ShopUNT]..[V_Order_Main] ....



新寫法

mySQL = "Select count(*) as Cnt from [ShopUNT]..[V_Order_Main] ....

mySQL = "Select * from Openquery([UNTDB],'" + mySQL.Replace("'", "''") + "')";

 

MS SQL 最小的 DateTime 是 1753-01-01
SqlDateTime.MinValue = 1753-01-01 00:00:00
DateTime.MinValue = 0001-01-01 00:00:00

如果要將時間塞入 DB 千萬不要小於 1753-01-01
-------------------------------------------------------
UW.DB 物件會將 null datetime 轉成 DateTime.MinValue
但再塞入 DB 時應該要將 DateTime.MinValue 換成 null 或是 SqlDateTime.MinValue
才不會出錯
 

最近把 shopunt 網站重啟的Log加上，發現網站有兩次時無預警的重啟
但是沒有exception log, 於是去看系統的 Event log, 發現出現以下的錯誤

應用程式: w3wp.exe

Framework 版本: v4.0.30319

描述: 處理序已終止，因為有未處理的例外狀況。

例外狀況資訊: System.Exception

堆疊:

於 UW.SQL.DTFromSQL(System.String, System.String)

於 UW.SQL.DTFromSQL(System.String, System.Data.SqlClient.SqlConnection ByRef, Boolean)

於 SHOPUNT.DB.Product.RebuildNotStopProducDT()

於 System.Threading.ExecutionContext.runTryCode(System.Object)

於 System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode, CleanupCode, System.Object)

於 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)

於 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)

於 System.Threading.ThreadHelper.ThreadStart()



---------------------------------------------------------------------------------------------------------------------------------------



失敗的應用程式名稱: w3wp.exe，版本: 7.5.7601.17514，時間戳記: 0x4ce7afa2

失敗的模組名稱: KERNELBASE.dll，版本: 6.1.7601.18229，時間戳記: 0x51fb1677

例外狀況碼: 0xe0434352

錯誤位移: 0x000000000000940d

失敗的處理程序識別碼: 0x57c4

失敗的應用程式開始時間: 0x01d012faf33398f8

失敗的應用程式路徑: c:\windows\system32\inetsrv\w3wp.exe

失敗的模組路徑: C:\Windows\system32\KERNELBASE.dll

報告識別碼: 90ad2f4d-7f89-11e4-8ad2-e41f13b7d81e

原因是另開 Thread 以非同步取得非停售產品的資料時 產生 sql timeout，這時網站就整個 Crash 
約兩分鐘後才重新啟動，我想雙11網站一直Crash應該跟這個有關

改善方式:
1. RebuildNotStopProducDT 要 Try Catch 不要丟 exception 直接寄 mail 就好
2. 資料庫取得 "非停售產品" 有點沒有效率，不忙時至少要跑 4 秒，資料庫一忙就會Timeout，所以應該要把確定下架且不會販售的產品設定為停售 (Is_StopSell = 'Y')，這樣至少少了2000件產品的查詢，可以秒殺

另外 .NET 4.0 有新的物件叫做 Task 可以用來處理非同步程式，也可以處理 exception 狀況，有時間的話再來改看看
 

半夜被老闆 Line，跟我說網頁發生錯誤，他無法看統計報表，會出現作業逾時的錯誤 我查了一下網頁程式，發現是這筆SQL指令跑太久

 Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date

From V_Order_main With(Nolock)

Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14278))

AND Create_Date >= '2014/10/3' And Create_Date < '2014/10/12' 

--> 跑了50秒~ 1分鐘

 Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date

From V_Order_main With(Nolock)

Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14278))

--> 拿掉日期限制 > 秒殺，  結果跑出 47 筆資料

跑一下執行計畫 原來子查詢的 Ad_Trace 沒有建立Index造成


加上 Index 之後，再跑第一支SQL --> 秒殺

奇怪的地方:
明明訂單日期欄位(Create_Date) 跟 Ad_Trace 沒有多大關係，但是加上日期限制之後，居然會引發 Ad_Trace 搜尋過久的現象
他好像是先依照訂單日期找出所有訂單，然後再從子查詢 Ad_Trace 裡面搜尋有無符合的條件訂單
而不是先跑子查詢找出 Ad_Trace 所有的 Order_Id，再去找符合 Create_Date 的訂單

SQL內部運作的機制真是有點讓人想不透，幸好有評估計畫可以看出問題在哪裡
這讓我想起以前學SQL時的一句話 => 盡量用Join來取代子查詢
使用子查詢真的是效能殺手啊

------繼續補充--------------------------------------

上面的SQL在雙11活動後，由於當日訂單過多，一樣造成SQL timeout 的現象，研判還是子查詢造成的問題

Select Id, (IsNull(Total, 0) - IsNull(CouponDiscount, 0) + IsNull(CouponAdd, 0)) as Total, Buyer_Email, EN_Packing_List_Status, EN_Order_Source, En_Stock_Status, Create_Date

From V_Order_main With(Nolock)

Where Id in (select Order_Id from Ad_Trace With(NoLock) where (Parameter_Id = 14720))

And Create_Date > '2014-11-10' And Create_Date < '2014-11-11'

--> 跑了51秒

所以先將子查詢拿出來跑出一串字串 865828,865890,865901,865903,865928,865955,865990,865993,866005,866035.....
(共1452筆)
再把他拼入sql跑   --> 7秒
 

這之前就知道的東西，但今天發現一個新寫法與各位分享

最常用寫法

INSERT INTO table (name) VALUES('bob');

SELECT SCOPE_IDENTITY()

新發現的寫法

INSERT INTO table (name)

OUTPUT Inserted.ID

VALUES('bob');

在 valuse 前面加上 Inserted.Id
這個有個優點 如果是GUID產生的 Id 也可以取得