f SQL Server varchar 與nvarchar轉換上所發生的效能問題 ~ 迪貝之家

Pages

SQL Server varchar 與nvarchar轉換上所發生的效能問題

 

當看到這個段落的時候
我的腦袋是被雷K到
原來如此...........





AP單位連同外包來找我
說是有個效能問題
他們SSMS執行某段簡單的select query
效能就OK
但是透過ORM跑出來的效能就不好
他們就覺得是不是SQL Server的bug
希望我去開call給微軟進行技術諮詢
恩.......
我當然得先看過狀況後
才決定要不要開call嘛!! 對吧~~
就跟他們約好時間
他們跑程式
我手動下指令exec sp_whoisactive來查看效能
當然有抓到東西
就是透過sp_executesql來執行dynamic sql
看到這東西... 好...
我當然就上Internet
用sp_executesql performance當關鍵字去找資料
就找到了這一篇
sp_executesql is slow with parameters
其中一篇的回應,在當時我覺得是癥結點
擷下內容:

sp_executesql is slow with parameters
因為環境是AWS SQL RDS
我也只能用他們的linux AP Server
叫起sqlcmd 的連線
就照他文章的作法,加了convert
把nvarchar轉成char
還真的解決了效能問題
人家也是用ORM阿....
還不是想辦法改程式
但當時外包居然跟我講
是framework轉出sql 語句的
所以沒辦法修改
我心裡當然就很幹阿.....
火了.......
外包ㄝ...收錢都收假的...
你到麥噹噹買個大麥克
漢堡裡頭沒肉
你會不會找麥當當理論
同理可證嘛~~
怎麼這間公司會這麼容忍外包呢??
真是他媽的多奇怪的一個現象阿~~~
不管了....反正就是這樣啦~~
不改code...不改那就是你家的事情
因為他們就認為
在地端的sql server透過SSMS測過
效能是沒問題
其實真正的問題在於SQL Server 進行資料轉換的優先順序
如微軟的文件所指出的
Data type precedence (Transact-SQL)
char data conversion
當varchar跟nvarchar做比較
系統會把varchar轉換為nvarchar
再來進行比較
因此欄位定義為varchar
ORM預設就是透過unicode來處理字串
組出來的sql 當然會讓系統
在where condition上
implicitly進行左值欄位轉換
當然不會用到索引
造成效能上的差異
那為什麼在SSMS上可以
因為SSMS跑在windows上
作業系統有既有的collation
一樣的話,就不會有任何轉換發生
但AP程式是跑在Linux 的jboss上
哪來的相同的collation
其實我看到這個章節的時候
我頭好痛....有夠難K的
一下子講codepage
一下子講codepoint
實在是太艱深了
不過至少看到現在
解了我半年多前的疑惑
#### 2020/09/22 15:15######
昨天剛好有個AP Leader偷偷來跟我說
要我私下幫個忙,看一下程式
為什麼之前跑4分鐘
現在居然要跑至少30分鐘
他說他真的沒招了
...........
我就看了他給的procedure 的source code
我個人是覺得應該就是implict conversion所造成的問題
右值帶入NVARCHAR的執行計畫
我實際測試,執行時間少說要一秒
因為SSMS右下角就秀1秒
再測右值帶入varchar
我們來看執行計畫
執行時間不到1秒
因為SSMS右下角就秀00:00:00
比對兩個計畫
明顯就少了一個CONVERSION的動作
雖然計劃裡顯示都採用PK SEEK
但這是由SSMS跑~~
而且是寫成store procedure
我的ubuntu環境是沒開通該資料庫
不然我就透過sqlcmd連進去
去跑看看那段sql語句的執行效率是否會更惡化
雖然從SSMS跑起來可能只有不到1秒的差異
但當量大的時候
累積就很可觀了
當然回饋了我的測試結果
要不要採用就看他囉~~
 
######2020/12/04###########
Pro SQL Server Internals 
這本書詮釋得更棒
我節錄下來
Another important factor that you must keep in mind is type conversion . In some cases, you can make
predicates non-SARGable by using incorrect data types. Let’s create a table with a varchar column and
populate it with some data, as shown in Listing 2-6 .
Listing 2-6. SARG predicates and data types: Test table creation
create table dbo.Data
(
VarcharKey varchar(10) not null,
Placeholder char(200)
);
create unique clustered index IDX_Data_VarcharKey
on dbo.Data(VarcharKey);
The clustered index key column is defined as varchar , even though it stores integer values. Now, let’s
run two selects, as shown in Listing 2-7 , and look at the execution plans.
Listing 2-7. SARG predicates and data types: Select with integer parameter
declare
@IntParam int = '200'
select * from dbo.Data where VarcharKey = @IntParam;
select * from dbo.Data where VarcharKey = convert(varchar(10),@IntParam);
As you can see in Figure 2-14 , in the case of the integer parameter, SQL Server scans the clustered
index, converting varchar to an integer for every row. In the second case, SQL Server converts the integer
parameter to a varchar at the beginning and utilizes a much more efficient clustered index seek operation
 
■ Tip Pay attention to the column data types in the join predicates. Implicit or explicit data type conversions
can significantly decrease the performance of the queries.
You will observe very similar behavior in the case of unicode string parameters. Let’s run the queries
shown in Listing 2-8 . Figure 2-15 shows the execution plans for the statements.
Listing 2-8. SARG predicates and data types: Select with string parameter
select * from dbo.Data where VarcharKey = '200';
select * from dbo.Data where VarcharKey = N'200'; -- unicode parameter
As you can see, a unicode string parameter is non-SARGable for varchar columns. This is a much
bigger issue than it appears to be. While you rarely write queries in this way, as shown in Listing 2-8 ,
most application development environments nowadays treat strings as unicode. As a result, SQL Server
client libraries generate unicode ( nvarchar ) parameters for string objects unless the parameter data
type is explicitly specified as varchar . This makes the predicates non-SARGable, and it can lead to major
performance hits due to unnecessary scans, even when varchar columns are indexed.
這一段終於寫出了解法
而且是針對ORM 的改寫方式
所以不要再跟我說
不知道怎麼改程式了
■ Important 
Always specify parameter data types in client applications. 
For example, in ADO.Net, use
Parameters.Add("@ParamName",SqlDbType.Varchar, <Size>).Value = stringVariable 
instead of
Parameters.Add("@ParamName").Value = stringVariable 
overload. 
Use mapping in ORM frameworks to
explicitly specify non-unicode attributes in the classes.