[SSMS] 效能觀察

以往要觀察 TSQL 執行的 Logical Read 和 CPU Time，都會直接在 Query 開頭直接打 SET STATISTICS IO , TIME ON 來啟用，在社群上看到大神分享才發現，原來 SSMS 內有查詢選項可以直接勾選啟用

開啟 Query 後才會出現 [查詢] Tag，點選進入 [查詢選項]

執行 => 進階內就可以看見 SET STATISTICS IO 和 SET STATISTICS TIME 選項

[C#] 環境變數

要透過 C# 存取 Windows 環境變數，要了解下述函式

• EnvironmentVariableTarget Enum
• Environment.GetEnvironmentVariables
• Environment.SetEnvironmentVariable

EnvironmentVariableTarget

環境變數範圍

• Process：目前 Process
• User：儲存在 HKEY_CURRENT_USER\Environment 
• Machine：儲存在 HKEY_CURRENT_USER\Environment

把環境變數存放在 Process 的話，該 Process 結束後環境變數也就消失囉

Environment.SetEnvironmentVariable

新增或修改

• 環境變數不存在：新增
• 環境變數存在：修改

刪除，值設定為下列三者都算是要刪除該環境變數

• null (該範例使用)
• String.Empty
• U+0000

刪除時假如該環境變數不存在，不會拋錯誤

簡易範例

WinForms 簡易範例來對環境變數進行 CRUD

using System.Collections;

namespace EnvVarSample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            dgvSearch.AutoGenerateColumns = false;
            cboSearch.DataSource = Enum.GetNames(typeof(EnvironmentVariableTarget));
            cboEnvironmentVariableTarget.DataSource = Enum.GetNames(typeof(EnvironmentVariableTarget));
        }

        private void cboSearch_SelectionChangeCommitted(object sender, EventArgs e)
        {
            var (_, _, target) = GetValue();

            var source = new List<Source>();
            IDictionary vars = Environment.GetEnvironmentVariables(target);
            foreach (DictionaryEntry entry in vars)
                source.Add(new Source() { EnvVar = entry.Key.ToString(), Value = entry.Value.ToString() });

            dgvSearch.DataSource = source;
        }

        private void btnAddOrModify_Click(object sender, EventArgs e)
        {
            var input = GetValue();
            Environment.SetEnvironmentVariable(input.EnvVar, input.Value, input.Target);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            var (EvnVar, _, target) = GetValue();
            Environment.SetEnvironmentVariable(EvnVar, null, target);
        }

        private (string EnvVar, string Value, EnvironmentVariableTarget Target) GetValue()
        {
            string item = cboEnvironmentVariableTarget.SelectedItem.ToString();
            if (Enum.TryParse(item, out EnvironmentVariableTarget target) == false)
                throw new NotSupportedException(nameof(item));

            return (
                txtEnvVar.Text.Trim(),
                txtValue.Text.Trim(),
                target);
        }

        private void dgvSearch_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvSearch.CurrentRow == null)
                return;

            cboEnvironmentVariableTarget.SelectedIndex = cboEnvironmentVariableTarget.FindStringExact(cboSearch.SelectedItem.ToString());
            txtEnvVar.Text = dgvSearch.CurrentRow.Cells[ColEnvVar.Index].Value?.ToString();
            txtValue.Text = dgvSearch.CurrentRow.Cells[ColValue.Index].Value?.ToString();
        }

        private class Source
        {
            public string EnvVar { get; set; }

            public string Value { get; set; }
        }

    }
}

GetEnvironmentVariables() 和 SetEnvironmentVariable() 沒有指定 EnvironmentVariableTarget 的多載，其 EnvironmentVariableTarget 為 Process 喔

Windows OS GUI 操作

在 Windows OS 的 [系統內容] =>  進階 => 環境變數內，可以直接編修 User 和 Machine 的環境變數

• 延伸閱讀
• [C#] ComboBox 預設值
• 參考資料
• StackOverFlow 討論
• EnvironmentVariablesExtensions.AddEnvironmentVariables Method
• Discard

[EFCore] Scaffold-DbContext

在 [EFCore] 在 Console 專案上安裝設定 內使用 Scaffold-DbContext 產生 DbContext 和 Entity 後，使用時發現有些細節想像中有落差，在官方文章 - Entity Framework Core tools reference - Package Manager Console in Visual Studio 內找到 Scaffold-DbContext 參數說明

必要參數

-Connection 和 -Provider 為必要參數，網路上寫法大多是省略參數標示，寫法如下

-- 完整寫法
Scaffold-DbContext 
    -Connection "Server=.;Database=AsventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true" 
    -Provider Microsoft.EntityFrameworkCore.SqlServer
  
-- 常見寫法
Scaffold-DbContext
    "Server=.;Database=AsventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true"
    Microsoft.EntityFrameworkCore.SqlServer

-Tables

指定要建立 Entity 的 Table，重覆使用該參數，必須每次都明確指定 Table，原因在於 DbContext 會被重建，第一次執行 Scaffold-DbContext 建立的 Entity，在第二次執行 Scaffold-DbContext 後，假如沒有明確指定，Entity 雖然存在，但 DbContext 內是沒有的

-NoOnConfiguring 

預設會在 DbContext.OnConfiguring 內產生下列 Code，連線字串會 HardCode 寫在裡面

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer("連線字串");

實務上慣用語法

Scaffold-DbContext 
	"Server=.;Database=AdventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true" 
	Microsoft.EntityFrameworkCore.SqlServer 
	-ContextDir DbContextDir            // 指定 DbContext 資料夾位置    
	-Context AdventureWork2022DbContext // 指定 DbContext 名稱
	-OutputDir EntityDir                // 指定 Entity 資料夾位置
	-Table Person                       // 指定特定 Table，不指定即建立整個 DB 內 Table 
	-NoPluralize                        // 不要複數化
	-UseDatabaseNames                   // Entity 名稱和 DB 內一樣
	-Force                              // 覆寫現有檔案

以上語法為方便閱讀，都會換行排列，實際在執行是串接在一起的

[SQL] 彙總函數應用 - 資料分組

要把 txt 檔案匯入系統內做資料檢核，txt 檔案內資料如下圖示意，希望能透過 TSQL 把員工和眷屬群組在一起

使用視窗彙總函數跑 Running Total 來達到該需求

DECLARE @Temp Table (RowNO char(7) , FullName nchar(8) , FName nchar(8))
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000001' , N'員工一' , N'')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000002' , N'' , N'眷屬一_1')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000003' , N'' , N'眷屬一_2')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000004' , N'員工二' , N'')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000005' , N'員工三' , N'')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000006' , N'' , N'眷屬三_1')

SELECT * , 
	SUM(IIF([FullName] = '' , 0 , 1)) 
	OVER 
		(
			ORDER BY RowNO
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
		) AS GroupNO
FROM @Temp

• 延伸閱讀
• [SQL] 次序函數應用-重覆資料不顯示
• [SQL] 次序函數應用-資料分組

[EFCore] ExecuteUpdate 和 ExecuteDelete

ExecuteUpdate 和 ExecuteDelete 為 EFCore 7 推出功能，可以直接對單一 Table 進行 update 和 delete，不牽涉到 Entity Tracking，且該語法會直接執行，不需要使用 SaveChange()，但SaveChange() 自帶交易，而 ExecuteDelete 和 ExecuteUpdate 沒有，要自行開啟 Transaction 來確保一致性喔

ExecuteDelete

master-detail table 架構 EX：訂單，要使用 ORM 刪除的話，語法會如同下列

internal class Program
{
    static void Main(string[] args)
    {
        using var dbContext = new AdventureWorks2022Context();

        var header = dbContext.SalesOrderHeaders.Single(w => w.SalesOrderId == 43659);
        var detail = dbContext.SalesOrderDetails.Where(w => w.SalesOrderId == 43659).ToList();

        dbContext.SalesOrderDetails.RemoveRange(detail);
        dbContext.SalesOrderHeaders.Remove(header);

        dbContext.SaveChanges();
    }
}

使用 SQL Profile 側錄語法可以看見是先把資料抓出來後，根據 SalesOrderDetail Table PK 一筆一筆資料進行刪除

下列為 SalesOrderDetails 刪除語法，留三個 delete 來示意

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderDetailID] = @p0 AND [SalesOrderID] = @p1;
SELECT @@ROWCOUNT;

DELETE FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderDetailID] = @p2 AND [SalesOrderID] = @p3;
SELECT @@ROWCOUNT;

DELETE FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderDetailID] = @p4 AND [SalesOrderID] = @p5;
SELECT @@ROWCOUNT;

',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p0=15,@p1=43661,@p2=16,@p3=43661,@p4=17,@p5=43661'

以往為了提高效能就乾脆直接下 TSQL 根據訂單編號去刪除，現在有 ExecuteDelete 後就可以直接來進行刪除

internal class Program
{
    static void Main(string[] args)
    {
        using var dbContext = new AdventureWorks2022Context();
        using var tran = dbContext.Database.BeginTransaction();
        
        dbContext.SalesOrderDetails
            .Where(w => w.SalesOrderId == 123)
            .ExecuteDelete();
        
        dbContext.SalesOrderHeaders
            .Where(w => w.SalesOrderId == 123)
            .ExecuteDelete();
        
        tran.Commit();
    }
}

ExecuteUpdate

ExecuteUpdate 可以透過 SetProperty 來針對多個欄位進行資料更新，用個範例紀錄

internal class Program
{
    static void Main(string[] args)
    {
         using var dbContext = new AdventureWorks2022Context();
         
         int numUpdated = dbContext.SalesOrderDetails
                .Where(w => w.SalesOrderId == 43664)
                .ExecuteUpdate(e =>
                e.SetProperty(sod => sod.ModifiedDate, DateTime.Now));
         
         Console.WriteLine("影響筆數 : " + numUpdated);
    }
}

• 參考資料
• ExecuteUpdate 和 ExecuteDelete （大量更新）
• ExecuteUpdate 和 ExecuteDelete
• EF Core Execute Update
• Entity Framework（EF）7簡介 - 1
• 在 EF Core 7.0 中針對大量更新與大量刪除的效能改善