SQL Server JDBC – Set sendStringParametersAsUnicode to false
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to explain why you should always disable the sendStringParametersAsUnicode default JDBC Driver setting when using SQL Server.
Database table
Let’s assume we have the following database table:
The PostID column is the Primary Key, and the Title column is of the VARCHAR type and has a secondary index as well:
CREATE INDEX IDX_Post_Title ON Post (Title)
The Post table contains the following records:
| PostID | Title | |--------|---------------------------------------------| | 1 | High-Performance Java Persistence, part 1 | | 2 | High-Performance Java Persistence, part 2 | | 3 | High-Performance Java Persistence, part 3 | | 4 | High-Performance Java Persistence, part 4 | | .. | .. | | 249 | High-Performance Java Persistence, part 249 | | 250 | High-Performance Java Persistence, part 250 |
As you can see, the Title column is highly selective since every record has a different title value.
Unexpected CONVERT_IMPLICIT and Clustered Index Scan
When finding a Post row by its associated Title column value, we expect an Index Seek operation against the IDX_Post_Title index, but this is not what we get when using the default SQL Server JDBC settings.
For instance, if we enable the runtime query statistics to retrieve the associated execution plan of the SQL query that filters by the Title column:
executeStatement(entityManager, "SET STATISTICS IO, TIME, PROFILE ON");
try (PreparedStatement statement = connection.prepareStatement("""
SELECT PostId, Title
FROM Post
WHERE Title = ?
"""
)) {
statement.setString(1, title);
if (statement.execute() && statement.getMoreResults()) {
LOGGER.info("Execution plan: {}{}",
System.lineSeparator(),
resultSetToString(statement.getResultSet())
);
}
}
We get the following SQL execution plan:
|StmtText | |----------------------------------------------------------------------------------------------------| |SELECT PostId, Title FROM Post WHERE Title = @P0 | | |--Clustered Index Scan(OBJECT:([high_performance_sql].[dbo].[Post].[PK__Post__AA12603828AEBF55]),| | WHERE:(CONVERT_IMPLICIT(nvarchar(255),[high_performance_sql].[dbo].[Post].[Title],0)=[@P0])) |
The Clustered Index Scan operation tells us that SQL Server has used the PostId Clustered Index to scan the leaf pages in search of the Title value we provided.
The reason why the IDX_Post_Title index was not used is because of the implicit conversion that was done between the provided NVARCHAR value and the VARCHAR value of the Title column.
Even if we provided the Title bind parameter value as a VARCHAR using the setString method:
statement.setString(1, title);
The SQL Server JDBC Driver behaved as if we used setNString method instead.
SQL Server JDBC sendStringParametersAsUnicode configuration
By default, SQL Server sends all String parameter values as NVARCHAR since the sendStringParametersAsUnicode configuration is set to true.
So, if we set the sendStringParametersAsUnicode configuration value to false
jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=high_performance_sql;sendStringParametersAsUnicode=false;
And, rerun the previous SQL query, we will get the following execution plan:
|StmtText | |--------------------------------------------------------------------------------| |SELECT PostId, Title FROM Post WHERE Title = @P0 | | |--Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[IDX_Post_Title]), | | SEEK:([high_performance_sql].[dbo].[Post].[Title]=[@P0]) ORDERED FORWARD)|
That’s exactly what we were expecting from the start. There’s an Index Seek on the IDX_Post_Title index, and there’s no implicit conversion happening anymore.
Handing Unicode characters
Now, even if you disable the sendStringParametersAsUnicode setting, you can still persist Unicode data in NHAR, NVARCHAR or NLONGVARCHAR column.
So, if the Title column is of the NVARCHAR type:
CREATE TABLE Post (
PostID BIGINT NOT NULL,
Title NVARCHAR(255),
PRIMARY KEY (PostID)
)
We can set the Title column using the setNString PreparedStatement method:
try (PreparedStatement statement = connection.prepareStatement("""
INSERT INTO Post (Title, PostID)
VALUES (?, ?)
"""
)) {
statement.setNString(1, "România");
statement.setLong(2, 1L);
assertEquals(1, statement.executeUpdate());
}
And, we can read the Title column using the getNString ResultSet method:
try (PreparedStatement statement = connection.prepareStatement("""
SELECT Title, PostId
FROM Post
WHERE Title = ?
"""
)) {
statement.setNString(1, "România");
try(ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
assertEquals("România", resultSet.getNString(1));
assertEquals(1L, resultSet.getLong(2));
}
}
}
If you’re using JPA and Hibernate, the NVARCHAR column needs to be annotated with the @Nationalized Hibernate annotation to instruct Hibernate that the underlying String attribute needs to be handled by the StringNVarcharType, as opposed to the default StringType:
@Entity(name = "Post")
public class Post {
@Id
@Column(name = "PostID")
private Long id;
@Column(name = "Title")
@Nationalized
private String title;
}
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
Conclusion
If you’re using SQL Server with Java, it’s a good idea to set the sendStringParametersAsUnicode configuration property value to false, as otherwise, you can face significant performance issues associated with CHAR, VARCHAR or LONGVARCHAR indexes.
In fact, even the SQL Server JDBC Driver performance tuning page recommends disabling this property, which is set to true for backward compatibility reasons.
Thank you for inspiring article, but are you sure about your results? I tried the scenario you described and SQL server have chosen index seek operation even in case of sendStringParametersAsUnicode=true.
May be it is dependent of SQL server version. I used MSSQL 2017.
Regards
Pavel Rund
You’re welcome.
This test provides the proof.
Here are the results:
So, you can run the test for yourself and see that it works as explained in the article.