星期五, 5月 31, 2024

[SQL] 避免對欄位進行數學運算

在官方文章 Troubleshoot high-CPU-usage issues in SQL Server 的 Step 6: Investigate and resolve SARGability issues 段落是討論 SARG,記錄文章範例並延伸

測試環境建置

Sales.SaleOrderDetail.UnitPrice 預設並沒有 Index,透過下列語法先把 Index 建立出來並實際執行 TSQL 語法來取得執行計畫
USE AdventureWorks2022
GO

DROP INDEX IF EXISTS IX_SalesOrderDetail_UnitPrice ON [Sales].[SalesOrderDetail]

CREATE INDEX IX_SalesOrderDetail_UnitPrice ON [Sales].[SalesOrderDetail] (UnitPrice)

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

TSQL 修正 - 隱式轉換

從 Index Scan Operator 內發現有隱示轉換發生,原來 UnitPrice 資料型態是 money,0.1 數字會被視為 numeric,以資料型態轉換優先權來看,UnitPrice 會被轉換為 numeric 來進行運算,為避免模糊焦點,之後數字 0.1 會明確轉型為 money
-- 原語法
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

-- 修正後
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * CAST(0.10 as money) [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * CAST(0.10 as money) > 300

TSQL 修正 - 排序 (相異排序)

從排序 (相異排序) operator 中觀察,DISTINCT 是針對 SalesOrderID、UnitPrice、[10% Commission]  這三個欄位來排序並進行重覆篩選,但 [10% Commission]  是計算欄位,不用包含在 DISTINCT 才是
-- 原語法
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

-- 修正後
SELECT
	T.* ,
	UnitPrice * 0.10 [10% Commission]
FROM
	(
		SELECT DISTINCT SalesOrderID, UnitPrice
		FROM [Sales].[SalesOrderDetail]
		WHERE UnitPrice * 0.10 > 300
	) AS T

修改前後執行計畫比較

統計資訊上排序 (相異排序) 造成的 Worktable 就消失

數學運算

經過上述修正後的 TSQL 語法
SELECT
	T.* ,
	UnitPrice * CAST(0.10 as money) [10% Commission]
FROM
	(
		SELECT DISTINCT SalesOrderID, UnitPrice
		FROM [Sales].[SalesOrderDetail]
		WHERE UnitPrice * CAST(0.10 as money) > 300
	) AS T
實際執行可以得到下圖執行計畫,因為對 UnitPrice 進行數學運算,導致無法吃到 Index Seek 且從 Plan Explorer 內觀察的話,還有錯誤訊息
修正 TSQL 語法,不要針對欄位進行數學運算
SELECT
	T.* ,
	UnitPrice * CAST(0.10 as money) [10% Commission]
FROM
	(
		SELECT DISTINCT SalesOrderID, UnitPrice
		FROM [Sales].[SalesOrderDetail]
		WHERE UnitPrice > 3000 -- 不針對欄位進行數學運算
	) AS T

改善前後 TSQL 語法執行計畫比較

星期五, 5月 24, 2024

[SQL] 日期時間分隔符號

課程中老師提到一個日期欄位轉型案例,日期參數來源日期是以 / 符號來進行分隔,但因為 MS SQL datetime 是以 - 符號,所以 developer 對日期欄位進行轉換,目的只是為了日期分隔符號,示意如下
SELECT 
	ModifiedDate AS [以 - 分隔],
	CONVERT(varchar(10) , ModifiedDate , 111) AS [以 / 分隔]
FROM Person.Person
WHERE CONVERT(varchar(10) , ModifiedDate , 111) = '2024/03/27'
 Language 和 Dateformat

日期會受到  Language 和 Dateformat 影響,所以要先確定連線設定值
DBCC USEROPTIONS;


日期分隔範例

從 CAST 和 CONVERT 文章內找到不同分隔符號,就都拿來試看看,下述都是可以正常篩選出資料的分隔符號,沒想到逗點和空白也行
-- 無分隔
SELECT * FROM Person.Person WHERE ModifiedDate = '20240327'

-- - 分隔
SELECT * FROM Person.Person WHERE ModifiedDate = '2024-03-27'

-- / 分隔
SELECT * FROM Person.Person WHERE ModifiedDate = '2024/03/27'

-- . 分隔
SELECT * FROM Person.Person WHERE ModifiedDate = '2024.03.27'

-- 空白分隔
SELECT * FROM Person.Person WHERE ModifiedDate = '2024 03 27'

從執行計畫 operator 來觀察上述語法執行,其實內部都是用 - 分隔來執行

以前寫 VFP 時也做過類似日期轉換,因為擔心 VFP Date 資料型態不相容 MS SQL,所以當下都把 VFP 日期參數轉成 ISO 格式才傳進 MS SQL 內,後來也發現是多此一舉,聽到這 case 時格外親切,XD

星期二, 5月 21, 2024

[RV] ReportDataSource

製做報表時收到下圖錯誤訊息


查官方文件發現 ReportDataSource Constructor (String, Object) 第二參數雖然是 object,但備註有特別說明,必須是 DataTable、IEnumerable、BindingSource、System.Type 才行


把資料來源的 class 塞進 List 再丟進去 ReportDataSource 就行

星期五, 5月 17, 2024

[SQL] HierarchyID - 分支前兩筆資料

在 Line 社群看見的問題,要找到樹狀圖上該條件分支上的前兩筆資料並轉置呈顯,原問題是採取父子階層,也把資料轉為 HierarchyID 來練習看看,把問題簡化為下面表格
 


父子階層

直接參考其他筆記
USE AdventureWorks2022
GO

DROP TABLE IF EXISTS tblParentChild

CREATE TABLE tblParentChild
(
	[id]        INT PRIMARY KEY NOT NULL,
    [parent_id] INT,
    [name]      NVARCHAR(50)
)

INSERT INTO tblParentChild([id], [parent_id], [name]) VALUES
	(0  , NULL , 'Root'),
	(1  , 0    , 'A'),
	(2  , 1    , 'A1'),
	(4  , 0    , 'B'),
	(5  , 4    , 'B1'),
	(6  , 5    , 'B12'),
	(7  , 6    , 'B123'),
	(8  , 0    , 'C'),
	(9  , 8    , 'C1'),
	(10 , 9    , 'C12'),
	(11 , 10   , 'C123'),
	(12 , 11   , 'C1234'),
	(20 , 0    , 'D');

;
WITH CTE AS 
(
		SELECT 
			id AS GroupID ,
			id ,
			[name] ,
			1 AS [level] ,
			parent_id
		FROM tblParentChild
		WHERE [name] IN ('A1', 'B12', 'C1234', 'D')
		UNION ALL
		SELECT T.GroupID ,
		       PC.id ,
		       PC.[name] ,
		       T.[level] + 1 AS [level],
		       [PC].[parent_id]				
		FROM cte AS T
			JOIN tblParentChild AS PC ON [T].[parent_id] = [PC].[id]
		WHERE PC.parent_id IS NOT NULL
)
SELECT 
     T2.* ,
	 T1.[1] AS [top_level_id] ,
     T1.[2] AS [top2_level_id]
FROM
	(
	   SELECT PV.*
	   FROM	
			(
			   SELECT 
					GroupID  ,
					id ,
					ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY [level] DESC) AS RowNO
			   FROM CTE
			) AS P
			PIVOT
			(
				MAX(id) FOR RowNO  IN ([1] , [2])
			) AS PV
	) AS T1
	JOIN tblParentChild AS T2 ON T1.GroupID = t2.id

HierarchyID

先把父子階層資料轉換為 HierarchyID,參考 [SQL] HierarchyID - 批次 insert 內容
DROP TABLE IF EXISTS tblHID

CREATE TABLE tblHID  
( 
	HID HierarchyID PRIMARY KEY CLUSTERED ,  
	ID int UNIQUE NOT NULL,  
	Name nvarchar(100) NOT NULL
) ;  

; 
WITH cteRowNO AS
(
    SELECT 
        ID ,
        Name ,
        Parent_ID ,
        -- 進行資料編號
        ROW_NUMBER() OVER (PARTITION BY Parent_ID ORDER BY Parent_ID) AS RowNO
    FROM tblParentChild
)
,
cteHID AS
(
    SELECT
        ID , 
        Name ,
        Parent_ID ,
        RowNO ,
        CAST(0 as int) AS TreeLevel ,
        CAST(Name as nvarchar(4000)) AS TreePath ,
        -- 串接 HierarchyID
        hierarchyid::GetRoot() AS HID
    FROM cteRowNO
    WHERE Parent_ID IS NULL
    UNION ALL
    SELECT
        C.ID , 
        C.Name , 
        C.Parent_ID ,
        C.RowNO ,
        CAST(TreeLevel + 1 as int) AS TreeLevel ,
        CAST(TreePath + '_' + C.Name as nvarchar(4000)) AS TreePath ,
        -- 串接 HierarchyID
        CAST(T.HID.ToString() + CAST(C.RowNO AS varchar(30)) + '/' AS hierarchyid) AS HID
    FROM cteHID AS T
        JOIN cteRowNO AS C ON T.ID = C.Parent_ID
)
INSERT INTO tblHID (HID , ID , Name)
SELECT HID , ID , Name
FROM cteHID
實際執行 TSQL 
SELECT 
	T3.* ,
	PV.[1] AS [top_level_id], 
	PV.[2] AS [top2_level_id]
FROM 
	(
		SELECT 			
			T1.ID AS ParentID,
			T2.ID ,
			T2.HID.GetLevel() AS [Level] 
		FROM tblHID AS T1
                        -- 重點條件
			JOIN tblHID AS T2 ON T2.HID.IsDescendantOf(T1.HID.GetAncestor(T1.HID.GetLevel() - 1)) = 1
								AND T2.HID.GetLevel() IN ( 1 , 2 )
		WHERE T1.[name] IN ('A1', 'B12', 'C1234', 'D')
	) AS P
	PIVOT
	(
		MAX(ID) FOR [Level] IN ([1] , [2])
	) AS PV
	JOIN tblHID AS T3 ON PV.ParentID = T3.ID
ORDER BY ParentID

重點條件解析,從下述獨立語法可以找出該條件最上層 top1_levle 節點
SELECT * ,
	HID.GetAncestor(HID.GetLevel() - 1) AS top1_level
FROM tblHID 
WHERE ID > 0
ORDER BY ID

星期四, 5月 16, 2024

[.NET] 設定檔 - appsettings.json

在 .NET 8 上建立 Worker Service 預設有 appsettings.json 和 appsettings.Development.json 兩個 json 檔案
可以自行新增 json 檔案,在下述三篇文章內,都有提到新增 json 檔案時,可以透過調整 project 檔案,讓新增 json 檔案有巢狀階層,但實際上操作發現,只要是 appsettings 開頭,就會直接變成巢狀階層,VS 版本為 17.9.6
環境模式

在 Use multiple environments in ASP.NET Core 內提到有三種環境模式,分別為
  • Development:launchSettings.json 內 DOTNET_ENVIRONMENT 預設值
  • Staging
  • Production:沒有設定 DOTNET_ENVIRONMENT 預設值
可根據不同環境模式載入不同的 appsettings.json 檔案設定,但部屬出去後不會有 launchSettings.json,也就沒有 DOTNET_ENVIRONMENT,預設即為 Production,只會載入 appsettings.json 而已,不會載入 appsettings.Development.json,另外除了系統環境變數 (該筆記是DOTNET_ENVIRONMENT) 外,也可以自訂環境變數 (該筆記是 SecretKey)。


測試 Worker Service 專案

延續 [.NET] 設定檔 - launchSettings.json 內容,已經建立 Staging Profile,後續會記錄 profile 切換 appsettings.json、自訂環境變數和部屬為 Windows Service,相關設定檔案如下

launchSettings.json
{
  "$schema": "http://json.schemastore.org/launchsettings.json",
  "profiles": {
    "EnvironmentsSample": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "environmentVariables": {
        "DOTNET_ENVIRONMENT": "Development",
        // 自訂環境變數 SecretKey
        "SecretKey": "Secret key value From EnvironmentsSample"
      }
    },
    "Staging": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "environmentVariables": {
        "DOTNET_ENVIRONMENT": "Staging"
        // 沒有設定 SecretKey
      }
    }
  }
}
appsettings.json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "SqlServer": "Server=RemoteIP;Database=AdventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true"
  }
}
appsettings.Development.json
{
  "ConnectionStrings": {
    "SqlServer": "Server=.;Database=AdventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true"
  }
}
appsettings.Staging.json
{
  "ConnectionStrings": {
    "SqlServer": "Server=StagingIP;Database=AdventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true"
  }
}

根據 profile 取得 DOTNET_ENVIRONMENT 環境變數


取得 DOTNET_ENVIRONMENT 環境變數的三種方式
namespace EnvironmentsSample
{
    public class Program
    {
        public static void Main(string[] args)
        {
            HostApplicationBuilder builder = Host.CreateApplicationBuilder(args);

            Console.WriteLine("----- 根據 profile 來取得 DOTNET_ENVIRONMENT 環境變數 -----");
            Console.WriteLine($"builder.Environment.EnvironmentName:{builder.Environment.EnvironmentName}");
            Console.WriteLine($"builder.Configuration[\"DOTNET_ENVIRONMENT\"]:{builder.Configuration["DOTNET_ENVIRONMENT"]}");
            Console.WriteLine($"Environment.GetEnvironmentVariable(\"DOTNET_ENVIRONMENT\"):{Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT")}");
        }
    }
}
下圖是以 EnvironmnetSample profile 的執行結果

根據 profile 的 DOTNET_ENVIRONMENT 來決定 appsettings 設定檔案


不使用 Host.CreateApplicationBuilder 產生預設 configuration,手動自行產生 configuration 來讀取
讀取 appsettings 內的連線字串
namespace EnvironmentsSample
{
    public class Program
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("----- 根據 profile 的 DOTNET_ENVIRONMENT 來決定 appsettings 設定檔案 -----");
            string environment = Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT");

            IConfiguration configuration = new ConfigurationBuilder()
                .SetBasePath(AppContext.BaseDirectory)
                .AddJsonFile("appsettings.json")
                .AddJsonFile($"appsettings.{environment}.json", optional: true)
                .AddEnvironmentVariables()
                .Build();

            string connectionString = configuration.GetConnectionString("SqlServer");

            Console.WriteLine($"Environment.GetEnvironmentVariable(\"DOTNET_ENVIRONMENT\"):{environment}");
            Console.WriteLine(connectionString);
        }
    }
}
下圖是以 Staging profile 的執行結果
下述 AddJsonFile() 第二參數,在對應 profile 載入 appsettings.json 時,理論上都會設定,萬一對應 appsettings.json 不存在就不載入該設定檔案就是
.AddJsonFile($"appsettings.{environment}.json", optional: true)

自訂環境變數:SecretKey

在 Configuration providers in .NET 內有提到該段文字


在 Windows 和  launchSettings.json 內的 EnvironmentSample profile 建立自訂環境變數 SecretKey,Staging profile 內故意沒有設定,當 profile 內有設定,會以 profile 為主,沒有才會使用 Windows 環境變數
namespace EnvironmentsSample
{
    public class Program
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("----- 自訂環境變數:SecretKey -----");
            string environment = Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT");
            string secretKey = Environment.GetEnvironmentVariable("SecretKey");
            Console.WriteLine($"Environment.GetEnvironmentVariable(\"DOTNET_ENVIRONMENT\"):{environment}");
            Console.WriteLine($"SecretKey:{secretKey}");
        }
    }
}
下圖是使用 EnvironmentSample profile 和 Staging profile 的疊圖
  • EnvironmentSample profile:讀取 launchSettings 內的 SecretKey
  • Staging profile:讀取 Windows 環境變數

部屬為 Windows Service

參考 [.NET] 使用 BackgroundService 建立 Windows Service 把 Worker Service 註冊為 Windows Service,觀察部屬後抓到的 appsettings.json

Program 內設定為 Windows Service
namespace EnvironmentsSample
{
    public class Program
    {
        public static void Main(string[] args)
        {
            HostApplicationBuilder builder = Host.CreateApplicationBuilder(args);

            builder.Services.AddWindowsService(options =>
            {
                options.ServiceName = "EnvironmentsService";
            });

            builder.Services.AddHostedService<Worker>();

            var host = builder.Build();
            host.Run();
        }
    }
}
Worker 內注入 configuration,並輸出相關資訊
using System.Text;

namespace EnvironmentsSample
{
    public class Worker : BackgroundService
    {
        private readonly ILogger<Worker> _logger;
        private readonly IConfiguration _configuration;

        public Worker(ILogger<Worker> logger, IConfiguration configuration)
        {
            _logger = logger;
            _configuration = configuration;
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            string environment = _configuration["DOTNET_ENVIRONMENT"];
            string secretKey = _configuration["SecretKey"];
            string connectionString = _configuration.GetConnectionString("SqlServer");

            StringBuilder sb = new StringBuilder();
            sb.AppendLine($"Worker running at: {DateTimeOffset.Now}");
            sb.AppendLine($"DOTNET_ENVIRONMENT:{environment}");
            sb.AppendLine($"SecretKey:{secretKey}");
            sb.AppendLine($"ConnectionString:{connectionString}");

            _logger.LogError(sb.ToString());
        }
    }
}

星期五, 5月 10, 2024

[SQL] 避免在 Select 中使用 Scalar Function (續)

[SQL] 避免在 Select 中使用 Scalar Function 內有提到 Scalar Function 副作用,該筆記內是用 getTotalPrice() 來驗證,該 function 有實際讀取 table 行為,在網路上看見問題,function 內不涉及 table 讀取,單純就是參數輸入後解析再輸出而已,該篇筆記驗證看看該情況,Scalar Funcation 是否也會反覆處理

建立 Scalar Function

把 [日期 datetime] 轉為 [民國年 char(7)],不涉及 table 讀取
CREATE FUNCTION udfConvertToTWDate
(
	@ADDate datetime
)
RETURNS char(7)
AS
BEGIN
	DECLARE @TWYY char(3) = RIGHT(REPLICATE('0' , 3) + CAST(YEAR(@ADDate) - 1911 as varchar(3)) , 3)
	DECLARE @MM char(2) = RIGHT(REPLICATE('0' , 2) + CAST(MONTH(@ADDate) as varchar(2)) , 2)
	DECLARE @DD char(2) = RIGHT(REPLICATE('0' , 2) + CAST(DAY(@ADDate) as varchar(2)) , 2)
	RETURN @TWYY + @MM + @DD
END
GO

實際執行


ALTER DATABASE [AdventureWorks2022] SET COMPATIBILITY_LEVEL = 140;

SELECT
	BusinessEntityID , 
	LastName ,
	MiddleName ,
	FirstName ,
	ModifiedDate ,
	[dbo].udfConvertToTWDate(ModifiedDate) AS TWDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2008-01-01' AND '2008-12-31'

Profile 觀察

在 Profile 內還是可以看見反覆執行情況,看起來這是 Scalar Function 宿命啦,另外開 Profile 時要選擇 Tuning 範本來收集,一開始使用預設 standard 範本觀察不到,還想說讀不讀取 table 竟然會有差別,完全就是誤會


星期二, 5月 07, 2024

[.NET] 設定檔 - launchSettings.json

在 .NET 8 上使用 Worker Service 來了解該官方文章 Use multiple environments in ASP.NET Core 內容,主要是紀錄 launchSettings.json,該設定檔可以在 Properties 內找到,有下列特性
  • 只能應用在本機開發使用
  • 包含 Profile 設定
  • 不會進行部屬,publish 時不會產生相對應檔案


Workder Service 建立後 launchSettingss.json 預設內容
{
  "$schema": "http://json.schemastore.org/launchsettings.json",
  "profiles": {
    "EnvironmentsSample": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "environmentVariables": {
        "DOTNET_ENVIRONMENT": "Development"
      }
    }
  }
}
launchSettings.json 檔案內容的 profile 即為 VS 上選項


也可以直接在 launchSettings.json 內建立多個 profile,下述 launchSetting.json 新增 Staging profile
{
  "$schema": "http://json.schemastore.org/launchsettings.json",
  "profiles": {
    "EnvironmentsSample": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "environmentVariables": {
        "DOTNET_ENVIRONMENT": "Development"
      }
    },
    "Staging": {
      "commandName": "Project",
      "dotnetRunMessages": true,
      "environmentVariables": {
        "DOTNET_ENVIRONMENT": "Staging"
      }
    }
  }
}


除了直接編輯 launchSettings.json 外,VS 上也有提供 UI 介面可以操作





星期五, 5月 03, 2024

[SQL] Like - 萬用字元和 Index Seek

在社群內看見討論,提到 LIKE 'A_E%' 是跑 Index Scan,但因為是以 A 為開頭字串,直覺是會跑 Index Seek 才是,來測試看看
USE AdventureWorks2022
GO

DROP INDEX IF EXISTS [IX_LastName] ON Person.Person

CREATE INDEX IX_LastName ON Person.Person (LastName)

SELECT
	BusinessEntityID ,
	LastName
FROM Person.Person
-- 單一字元
WHERE LastName LIKE 'A_E%'
-- 相符字元
WHERE LastName LIKE 'A[a-Z]E%'
-- 不相符
WHERE LastName LIKE 'A[^0-9]E%'


上述三種 LIKE 條件皆可以跑出 Index Seek,第一位字元為 A 是關鍵啦