但最近改版時產生異常，變成一部機器有多筆資料，導致執行計畫沒有從該 Table 起跑，兩個重點 Table 資料量大約是 150 萬筆和 50 萬筆，通通都被拉出來篩選，CPU Time 超過 1 秒
整理 Table 內資料，執行計畫就恢復正常啦
改善前後比較
星期一, 10月 30, 2023
[SQL] 限定資料資料表
有一個 Table 記錄著部門內每台機器最後一筆完工資料，該部門 Table 最多也只有四部機器，只有四筆資料的意思，有一個 Query 依賴該 Table 來限定資料，執行計畫只要從該 Table 開始跑就沒有問題啦
星期六, 10月 21, 2023
[SQL] 遺漏索引 - equality、inequality
延續 [SQL] 遺漏索引建議 該篇筆記，xml missing index 內還有其他發現，是關於 equality 和 inequality 條件判斷，在 [SQL] 遺失索引案例 內有紀錄過 <>、> 或 NOT IN 是屬於 inequality，網路上也可以找到詳盡列出屬於 equality、inequality 的操作
但最後我是以 sys.dm_db_missing_index_details 內的 inequality_columns 文字說明為判斷依據
Any comparison operator other than "=" expresses inequality.
白話翻譯就是任何非等於的操作就是 inequality，但是在該 missing index 內，IN 有出現在不同的建議內，且一個是歸類在 equality，一個是歸類在 inequality 內
WHERE ColumnId5 IN ('資料1', '資料2', '資料3', '資料4')星期五, 10月 20, 2023
[SQL] 遺漏索引建議
Turning 平行處理時發現前兩個 TSQL 執行次數特別高，特別抓出來處理
打開 xml missing index 發現特別之處，TSQL 語法內的欄位資訊以 missing index 內的 ColumnID 來示意
SELECT
M.ColumnId5
,M.ColumnId11
,M.ColumnId12
,M.ColumnId27
FROM TableName AS M
JOIN
(
SELECT
ColumnId5 ,
MAX(ColumnId29) AS ColumnId29
FROM TableName
WHERE ColumnId5 IN ('資料1', '資料2', '資料3', '資料4')
AND ColumnId23 > 0
GROUP BY ColumnId5
) AS T ON M.ColumnId5 = T.ColumnId5
AND M.ColumnId29 = T.ColumnId29
WHERE M.ColumnId23 > 0重點欄位在於 where 條件
WHERE ColumnId5 IN ('資料1', '資料2', '資料3', '資料4')
AND ColumnId23 > 0missing index 建議
在舊文章 - Using Missing Index Information to Write CREATE INDEX Statements 內有條列這四點說明
- List the equality columns first (leftmost in the column list).
- List the inequality columns after the equality columns (to the right of equality columns listed).
- List the include columns in the INCLUDE clause of the CREATE INDEX statement.
- To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.
在新文章 - Tune nonclustered indexes with missing index suggestions 內則是改為文字說明
Review the missing index recommendations for a table as a group, along with the definitions of existing indexes on the table. Remember that when defining indexes, generally equality columns should be put before the inequality columns, and together they should form the key of the index. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list). Unique columns are most selective, while columns with many repeating values are less selective.Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. The order of included columns doesn't affect query performance. Therefore, when combining indexes, included columns may be combined without worrying about order
打開 XML 執行計畫找到最佳建議，基本上有依循上述原則建議，先是建議 equality 欄位後，才是 inequality 欄位
但該 case 的 ColumnId5 在商業邏輯上類似分類資料，ColumnId23 類似未結案資料，ColumnId23 欄位是比較 selective，確認另外兩個建議都是以 ColumnId5 為主，建議完全沒有幫助，最後單純建立 ColumnId23 Index 來消除平行處理
CREATE INDEX IX_TableName ON TableName( ColumnId5 )這兩個 case 重點 table 剛好都是同一個，建立一支 index 解決兩個 case 的平行處理，該 case 效能前後比較
星期四, 10月 19, 2023
[SQL] 隱含轉換 - CASE WHEN
Turning 時遇見新的隱含轉換案例，該 case 是兩個 table 透過 left join 後，要在 select 內去進行統計轉換，但該欄位原本是字串欄位，卻轉成整數 (理論上是手誤)，導致隱含轉換發生 
模擬案例
USE tempdb
GO
DROP TABLE IF EXISTS tblMaster
DROP TABLE IF EXISTS tblDetail
CREATE TABLE tblMaster (ColPK CHAR(11))
CREATE TABLE tblDetail (ID INT IDENTITY(1,1) , ColPK char(11))
SELECT
CASE
WHEN D.ColPK IS NOT NULL THEN 1 -- 字串欄位轉為整數
ELSE D.ColPK
END AS Test1 ,
IIF(D.ColPK IS NOT NULL , 1 , D.ColPK) AS Test2
FROM tblMaster AS M
LEFT JOIN tblDetail AS D ON M.ColPK = D.ColPK
星期五, 10月 13, 2023
[DP] 責任鍊
以資料驗證為例子，使用責任鍊 (Chain of Responsibility，簡稱 cor) 來消除 IfElse 的驗證流程
Legacy Code：IfElse
概念示意圖 (第一次畫)
using System;
namespace ConsoleApp2
{
internal class Program
{
static void Main(string[] args)
{
string data = "AB-20231013";
bool isValid = false;
if (data.Length == 11)
{
if (data.Substring(0, 2) == "AB")
{
if (data.Substring(2, 1) == "-") ;
{
string date = data.Substring(3, 8);
isValid = DateTime.TryParseExact(
date,
"yyyyMMdd",
System.Globalization.CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None,
out DateTime result);
}
}
}
Console.WriteLine($"驗證結果為 {isValid}");
}
}
}using System;
namespace ConsoleApp2
{
internal class Program
{
static void Main(string[] args)
{
string data = "AB-20231013";
AbstractHandler chain = new 總長度Handler(
new 前兩碼Hanlde(
new 符號Handler(
new 日期Handle(null))));
bool isValid = chain.HandleRequest(data);
Console.WriteLine($"驗證結果為 {isValid}");
}
}
public class 日期Handle : AbstractHandler
{
public 日期Handle(AbstractHandler successor) : base(successor)
{
}
protected override bool Handle(string request)
{
string date = request.Substring(3, 8);
return DateTime.TryParseExact(
date,
"yyyyMMdd",
System.Globalization.CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None,
out DateTime result);
}
}
public class 符號Handler : AbstractHandler
{
public 符號Handler(AbstractHandler successor) : base(successor)
{
}
protected override bool Handle(string request)
{
return (request.Substring(2, 1) == "-");
}
}
public class 前兩碼Hanlde : AbstractHandler
{
public 前兩碼Hanlde(AbstractHandler successor) : base(successor)
{
}
protected override bool Handle(string request)
{
return (request.Substring(0, 2) == "AB");
}
}
public class 總長度Handler : AbstractHandler
{
public 總長度Handler(AbstractHandler successor) : base(successor)
{
}
protected override bool Handle(string request)
{
return (request.Trim().Length == 11);
}
}
public abstract class AbstractHandler
{
private AbstractHandler _successor;
public AbstractHandler(AbstractHandler successor)
{
_successor = successor;
}
protected abstract bool Handle(string request);
public bool HandleRequest(string request)
{
if (Handle(request) == false)
return false;
if (_successor == null)
return true;
return _successor.HandleRequest(request);
}
}
}星期二, 10月 10, 2023
[SSMS] 指令碼精靈
[SSMS] 產生 Script 以 Alter 為目標，紀錄單一物件和多物件產生 Script 方式，該篇會以指令碼精靈為主，指令碼精靈除了產生物件 Script 之外，還可以把資料一併轉出
這邊可以選擇 [編寫整個資料庫和所有資料庫物件的指令碼] 或 [選擇特定的資料庫物件]，該筆記就單選轉出 Person.Person Table 而已
該筆記以 [結構描述和資料] 為主，要同時匯出 Table Scheam 和資料
DB => 工作 => 產生指令碼
把物件匯出成 Script 並點選右上角的 [進階] 選項
進階選項內的 [要編寫指令碼的資料類型] 有三種選項，分別為
- 結構描述和資料
- 僅限結構描述
- 僅限資料
轉出的 Script 語法，資料部分只記錄 5 筆資料
USE [AdventureWorks2022]
GO
/****** Object: Table [Person].[Person] Script Date: 2023/10/10 下午 10:32:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Person](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[NameStyle] [dbo].[NameStyle] NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL,
[Suffix] [nvarchar](10) NULL,
[EmailPromotion] [int] NOT NULL,
[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (1, N'EM', 0, NULL, N'Ken', N'J', N'Sánchez', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'92c4279f-1207-48a3-8448-4636514eb7e2', CAST(N'2009-01-07T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (2, N'EM', 0, NULL, N'Terri', N'Lee', N'Duffy', NULL, 1, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'd8763459-8aa8-47cc-aff7-c9079af79033', CAST(N'2008-01-24T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (3, N'EM', 0, NULL, N'Roberto', NULL, N'Tamburello', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'e1a2555e-0828-434b-a33b-6f38136a37de', CAST(N'2007-11-04T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (4, N'EM', 0, NULL, N'Rob', NULL, N'Walters', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'f2d7ce06-38b3-4357-805b-f4b6b71c01ff', CAST(N'2007-11-28T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (5, N'EM', 0, N'Ms.', N'Gail', N'A', N'Erickson', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'f3a3f6b4-ae3b-430c-a754-9f2231ba6fef', CAST(N'2007-12-30T00:00:00.000' AS DateTime))