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.
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:
- Index scan
- Parallelism (Parallel plans kill CPU so we should avoid parallel plans in case of busy OLTP).
- 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).
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.
Amazing blogpost on default behaviour of JDBC connectionstring property and its impact on database performance.. 👍
Great post, I believe website owners should larn a lot from this web blog its real user friendly.
This is the common problem so thought to share with everyone. I have faced similar issue.
Really a good tip..thanks for sharing this..this will definitely help
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!!
This website was… how do I say it? Relevant!! Finally
I’ve found something which helped me. Cheers!
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!
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.
Yes! Finally something about website.
Awesome! Its in fact amazing paragraph, I have got much clear
idea about from this post.