[SQL] 當天午夜時間

同事告知有段 Logical Read 很高的 T-SQL 語法，開啟 [包含實際執行計畫] 和 [SET STATISTICS] 來觀察 T-SQL 執行計畫、Logical Read 和 CPU Time

-- Ctrl + M 開啟執行計畫
SET STATISTICS IO ON
SET STATISTICS TIME ON

原語法示意

SELECT 
  欄位名稱
FROM 訂單檔頭 AS B
  JOIN 客戶 AS C
  JOIN 訂單檔身 AS I
  JOIN 派工工序 AS F 
WHERE B.客戶編號 <> '09084' 
  AND I.剩餘數量 > 0 
  AND I.完工日期 < cast(datediff(DD,0,getdate()) AS datetime) 
  AND I.派工單號 <> ''
  AND F.工序 = 'A1'
  AND F.完工數量 > 0

重點 Table Logical Read 和 CPU Time 統計資訊

資料表 "訂單檔身" 邏輯讀取 354137。

SQL Server 執行次數:
CPU 時間 = 639 ms，經過時間 = 734 ms。

問題點觀察分析

 

從 Logical Read 可以發現問題在 [訂單檔身] 資料表上，再觀察執行計畫，[訂單檔身] 資料表因為 I.完工日期 < cast(datediff(DD,0,getdate()) AS datetime) 條件而使用 [IX_訂單檔身_完工日期] 索引，利用 Index Seek 抓出幾乎全部的資料（抓取資料／ 全部資料：117826／118186），檢查索引破碎和統計資訊老舊是否為導致 Query Optimizer（簡稱 QO），誤用索引原因，發現兩者都在合理範圍。

 
I.完工日期 < cast(datediff(DD,0,getdate()) AS datetime) 條件，是要找出今天午夜時間前的資料，完全無法理解 cast(datediff(DD,0,getdate()) AS datetime) 語法邏輯是甚麼，但確定結果正確的，因此特地找執行計畫來了解 QO 到底是怎麼執行的。

 

從執行計畫就可以很清楚知道這段 CAST(DATEDIFF(DD,0,GETDATE()) AS datetime) 是這樣 CONVERT(datetime , DATEDIFF(DD,0,GETDATE()) , 0) 執行的。

尋找午夜時間的方法

-- 方法一：搭配 DATEADD、DATEDIFF 應用
SELECT
 DATEADD
    (dd,
        DATEDIFF(dd,'',GETDATE())
    ,'')
 
-- 方法二：利用轉換資料形態
SELECT
 CAST
    (
      CONVERT(char(10),GETDATE(),120) -- 把 GETDATE() 轉為字串，因限定字元因素，只會產生 yyyy-mm-dd
      AS Datetime
    ) -- 再把 yyyy-mm-dd 轉為 datetime 變成午夜時間

-- 方法三：這次發現的語法
SELECT 
  CAST
     (
       DATEDIFF(DD,0,GETDATE()) AS datetime
     )

原語法跑這三種午夜方法，可以觀察發現，

1. [方法一] 和 [方法三] 會導致 QO 誤用 [IX_訂單檔身_完成日期] 索引
2. [方法二]，QO 則是根據想法使用 [IX_訂單檔身_剩餘數量] 索引來處理

• 方法二

AND I.完工日期 < CAST(CONVERT(char(10),GETDATE(),112) AS datetime)

資料表 "訂單檔身"。掃描計數 1，邏輯讀取 2064。

SQL Server 執行次數:
CPU 時間 = 16 ms，經過時間 = 125 ms。

 

QO 使用 [IX_訂單檔身_剩餘數量] 索引，只抓取 [訂單檔身] 資料表 668 筆資料來處理

 

 

效能比較

訂單檔身 觀察	方法三	方法二
資料筆數	118,186	668
Logical Read	354,137	2,064
CPU Time	639 ms	16 ms

很意外午夜時間竟然會是導致 QO 誤判的問題點，QO 果然是被蠱惑的。

20220415 補述

該筆記是在 SQL Server 2005 上案例，發現午夜時間問題點在 SQL Server 2019 上已經不存在囉，而且重新閱讀該筆記也發現，改善後執行計畫是從 Constant Scan 開始，其實也是蠻奇怪的，該語法在當下應該也不太穩定才是

• 延伸閱讀
• [SQL] 日期 - 基本應用
• [SQL] Like - Constant Scan

[Challenge] 建立 SQL Server 版本的 Oracle TRANSLATE() 函數

Beyond Relational TSQL Challenge 23

In Oracle/PLSQL, the Translate function replaces a sequence of characters in a string with another set of characters. It will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

For example, translate('challenge', 'aen', 'xyz'); would return 'chxllyzgy'. That is 'a' is replaced by 'x', 'e' is replaced by 'y' and 'n' by 'z'

The challenge is to implement similarly functionality in TSQL, that works similar to TRANSLATE function of ORACLE.

• Sample Data

Data DataToReplace ReplacedWithData
---- ------------- ----------------
Cap  cp         xy

• Expected Results

OriginalData TranslatedData
------------ --------------
Cap          xay

• Rules
1. The program should run in SQL SERVER 2005 and above.
2. The output should be in the same way as it has been shown.

• TRANSLATE(string,from_str,to_str) 語法

from_str 參數和 to_str 參數應為一對一關係，假如不足，則以空白代替，但 to_str 不能為空白字元，Oracle 將空白字元解釋為 NULL，參數中有 NULL，結果即為 NULL

[C#] Timer - 利用 Label 顯示時間

利用 Label 控制項來顯示現在時間

• 設定 timer 每 1000 毫秒（1 秒）中必須觸發一次

private void LabelTime_Load(object sender, EventArgs e)
{
     timer.Enabled = true;
     timer.Interval = 1000;
     timer.Start();
}

• timer 被觸發時更改 lblTime.Text 來顯示現在時間。

private void timer_Tick(object sender, EventArgs e)
{
    lblTime.Text = DateTime.Now.ToString();
}

參考資料
• 論壇問題出處
• 逐步解說：使用 Visual C# 撰寫使用者控制項

[SQL] CTE - 累計薪資

在網路上看見這個問題，要求每個父節和其子節點的總和。

• 網友提供的圖示

直覺以前好像解過，但又有點陌生，把 Blog 文章找出來才發現之間的小差異，這篇 [Challenge] 計算各經理和其部屬薪資 需求是計算最上層父節點和其全部子節點薪資總和，但這個網路問題是要計算每個父節點和其子結點總和，不侷限在最上層的父節點。

[Challenge] 建立 SQL Server 版本的 Oracle WM_CONCAT() function

Beyond Relational TSQL Beginners Challenge 21

This challenge deals with String aggregation. On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value.

Oracle has an inbuilt function "WM_CONCAT" to aggregate data from a number of rows into a single row. Now, Let us implement this in TSQL.

WM_CONCAT aggregates data from a number of rows into a single row, giving a list of data associated with a specified value.

• Sample Data

Deptno Empname
------ -------
10    Niladri
20    Jeeva
30    Deepak
30    Prantik
20    Arina

• Expected Results

Deptno Empname
-----  --------------
10    Niladri
20    Arina,Jeeva
30    Deepak,Prantik

• Rules
1. Deptno should be sorted in Ascending Order.
2. Empname should be sorted in Ascending Order.
3. The program should run in SQL SERVER 2005 and above.
4. The output should be in the same way as it has been shown.

[VS] 程式碼行號

討論 code 的時候，行號的存在可以讓討論過程更加順暢、增加 code 的可讀性。

• VS => 工具 => 選項 => 文字編輯器 => 所有語言 => 行號

• 設定完成後，Code 的左側就會出現行號

• 20160627

安裝 2015 社群板時發現該設定已經是預設開啟功能

[SQL] 警示狀態

管理資料庫會建立警示，當 SQL Servr 發生問題時，能主動通知 DBA，來達到自動化管理，因此警示狀態就很重要啦。

• 利用 sysalerts 來查詢警示狀態

SELECT 
    [name] AS 警示名稱 ,
    message_id AS 錯誤號碼 ,
    severity AS 嚴重性 ,
    enabled AS 警示狀態 ,
    has_notification AS 發生警示收到通知的操作員數目 ,
    last_occurrence_date AS 上次發生日期 ,
    occurrence_count AS 這個警示的出現次數
FROM msdb.dbo.sysalerts

個人覺得重要的兩個資訊是 ［enabled 警示狀態］ 和 ［has_notification 發生警示收到通知的操作員數目］，可不要設定警示卻沒有啟動或是沒有設定通知的操作員喔。

延伸閱讀
• [SQL] 警示應用 - 監控使用者登錄失敗
• [SQL] 警示應用 - 錯誤嚴重性

參考資料
• dbo.sysalerts

[Challenge] 錯位字遊戲

Beyond Relational TSQL Beginners Challenge 20

Many of you must have played the 'mangled words' game in the school. Here is a challenge that gives you an opportunity to play it once again using TSQL.

Your task is to process the input table that contains several mangled words and try to 'un-mangle' them and validate them against a 'dictionary' table. You might be able to create more than one correct word from some of the input strings. It is also possible that a mangled word is completely incorrect and no valid word can be created from it.

As an example, given "xtet", the valid word that can be formed is "text".

• Sample Data

There are basically two tables.The first table (MangledWord) contains the randomize word list. Sample Input Data for MangledWord table.

ID MangledWord
-- -----------
1  Lnlaheecg
2  etxt

The second one (Dictionary) contains the valid words. Sample Input Data for Dictionary table.

ID Word
-- ---------
1  challenge
2  enigma
3  angle
4  change
5  changing

• Expected Results

ID MangledWord CorrectWords
-- ----------- ----------------------
1  lnlaheecg   angle,challenge,change
2  etxt        No matching word found

• Rules
1. ID should be sorted in Ascending Order.
2. The valid words(if found) will be sorted in Ascending order in the CorrectWords column.
3. If no words can be formed, then the CorrectWords column should contain No matching word found corresponding to the MangledWord.
4. The program should run in SQL SERVER 2005 and above.
5. The output should be in the same way as it has been shown.

[C#] 拖放應用 - TextBox

把記事本拖放至 TextBox 內並顯示資料。

using System.IO; // 必須引用此命名空間

namespace WindowsFormsApplication1
{
    public partial class DragDropText : Form
    {
        public DragDropText()
        {
            InitializeComponent();
        }

        private void DragDropText_Load(object sender, EventArgs e)
        {
            // 允許 textbox 可以拖放
            txtContent.AllowDrop = true;
        }

        private void txtContent_DragEnter(object sender, DragEventArgs e)
        {

            // 判斷
            // 1. 是否有拖曳物件
            // 2. 該物件是否無 txt 檔案
            // 3. 是不是 DataFormats.FileDrop
            bool fileformatCheck = e.Data.GetDataPresent(DataFormats.FileDrop);
            string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
            if
            (
            fileformatCheck == false ||
            files == null || files.Length == 0 ||
            Path.GetExtension(files[0]).ToUpperInvariant() != ".TXT"
            )
            {
                // 當 e.Effect = DragDropEffects.None 時，DragDrop 事件不會被觸發
                e.Effect = DragDropEffects.None;
            }
            else
            {
                // 當 e.Effect = DragDropEffects.Copy （非DragDropEffects.None）時，DrapDrop 事件會被觸發，且 TextBox 上的拖曳符號會變成 +
                e.Effect = DragDropEffects.Copy;
            }
        }

        private void txtContent_DragDrop(object sender, DragEventArgs e)
        {
   
            // 已經在 DrapEnter 內進行防呆，DrapDrop 內就直接抓取資料
            string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
            string filepath = files[0];
            FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read);
            // Encoding.Default 可以避免內容產生亂碼
            StreamReader sr = new StreamReader(fs, Encoding.Default);
            txtContent.Text = sr.ReadToEnd();
        }
    }
}

文字檔案（預設編碼為 ANSI）編碼格式與 Stream 必須一致才能正確顯示文字，因此產生 StreamReader 時，指定 Encoding.Default 來避免亂碼產生。

參考資料
• Visual C# 2010 精要剖析 charpter 12
• .NET 網路與 I/O 技術手冊 6.6.3 字元讀取的編碼設定
• 使用 Visual Studio 2012 開發 Windows Form 應用程式課程筆記
  
• Control.DragEnter 事件
• DragEventArgs 類別
• DragEventArgs.Data 屬性
• IDataObject.GetDataPresent 方法
• IDataObject.GetData 方法
• DataFormats.FileDrop 欄位
• 如何提供視覺化的 C# 應用程式中的檔案拖放功能

[SQL] 警示應用 - 錯誤嚴重性

SQL Server 定義 26 個嚴重性層級，從 0 - 25，

1. 17、18 為警告
2. 19 以上為非常嚴重
3. 20 以上會導致資料庫連接關閉，且所有 Transaction 皆會 Rollback
4. 17 以上的錯誤訊息會寫入 SQL Server 錯誤記錄檔和 Windows 應用程式事件記錄檔中

在 SQL Server 2000 警示內，預設會建立錯誤嚴重性 19 - 25 的未啟用警示，在 SQL Server 2005 開始則不會有，建議建立此警示

嚴重性	簡易說明
0 - 9	資訊類訊息，不算錯誤
10	資訊類訊息以傳回狀態，或回報不嚴重的錯誤
11 - 16	使用者造成的錯誤
17 - 19	使用者無法修正的軟體錯誤
20	目前的處理發生嚴重錯誤
21	資料庫處理序發生嚴重錯誤
22 - 23	嚴重錯誤：資料表完整性受到質疑
24	嚴重錯誤：硬體錯誤
25	嚴重錯誤

[Challenge] 統計回文質數

Beyond Relational Challenge 7

This challenge is to test the logical ability and does not have any resemblance with real time problem. The task is to generate the palindrome primes from 10 to 1000 and count the number of occurrences of each digit in them

A number that reads the same from left to right and vice versa is a palindrome number, for example 121 or 65456. Primes numbers are those that are divisible only by 1 and the number itself, for example 2 or 5.

• Sample Data

根據題目來產生所需要數字

• Expected Results

PalindromPrimes 0 1 2 3 4 5 6 7 8 9
--------------- -- -- -- -- -- -- -- -- -- --
11              - 2 - - - - - - - -
101             1 2 - - - - - - - -
131             - 2 - 1 - - - - - -
151             - 2 - - - 1 - - - -
181             - 2 - - - - - - 1 -
191             - 2 - - - - - - - 1
313             - 1 - 2 - - - - - -
353             - - - 2 - 1 - - - -
373             - - - 2 - - - 1 - -
383             - - - 2 - - - - 1 -
727             - - 1 - - - - 2 - -
757             - - - - - 1 - 2 - -
787             - - - - - - - 2 1 -
797             - - - - - - - 2 - 1
919             - 1 - - - - - - - 2
929             - - 1 - - - - - - 2

11 is a palindrome prime number and hence it is listed in the output. 11 has the number "1" twice and therefore the column "1" shows 2. Another palindrome prime, "101" has one zero and two ones. That is why the column "0" shows 1 and the column "1" shows 2.

• Rules
1. The code should run in SQL SERVER 2005 and later versions.
2. The code should generate the palindrome prime numbers. It should not read from a static table/cte/view.
3. Output should be sorted by the first column(Palindrome Primes) in ascending order.