Kiểu tham số từ .NET làm truy vấn quét cả bảng

1. Vấn đề: tra một số điện thoại mà đọc cả bảng khách #

Quầy thu ngân của BanHang tra khách theo số điện thoại để cộng điểm và áp mã giảm giá. Cột dbo.KhachHang.SoDienThoai là varchar(20), và câu tra dùng chỉ mục IX_KhachHang_SoDienThoai (SoDienThoai). Trên bản thử 200.000 khách, câu tra đúng kiểu đọc 6 page: 3 page seek chỉ mục, 3 page lookup lấy tên.

Một phiên bản API đọc ô nhập số điện thoại vào long rồi đưa thẳng cho Dapper. Tham số tới server là bigint, và câu tra thành Clustered Index Scan: 1.388 page cho mỗi lần tra. Sau đó một khách được nhập số 0912 345 678, có dấu cách. Từ lúc đó mọi lần tra, với bất kỳ số nào, đều lỗi 8114 Error converting data type varchar to bigint.

Chuỗi cũng không an toàn ở mọi nơi. Instance LocalDB trên máy thử cài với collation SQL_Latin1_General_CP1_CI_AS, và database tạo không ghi COLLATE nhận collation đó. Trên cột varchar mang collation SQL, tham số chuỗi mặc định của Dapper, của AddWithValue và của EF Core khi quên IsUnicode(false) đều làm câu tra đọc 602 page thay vì 6.

2. Mục đích: 6 page với mọi thư viện, mọi collation #

• Câu tra một số điện thoại đọc 6 page bằng Index Seek cộng lookup, dù gửi từ Dapper, ADO.NET hay EF Core, trên cột collation Windows lẫn collation SQL.
• Plan cache không còn cảnh báo PlanAffectingConvert cho câu tra.
• Một số điện thoại lưu sai định dạng không làm hỏng câu tra số khác.

3. Cơ sở lý thuyết: vế có kiểu ưu tiên thấp hơn bị đổi kiểu #

Khi hai vế của phép so sánh khác kiểu, SQL Server đổi vế có kiểu ưu tiên thấp hơn sang kiểu cao hơn. Trong bảng thứ tự ưu tiên, bigint đứng trên nvarchar, và nvarchar đứng trên varchar. Cột SoDienThoai varchar(20) so với tham số bigint hay nvarchar thì chính cột bị đổi: kế hoạch ghi CONVERT_IMPLICIT(bigint, SoDienThoai) hoặc CONVERT_IMPLICIT(nvarchar(20), SoDienThoai). Chỉ mục được sắp theo giá trị gốc của cột, nên khi cột bị bọc trong phép đổi kiểu, seek chỉ còn nếu trình tối ưu tìm được một khoảng giá trị gốc tương đương.

Với bigint, không có khoảng nào như vậy. Chuỗi '0900156954' và số 900156954 xếp theo hai thứ tự khác nhau, nên engine đọc mọi dòng, đổi từng giá trị sang số rồi so. Dòng nào không đổi được, như '0912 345 678', làm cả câu lỗi, vì lần quét đi qua mọi dòng.

Với nvarchar, kết quả phụ thuộc collation của cột:

• Collation Windows, như Vietnamese_100_CI_AS của BanHang, so varchar và nvarchar bằng cùng một thuật toán. Trình tối ưu tính trước khoảng varchar tương đương với tham số bằng GetRangeThroughConvert, rồi seek trong khoảng đó, và giữ phép so có CONVERT_IMPLICIT làm điều kiện phụ.
• Collation SQL, tiền tố SQL_, dùng luật khác nhau cho dữ liệu Unicode và không Unicode. Không có khoảng tương đương, nên engine quét cả chỉ mục, và kế hoạch mang cảnh báo PlanAffectingConvert với ConvertIssue="Seek Plan".

Ba trường hợp nhìn thấy được trong kế hoạch. Đoạn dưới đọc bằng SET SHOWPLAN_TEXT ON trên bảng thử ở mục 5, tham số là biến cục bộ, đã bỏ tên database và bảng cho gọn:

-- bigint, collation Windows
Clustered Index Scan  WHERE: CONVERT_IMPLICIT(bigint,[SoDienThoai],0)=[@S]

-- nvarchar, collation Windows
Compute Scalar        GetRangeThroughConvert([@S],[@S],(62))
Index Seek            SEEK: [SoDienThoai] > [Expr1004] AND [SoDienThoai] < [Expr1005]
                      WHERE: CONVERT_IMPLICIT(nvarchar(20),[SoDienThoai],0)=[@S]

-- nvarchar, collation SQL
Index Scan            WHERE: CONVERT_IMPLICIT(nvarchar(20),[SoDienThoai],0)
                             = CONVERT_IMPLICIT(nvarchar(4000),[@S],0)

flowchart TD
  A["Cột SoDienThoai varchar(20) so với @Sdt"] --> B{"Kiểu của @Sdt"}
  B -->|"varchar(20)"| C["Cùng kiểu: Index Seek"]
  B -->|"nvarchar"| D{"Collation của cột"}
  B -->|"bigint"| E["Cột đổi sang bigint: quét, có thể lỗi 8114"]
  D -->|"Windows"| F["GetRangeThroughConvert: Index Seek"]
  D -->|"SQL_"| G["Không có khoảng tương đương: Index Scan"]

Chiều ngược lại an toàn: cột nvarchar so với tham số varchar thì tham số bị đổi, cột giữ nguyên.

Kiểu của tham số do thư viện .NET chọn từ kiểu C# nếu không khai báo:

string của .NET là UTF-16, nên mặc định của cả ba thư viện là kiểu Unicode. Cột varchar là ngoại lệ phải khai báo.

4. Cách giải quyết: khai báo tham số đúng kiểu và độ dài của cột #

BanHang chọn cách cuối. Cột giữ nguyên, và kế hoạch không phụ thuộc collation của nơi database được cài. Ép kiểu trong SQL cũng seek, nên nó là cách chữa cháy khi chưa sửa được ứng dụng.

Các bước:

1. Giữ số điện thoại trong string ở mọi tầng của ứng dụng. Nó là chuỗi: có số 0 đầu, có thể có dấu +, không làm phép tính.
2. Với Dapper, gói mọi tham số cho cột varchar trong DbString { IsAnsi = true, Length = n }, với n bằng độ dài cột.
3. Với ADO.NET, thay AddWithValue bằng SqlParameter khai báo SqlDbType.VarChar và độ dài.
4. Với EF Core, khai báo IsUnicode(false).HasMaxLength(n) cho thuộc tính ánh xạ vào cột varchar(n); mọi câu LINQ trên thuộc tính đó tự gửi varchar(n).
5. Định kỳ tìm PlanAffectingConvert trong plan cache để bắt chỗ còn sót.

5. Cách cài đặt: ba thư viện, một kiểu tham số #

// Dapper: chuỗi ANSI, đúng độ dài cột
var khach = await conn.QueryAsync<(int KhachHangId, string Ten)>(
    "SELECT KhachHangId, Ten FROM dbo.KhachHang WHERE SoDienThoai = @Sdt;",
    new { Sdt = new DbString { Value = sdt, IsAnsi = true, Length = 20 } });

// ADO.NET: khai báo kiểu thay cho AddWithValue
cmd.Parameters.Add(new SqlParameter("@Sdt", SqlDbType.VarChar, 20) { Value = sdt });

// EF Core: model nói đúng kiểu cột, mọi câu LINQ gửi varchar(20)
e.Property(k => k.SoDienThoai).IsUnicode(false).HasMaxLength(20);
var timThay = await db.KhachHang.Where(k => k.SoDienThoai == sdt)
    .Select(k => new { k.KhachHangId, k.Ten }).ToListAsync();

Biến sdt là string từ đầu đến cuối. Danh sách cột cần khai báo như vậy lấy từ chính database: mọi cột varchar và char, kèm độ dài và collation. Trên database thử, câu này trả SoDienThoai varchar(20) của hai bảng thử, một cột Vietnamese_100_CI_AS, một cột SQL_Latin1_General_CP1_CI_AS:

SELECT OBJECT_NAME(c.object_id) AS bang, c.name AS cot,
       TYPE_NAME(c.user_type_id) AS kieu, c.max_length AS do_dai, c.collation_name
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE TYPE_NAME(c.user_type_id) IN ('varchar', 'char')
ORDER BY bang, cot;

Câu dưới tìm 20 câu đọc nhiều nhất có cảnh báo PlanAffectingConvert trong plan cache. Nó đọc XML của mọi kế hoạch đang lưu, nên chạy ngoài giờ cao điểm:

SELECT TOP (20)
    qs.execution_count,
    qs.total_logical_reads,
    SUBSTRING(st.text, 1, 200) AS cau_lenh
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE CAST(qp.query_plan AS nvarchar(max)) LIKE N'%PlanAffectingConvert%'
ORDER BY qs.total_logical_reads DESC;

Chương trình ThamSo.cs dựng hai bảng 200.000 khách, giống phần KhachHangId, ChiNhanhId, Ten, SoDienThoai của dbo.KhachHang. Bảng KhachHangSdt mang collation của database Kumeo_D, Vietnamese_100_CI_AS. Bảng KhachHangSdt_Latin chỉ khác ở collation SQL_Latin1_General_CP1_CI_AS của SoDienThoai. Số điện thoại có dạng 09 cộng 8 chữ số; khách 4242 có số 0900156954. Chương trình tra số đó bằng bảy cách trên mỗi bảng, rồi đọc kiểu tham số, toán tử, cảnh báo và logical reads từ plan cache. Cuối cùng nó thêm một số có dấu cách và tra lại bằng long, rồi thử ánh xạ mọi string sang varchar.

Môi trường: .NET 10.0.12, Dapper 2.1.89, Microsoft.EntityFrameworkCore.SqlServer 10.0.12, SQL Server 2019 LocalDB 15.0.4382, Intel Core Ultra 5 125U. Dòng #:property PublishAot=false tắt AOT mà file-based app bật mặc định, vì Dapper và EF Core sinh code lúc chạy. EF Core giữ một model cho mỗi kiểu DbContext, nên mỗi cách khai báo là một lớp con. Chạy bằng dotnet run ThamSo.cs.

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.12
#:package Dapper@2.1.89
#:property PublishAot=false
// Tra khách theo số điện thoại: cùng một câu, bảy cách gửi tham số từ Dapper, ADO.NET và EF Core,
// trên cột varchar(20) collation Windows (như BanHang) và collation SQL. Đọc kiểu tham số,
// toán tử và logical reads từ plan cache.
using System.Data;
using System.Xml.Linq;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

using var conn = new SqlConnection(Db.Cs);
conn.Open();
conn.Execute("""
    DROP TABLE IF EXISTS dbo.KhachHangSdt_Latin; DROP TABLE IF EXISTS dbo.KhachHangSdt;
    CREATE TABLE dbo.KhachHangSdt (
        KhachHangId int NOT NULL CONSTRAINT PK_KhachHangSdt PRIMARY KEY CLUSTERED,
        ChiNhanhId int NOT NULL,
        Ten nvarchar(200) NOT NULL,
        SoDienThoai varchar(20) NULL);                       -- collation của database: Vietnamese_100_CI_AS
    INSERT dbo.KhachHangSdt (KhachHangId, ChiNhanhId, Ten, SoDienThoai)
    SELECT n, n % 120 + 1, N'Khách ' + CAST(n AS nvarchar(10)), '09' + RIGHT('00000000' + CAST(n * 37 AS varchar(10)), 8)
    FROM (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
          FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS t;
    CREATE INDEX IX_KhachHangSdt_SoDienThoai ON dbo.KhachHangSdt (SoDienThoai);

    -- Bản sao chỉ khác collation SQL của SoDienThoai
    SELECT KhachHangId, ChiNhanhId, Ten, SoDienThoai INTO dbo.KhachHangSdt_Latin FROM dbo.KhachHangSdt;
    ALTER TABLE dbo.KhachHangSdt_Latin ALTER COLUMN SoDienThoai varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL;
    ALTER TABLE dbo.KhachHangSdt_Latin ADD CONSTRAINT PK_KhachHangSdt_Latin PRIMARY KEY CLUSTERED (KhachHangId);
    CREATE INDEX IX_KhachHangSdt_Latin_SoDienThoai ON dbo.KhachHangSdt_Latin (SoDienThoai);
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    """, commandTimeout: 300);

var sdt = "0900156954";   // khách 4242
foreach (var bang in new[] { "KhachHangSdt", "KhachHangSdt_Latin" })
{
    // Dapper: string, long, DbString. Chú thích trong câu chỉ để tách các dòng trong plan cache.
    conn.Query($"SELECT KhachHangId, Ten FROM dbo.{bang} WHERE SoDienThoai = @Sdt; -- Dapper string", new { Sdt = sdt });
    conn.Query($"SELECT KhachHangId, Ten FROM dbo.{bang} WHERE SoDienThoai = @Sdt; -- Dapper long", new { Sdt = long.Parse(sdt) });
    conn.Query($"SELECT KhachHangId, Ten FROM dbo.{bang} WHERE SoDienThoai = @Sdt; -- Dapper DbString",
        new { Sdt = new DbString { Value = sdt, IsAnsi = true, Length = 20 } });

    // ADO.NET: AddWithValue, và SqlParameter khai báo đủ kiểu.
    using (var cmd = new SqlCommand($"SELECT KhachHangId, Ten FROM dbo.{bang} WHERE SoDienThoai = @Sdt; -- AddWithValue", conn))
    {
        cmd.Parameters.AddWithValue("@Sdt", sdt);
        using var r = cmd.ExecuteReader(); while (r.Read()) { }
    }
    using (var cmd = new SqlCommand($"SELECT KhachHangId, Ten FROM dbo.{bang} WHERE SoDienThoai = @Sdt; -- SqlParameter", conn))
    {
        cmd.Parameters.Add(new SqlParameter("@Sdt", SqlDbType.VarChar, 20) { Value = sdt });
        using var r = cmd.ExecuteReader(); while (r.Read()) { }
    }
}

// EF Core: quên IsUnicode(false), và khai báo đủ.
foreach (var db in new KhachDb[] { new NvarcharWin(), new VarcharWin(), new NvarcharLatin(), new VarcharLatin() })
    using (db) db.KhachHang.Where(k => k.SoDienThoai == sdt).Select(k => new { k.KhachHangId, k.Ten }).ToList();

XNamespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
foreach (var row in conn.Query<(string Text, long Reads, string Plan)>("""
    SELECT st.text, qs.last_logical_reads, CAST(qp.query_plan AS nvarchar(max))
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
    WHERE pa.attribute = 'dbid' AND CAST(pa.value AS int) = DB_ID()
      AND st.text LIKE N'(@%KhachHangSdt%'
    ORDER BY qs.creation_time;
    """))
{
    var xml = XDocument.Parse(row.Plan);
    var truyCap = xml.Descendants(ns + "RelOp").Select(r => (string)r.Attribute("PhysicalOp")!)
        .Where(o => o.Contains("Index") || o.Contains("Lookup")).Distinct();
    var canhBao = xml.Descendants(ns + "PlanAffectingConvert").Select(c => (string)c.Attribute("ConvertIssue")!).Distinct();
    var nguon = row.Text.Contains("-- ") ? row.Text[(row.Text.IndexOf("-- ") + 3)..].Trim() : "EF Core";
    var bang = row.Text.Contains("KhachHangSdt_Latin") ? "collation SQL" : "collation Windows";
    Console.WriteLine($"{nguon,-13} {row.Text[..(row.Text.IndexOf(")SELECT") + 1)],-22} {bang,-17} {row.Reads,5} reads, " +
                      $"{string.Join(" + ", truyCap)}; PlanAffectingConvert: {string.Join(",", canhBao.DefaultIfEmpty("không"))}");
}

// Một số nhập có dấu cách: tham số long làm mọi lần tra báo lỗi, vì cột bị đổi sang bigint ở mọi dòng.
conn.Execute("INSERT dbo.KhachHangSdt (KhachHangId, ChiNhanhId, Ten, SoDienThoai) VALUES (200001, 3, N'Khách 200001', '0912 345 678');");
try { conn.Query("SELECT KhachHangId, Ten FROM dbo.KhachHangSdt WHERE SoDienThoai = @Sdt; -- Dapper long", new { Sdt = long.Parse(sdt) }); }
catch (SqlException ex) { Console.WriteLine($"Dapper long sau khi có '0912 345 678': lỗi {ex.Number}: {ex.Message}"); }
var vanTimDuoc = conn.QuerySingle<int>("SELECT KhachHangId FROM dbo.KhachHangSdt WHERE SoDienThoai = @Sdt;",
    new { Sdt = new DbString { Value = sdt, IsAnsi = true, Length = 20 } });
Console.WriteLine($"DbString varchar(20) vẫn tìm được khách {vanTimDuoc}");

// Cách tắt: ánh xạ mọi string sang varchar cho cả ứng dụng. Số điện thoại seek, nhưng chữ tiếng Việt mất ngay ở tham số.
SqlMapper.AddTypeMap(typeof(string), DbType.AnsiString);
SqlMapper.PurgeQueryCache();
var tenGui = conn.QuerySingle<string>(
    "SELECT CAST(SQL_VARIANT_PROPERTY(@Ten, 'BaseType') AS nvarchar(20)) + N', ' + CAST(@Ten AS nvarchar(50));",
    new { Ten = "Nguyễn Thị Ánh" });
Console.WriteLine($"AddTypeMap(string, AnsiString): tham số tới server {tenGui}");

class KhachHang
{
    public int KhachHangId { get; set; }
    public string Ten { get; set; } = "";
    public string? SoDienThoai { get; set; }
}

// EF Core giữ một model cho mỗi kiểu DbContext, nên mỗi cách khai báo là một lớp con.
abstract class KhachDb(string bang, bool quenIsUnicode) : DbContext
{
    public DbSet<KhachHang> KhachHang => Set<KhachHang>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlServer(Db.Cs);
    protected override void OnModelCreating(ModelBuilder b) => b.Entity<KhachHang>(e =>
    {
        e.ToTable(bang);
        e.Property(k => k.Ten).HasMaxLength(200);
        if (quenIsUnicode) e.Property(k => k.SoDienThoai).HasMaxLength(20);                   // nvarchar(20)
        else e.Property(k => k.SoDienThoai).IsUnicode(false).HasMaxLength(20);               // varchar(20)
    });
}
class NvarcharWin() : KhachDb("KhachHangSdt", true);
class VarcharWin() : KhachDb("KhachHangSdt", false);
class NvarcharLatin() : KhachDb("KhachHangSdt_Latin", true);
class VarcharLatin() : KhachDb("KhachHangSdt_Latin", false);

static class Db
{
    public const string Cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_D;Integrated Security=true;TrustServerCertificate=true";
}

6. Chứng minh: mọi cách khai báo varchar(20) đều seek 6 page #

Logical reads của câu tra khách 4242, đọc từ sys.dm_exec_query_stats:

Tiêu chí đầu ở mục 2 đạt với ba cách khai báo varchar(20): 6 page trên cả hai collation. Mọi câu quét đều mang cảnh báo PlanAffectingConvert loại Seek Plan; hai câu bigint có thêm loại Cardinality Estimate. Các câu seek, kể cả câu nvarchar trên collation Windows, không có cảnh báo nào, nên câu tìm trong plan cache ở mục 5 bắt đúng những câu cần sửa. Đó là tiêu chí thứ hai.

Tiêu chí thứ ba: sau khi thêm khách có số 0912 345 678, câu tra bằng long báo lỗi 8114, còn câu tra bằng DbString vẫn ra khách 4242. Tham số đúng kiểu không đổi cột, nên dòng sai định dạng không bao giờ được đem ra đổi kiểu.

Dòng cuối của chương trình kiểm cách tắt ở mục 4: sau SqlMapper.AddTypeMap(typeof(string), DbType.AnsiString), tham số "Nguyễn Thị Ánh" tới server là varchar với giá trị Nguy?n Th? Ánh. Số điện thoại sẽ seek, nhưng mọi câu tìm theo tên tiếng Việt hỏng theo.

Hai giới hạn. Bảng thử không có temporal, masking và row-level security như dbo.KhachHang thật, nên số page tuyệt đối trên bảng thật sẽ khác. Ép kiểu trong SQL, CAST(@Sdt AS varchar(20)), đo riêng trên bảng collation SQL với tham số nvarchar(4000) cũng ra 6 page, nhưng không nằm trong chương trình trên.

7. Kết luận #

Tham số khác kiểu cột làm cột bị đổi kiểu, và cột bị đổi kiểu thì chỉ mục chỉ còn dùng được khi engine tìm ra khoảng tương đương. Khai báo tham số đúng kiểu và độ dài của cột thì không phải trông vào điều đó.

Trong dự án .NET của bạn:

• Tìm AddWithValue trong code và thay bằng SqlParameter khai báo SqlDbType và độ dài.
• Với Dapper, dùng DbString { IsAnsi = true, Length = n } cho mọi cột varchar(n); với EF Core, khai báo IsUnicode(false).HasMaxLength(n) cho các thuộc tính đó.
• Giữ số điện thoại, mã sản phẩm, mã giảm giá trong string, không parse sang int hay long.
• Chạy câu tìm PlanAffectingConvert trong plan cache sau mỗi lần đổi ORM hay thư viện truy cập dữ liệu.

Những chỗ hay hiểu sai #

• "CONVERT_IMPLICIT trên cột luôn gây quét." Với collation Windows, cột varchar so với tham số nvarchar vẫn seek qua GetRangeThroughConvert.
• "Tham số nvarchar không sao vì BanHang dùng collation Windows." Cùng code chạy trên database collation SQL thì quét.
• "Tham số số nhanh hơn tham số chuỗi." So với cột varchar, tham số bigint làm cột bị đổi kiểu trên mọi dòng.
• "Ép kiểu trong SQL là đủ." Nó seek, nhưng phải nhớ ở từng câu.

Đọc tiếp #

• Bài trước: Đếm đơn theo ngày: BETWEEN, mốc .999 và giờ UTC. Bài tiếp: Khóa chính cho đơn hàng: IDENTITY, SEQUENCE hay GUID.
• Collation Windows và collation SQL so chữ thế nào: Tìm "nguyen" ra "Nguyễn" mà vẫn Index Seek.
• Seek, scan và key lookup: Chỉ mục B-tree và key lookup.

Nguồn #

• Data type precedence
• Collation and Unicode support
• SqlClient: SQL Server data type mappings
• Dapper: README, DbString và ánh xạ kiểu
• EF Core: Entity properties, Unicode và độ dài
• sys.dm_exec_query_plan