So recently while talking at SQL Server Saturday Chicago, I had a discussion with a DBA who was having huge performance issues with his database. The problem? All of the queries were being sent to the database as NVARCHAR and not VARCHAR. What is the issue with this may you ask? Well every single query was doing an index scan instead of an index seek. They were in the middle of a huge project to change the data types from VARCHAR to NVARCHAR in order to remedy this situation. The application tier was coded in Java. I shared with him the following story.
Many years ago I worked in a shop that did a lot of in-house development. The database of course was SQL Server, but the application layer was coded in Java. We also used an object model mapper software called Hibernate. This would enable us to define all of the connection strings to a database in a common way, it also let us defined objects that would always be referenced in the same way. One of the features that we really liked about this product was that if we decided to redesign parts of the database layout, changes to the application layer would be a lot more straightforward and simple to make.
Everything started off great, but after awhile I noticed that we were pushing some really heavy loads. Performance was decreasing even though we had made a ton of performance improvements. I started to dig through my trace files (this was SQL 2000) and found this weird thing. The queries looked fine but everything was being sent a NVARCHAR. If I ran the queries directly with the values, I got an optimal query plan. If ran the queries as they came across with the variables defined as NVARCHAR, they scanned all of the indexes.
I did some digging around and I found this wonderful post where someone was complaining, “This is not a feature it is a bug!” I wish I could find that post. Well it turns out that the default behavior of the Java driver is to send the values in Unicode. The problem is that most of use Non-Unicode or ASCII, so we have type conversion issues. The fix? Did it require a lot of code changes? No you just have to flip a bit on Java driver. An example is listed below of how to change the driver to not send Unicode.
”jdbc:sqlserver://hostname\Instancename;DatabaseName=MyDatabase;sendStringParametersAsUnicode=false“Is this the first time I have seen this issue since then? No I actually have seen it at almost every place I have worked since then. I also had another discussion today with someone in Richmond seeing the same issue. I have seen this issue in both in-house built applications and 3rd party applications. It is commonly missed. I have worked with multiple software vendors to get this issue fixed. They were happy to make the change because it meant less customers calling in complaining about performance issues. So if you have Java, look for this in your environment. The change could have drastic performance benefits. Happy Coding!
Reference