Recently, I have monitored a slow query like this:
select delete_flag,delete_timefrom D_OrderInfo WHERE ( OrderId is not null and OrderId = N'xxxx')
There is an index of OrderId on the D_OrderInfo table, but the OrderId field is of type Varchar.
Since the development framework MyBatis automatically generates the Where condition and does not specify the parameter type, the parameter of the string type will automatically become NVARCHAR (4000) in SQLServer. What's tricky is that it's fine if you don't specify the parameter type, but you also automatically add a non-SARG condition like OrderId Is NOT NULL, and the execution plan becomes like this:
---------------------------------------------------------------------------------------------
If there is no OrderId IS NOT NULL condition, the execution plan will be like this:
Since the parameter type Nvarchar has higher priority than the index field type varchar, it cannot be converted directly, but the SQLServer optimizer eventually converts it into a range value, and the final equal sign query becomes similar to a small range query.
You can see from the detailed information of Index Seek:
------------------------------------------------------------------------
If the parameter types match, the execution plan will be as expected (although not included, there is still a Key Lookup):
Of course, the way I finally hoped to write is as follows:
select delete_flag,delete_timefrom D_OrderInfo WHERE OrderId = 'xxxx'
The execution plan will of course be like this:
But, I just don’t know what the development master can change to in the end...
The solution of the master of development: Configure the connection string:
sendStringParametersAsUnicode=false
postscript:
By default, character data in Java is processed as Unicode; Java String objects represent Unicode character data. In JDBC drivers, the only things that can not obey this rule are the ASCII stream getter and setter methods, which are special cases because the byte streams used by these methods carry the implicit assumption of a single known code page (ASCII).
Additionally, the JDBC driver provides the sendStringParametersAsUnicode connection string property. This property can be used to specify predefined parameters for character data sent as ASCII instead of Unicode.
As a performance enhancement, the String parameter can be passed to SQL Server in a non-Unicode format by setting the sendStringParametersAsUnicode connection string property. The default setting of sendStringParametersAsUnicode is "true", which means that the String parameter will be sent as Unicode.
If sendStringParametersAsUnicode is set to "false", all String parameters on the connection are sent to the server using the database default collation.
refer to:
http://d.hatena.ne.jp/gnarl/20110706/1309945379
https://technet.microsoft.com/zh-cn/library/ms378857(SQL.90).aspx
https://technet.microsoft.com/zh-cn/library/ms378988(v=sql.90).aspx