Performance impact of JDBC connectionstring property “sendStringParametersAsUnicode”

In this blog post, I will demonstrate the default behaviour of JDBC connectionstring property called sendStringParametersAsUnicode and its impact on database performance.

The default value for the sendStringParametersAsUnicode property is “true” which means String parameters will be sent to the database server in Unicode format so if you have not configured it in your connectionstring, it will send VARCHAR parameter value as NVARCHAR.

For example, I have not mentioned sendStringParametersAsUnicode in my JDBC connectionstring and setting below prepared statement as String from front end.

selectTestJDBCStatement.setString(1, TestJDBCReference);

Basically it will run following simple query on database side where @p1 is string type.

SELECT  BusinessEntityID ,
        PhoneNumber 
FROM    Person.PersonPhone
WHERE   PhoneNumber = @p0

Just to give you some background about Person.PersonPhone table. It has two columns BusinessEntityID as INT and PhoneNumber as varchar(25). Additionally I have created a covering index to support my query. Let’s test the application if sendStringParametersAsUnicode has not been configured.

When I ran the application, my extended event captured following query.

(@p0 NVARCHAR(4000))
SELECT  BusinessEntityID ,
        PhoneNumber 
FROM    Person.PersonPhone
WHERE   PhoneNumber = @p0

I did not expect that @p0 will appear as NVARCHAR. Let’s check the execution plan:

This doesn’t look good to me either and I have following observations:

1. Index scan
2. Parallelism (Parallel plans kill CPU so we should avoid parallel plans in case of busy OLTP).
3. Last but not least, a warning sign (we should never ignore warning sign in execution plans, thanks to Microsoft for introducing it SQL Server Execution plans) so let’s check the warning (hover your mouse over warning).

We can see, “estimated Subtree cost” of the plan is considerably high even though our query is very simple. Also if you look at “warnings”, you can understand the reason  “why SQL Server is scanning the whole index?”, it is because of a Implicit conversion from VARCHAR to NVARCHAR.

Now let’s change the JDBC connectionstring slightly and add sendStringParametersAsUnicode= False; and run the same application again. Now we will find following query running on the database end:

(@p0 VARCHAR(4000))
SELECT  BusinessEntityID ,
        PhoneNumber 
FROM    Person.PersonPhone
WHERE   PhoneNumber = @p0

Looks good as @p0 is now appearing as VARCHAR, let’s check new execution plan:

Nice!! Now it is seeking index without any warning with a serial plan. Let’s check estimated subtree cost of new plan:

It has come down to .0048 from 21.76, perfect!

We can check other performance parameters by setting Statistics IO and Time ON ( please read Performance Stats Analysis with Statistics Parser to understand the process to assess before and after stats).

	Before	After
CPU	0.855	0.001
Scan	5	1
Logical Reads	24,125	6
Elapsed Time	0.418	0.004

We can see, a small change in connectionstring at application layer has improved the performance significantly. Needless to say, only configure this property as “false” when you are sure that you don’t need to send data in unicode.

Please leave your feedback or query in comment sections and follow me @Facebook.

Advertisement