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:

1

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).

1

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:

1

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

1

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.

10 thoughts on “Performance impact of JDBC connectionstring property “sendStringParametersAsUnicode”

Add yours

  1. An intriguing discussion is worth comment. I believe that you should
    write more on this subject, it might not be a taboo matter but generally people do not
    talk about these issues. To the next! All the best!!

    Like

  2. Magnificent web site. A lot of useful information here.
    I am sending it to several buddies ans also sharing in delicious.
    And certainly, thanks in your sweat!

    Like

  3. Great items from you, man. I’ve take into accout your stuff previous to and you’re
    just too great. I really like what you have received right here, certainly like what you are stating and the best way during which
    you say it. You are making it entertaining and you still care
    for to keep it sensible. I can’t wait to read far more from you.
    This is really a great website.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: