當過濾欄位是 char/varchar 時,透過 SqlCommand 搭配 SqlParameter 時,大量存取會有效能考量

最近幫朋友壓測時,發現的問題。

依據 SQL Server 的 Data Type Precedence https://msdn.microsoft.com/en-us/library/ms190309.aspx

  1. user-defined data types (highest)

  2. sql_varian t

  3. xml

  4. datetimeoffset

  5. datetime2

  6. datetime

  7. smalldatetime

  8. date

  9. time

  10. float

  11. real

  12. decimal

  13. money

  14. smallmoney

  15. bigint

  16. int

  17. smallint

  18. tinyint

  19. bit

  20. ntext

  21. text

  22. image

  23. timestamp

  24. uniqueidentifier

  25. nvarchar (including nvarchar(max) )

  26. nchar

  27. varchar (including varchar(max) )

  28. char

  29. varbinary (including varbinary(max) )

  30. binary (lowest)

代表輸入的條件參數若是 unicode,而目標欄位是 char/varchr/text,將會是欄位內所有的紀錄值先轉型成 unicode,再跟輸入條件比較。

範例程式碼:

use tempdb

drop table if exists t1

create table t1(c1 int identity primary key,c2 char(6),c3 char(10),c4 varchar(50),c5 datetime2(7) default(sysdatetime()),
c6 uniqueidentifier default(newsequentialid()),c7 bigint default(0))

set nocount on

declare @i int=0
while @i<100000
begin
    insert t1(c2,c3,c4) values(right(‘000000’+convert(varchar(6),@i),6),
    right(‘0000000000’+convert(varchar(6),@i),10),rand())
    set @i+=1
end

select * from t1

create index idx on t1(c2,c3,c4)
go
create proc sp @c2 char(6),@c3 char(10),@c4 varchar(50)
as
    select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4
go

簡單利用 console 程式測試

using System;
using System.Data.SqlClient;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection cnn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;integrated security=sspi"))
            {
                SqlCommand cmd = new SqlCommand("select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4");
                cmd.Parameters.Add(new SqlParameter("c2", "000000"));
                cmd.Parameters.Add(new SqlParameter("c3", "0000000000"));
                cmd.Parameters.Add(new SqlParameter("c4", "0.0558658"));
                cmd.Connection = cnn;

                SqlCommand cmd2 = new SqlCommand("select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4");
                cmd2.Parameters.Add(new SqlParameter("c2", System.Data.SqlDbType.Char,6));
                cmd2.Parameters.Add(new SqlParameter("c3", System.Data.SqlDbType.Char, 10));
                cmd2.Parameters.Add(new SqlParameter("c4", System.Data.SqlDbType.VarChar, 50));
                cmd2.Parameters[0].Value = "000000";
                cmd2.Parameters[1].Value = "0000000000";
                cmd2.Parameters[2].Value = "0.0558658";
                cmd2.Connection = cnn;

                SqlCommand cmd3 = new SqlCommand("sp");
                cmd3.CommandType = System.Data.CommandType.StoredProcedure;
                cmd3.Parameters.Add(new SqlParameter("c2", "000000"));
                cmd3.Parameters.Add(new SqlParameter("c3", "0000000000"));
                cmd3.Parameters.Add(new SqlParameter("c4", "0.0558658"));
                cmd3.Connection = cnn;

                cnn.Open();
                long l = DateTime.Now.Ticks;
                int times = 100000;
                for(int i=0;i<times;i++)
                {
                    //exec sp_executesql N’select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4′,N’@c2 nvarchar(6),@c3 nvarchar(10),@c4 nvarchar(9)’,@c2=N’000000′,@c3=N’0000000000′,@c4=N’0.0558658′
                    SqlDataReader dr = cmd.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());

                System.Threading.Thread.Sleep(3000);

                l = DateTime.Now.Ticks;
                for (int i = 0; i <times; i++)
                {
                    //exec sp_executesql N’select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4′,N’@c2 char(6),@c3 char(10),@c4 varchar(50)’,@c2=’000000′,@c3=’0000000000′,@c4=’0.0558658′
                    SqlDataReader dr = cmd2.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());
                System.Threading.Thread.Sleep(3000);

                l = DateTime.Now.Ticks;
                for (int i = 0; i < times; i++)
                {
                    //exec sp @c2=N’000000′,@c3=N’0000000000′,@c4=N’0.0558658′
                    SqlDataReader dr = cmd3.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());
            }
        }
    }
}

 

若 .NET 未指定 type,會自動賦予 nvarchar,導致欄位為 char 的要轉成 nvarchar 再行比較,以此例而言,也就是每次比較就需要轉 10 萬筆,3 欄位的紀錄值:

image

就單次查詢時,SQL Server 的執行計畫判讀不覺得有效能差,所以彼此占比相同。

但重複執行 10 萬次後,時間上,可以看得出來,因為指定正確的欄位型態,或是 Stored Procedure 只需要輸入參數時轉對參數型態,實際語法比較時,不需逐筆轉換,所以效率較佳。

image

就總 CPU 耗用而言,因為無法平行運算,轉資料型態導致 CPU 要跑得比較久,但只會操死一顆 CPU

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: