最近幫朋友壓測時,發現的問題。
依據 SQL Server 的 Data Type Precedence https://msdn.microsoft.com/en-us/library/ms190309.aspx
- user-defined data types (highest)
- sql_varian t
- xml
- datetimeoffset
- datetime2
- datetime
- smalldatetime
- date
- time
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar (including nvarchar(max) )
- nchar
- varchar (including varchar(max) )
- char
- varbinary (including varbinary(max) )
- 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 欄位的紀錄值:
就單次查詢時,SQL Server 的執行計畫判讀不覺得有效能差,所以彼此占比相同。
但重複執行 10 萬次後,時間上,可以看得出來,因為指定正確的欄位型態,或是 Stored Procedure 只需要輸入參數時轉對參數型態,實際語法比較時,不需逐筆轉換,所以效率較佳。
就總 CPU 耗用而言,因為無法平行運算,轉資料型態導致 CPU 要跑得比較久,但只會操死一顆 CPU