Egan伊根

公司的營運系統每月固定日期會進行交易資料的匯整，因營運逐年成長一張表的資料量來到了2億筆

負責彙整資料的程式原本就已經很慢，隨著資料量逐年增加，近幾次發現無法正確查詢出資料並進行彙整產出相應報表

程式碼片段

string sqlStr = @"
           SELECT UniqNo,col1,col2,col3,col4
           FROM  dbo.TableName
           WHERE ID=@ID ";
if (_SqlConnection.State == ConnectionState.Closed)
{
    _SqlConnection.Open();
}
SqlCommand sqlCmd = new SqlCommand(sqlStr, _SqlConnection);
sqlCmd.CommandTimeout = 30;
sqlCmd.Parameters.AddWithValue("@ID", "MyID");
SqlDataReader myDataReader = sqlCmd.ExecuteReader();
while (myDataReader.Read())
{
    Console.WriteLine(Convert.ToString(myDataReader["UniqNo"]));
}

從Log看到的都是command timeout等錯誤訊息，將程式內的T-SQL放到SSMS上執行發現，2~5秒即可將資料撈出(已清過cache 與buffer)，檢查過執行計畫也是透過正確的索引進行查詢

但只要是透過程式進行查詢則會發生command timeout，從SSMS Dump 程式執行的指令，接著再將該T-SQL放到SSMS執行一次發現執行計畫竟然不同(下圖查詢 1)

仔細比對兩條T-SQL的差異發現僅是敘詞型別不同，資料表欄位ID的型態為VARCHAR，當@ID的型態是NVARCHAR執行計畫就不會正確使用索引進行查詢，而透過ADO給定字串型別參數@ID進到SQL預設型別為NVARCHAR，當欄位型別不匹配的時候，SQL Server 幫我們將欄位的值做一個隱性的轉換，轉換好之後才去跟我們的檢索條件去比對；

※這樣的處理需要針對這個 ＩＤ的欄位一筆資料一筆資料的處理，使得 SQL Server 就無法從直接透過索引檢索，變成去使用這個索引去做每一筆資料的轉換。

--查詢1
DECLARE @ID NVARCHAR(30);
SET @ID = 'Egan';
SELECT UniqNo,col1,col2,col3,col4
FROM  dbo.TableName
WHERE ID=@ID;

--查詢2
DECLARE @ID VARCHAR(30);
SET @ID = 'Egan';
SELECT UniqNo,col1,col2,col3,col4
FROM  dbo.TableName
WHERE ID=@ID;

此次案例的處理方式是透過明確指定參數資料型別方式處理，如下

sqlCmd.Parameters.Add("@ID", SqlDbType.VarChar);
sqlCmd.Parameters["@ID"].Value = "MyID";

為什麼 SQL Server 不是將我們檢索條件由轉換對應的欄位型別?

當一個運算子結合兩個不同資料類型的運算式時，資料類型優先順序的規則，會指定將低優先順序的資料類型，轉換為高優先順序的資料類型。　如果轉換不是支援的隱含轉換，就會傳回錯誤。

C# 內ADO預設型別參考

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DBType.Test
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection(""))
            {
                using (SqlCommand cmd = new SqlCommand("", conn))
                {
                    cmd.Parameters.AddWithValue("string", string.Format("Aerosmith"));
                    cmd.Parameters.AddWithValue("decimal", new decimal(100));
                    cmd.Parameters.AddWithValue("int", 0);
                    cmd.Parameters.AddWithValue("Datetime", new DateTime(1900, 1, 1));
                    Console.WriteLine("C# string  SqlDbType is {0}", cmd.Parameters["string"].SqlDbType);
                    Console.WriteLine("C# decimal SqlDbType is {0}", cmd.Parameters["decimal"].SqlDbType);
                    Console.WriteLine("C# int     SqlDbType is {0}", cmd.Parameters["int"].SqlDbType);
                    Console.WriteLine("C# DatetimeSqlDbType is {0}", cmd.Parameters["Datetime"].SqlDbType);
                }
            }
        }
    }
}

 

小結：

•隱含轉換資料類型優先順序是將低順位的轉成高順位的之後進行比對，隱含轉換後可能會造成執行計畫的改變，造成效能不佳

•C# 使用sqlCmd.Parameters.AddWithValue("@Parameter", “Value");要注意資料型別，可能引起隱含轉換\

如有謬誤還請版上大大提醒，萬分感謝

Reference:

•資料類型轉換 (Database Engine)

https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/data-type-conversion-database-engine#implicit-and-explicit-conversion

•資料類型優先順序

https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/data-type-precedence-transact-sql

•SQL Server Data Type Mappings

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

egan2608@gmail.com