Default parameterization of varchar to nvarchar in sql driver

We came across a strange case where there were high logical reads for some queries coming from Spring batch. This was a query executed from Spring batch's  SimpleJobRepository where it tries to create JobInstance using the jobInstanceDao

This is the query which we have no control over.

When it goes to db, the type is nvarchar and not varchar

DECLARE @P0 nvarchar(4000) = 'someJobTable', @P1 nvarchar(4000) = 'someJobKey'
SELECT JOB_INSTANCE_ID, JOB_NAME from SOME_JOB_INSTANCE where JOB_NAME =  @P0  and JOB_KEY =  @P1

This has ~1900 logical reads

While the same query with type varchar took only 4 logical reads

DECLARE @P0 varchar(100) = 'someJobTable', @P1 varchar(32) = 'someJobKey'
SELECT JOB_INSTANCE_ID, JOB_NAME from SOME_JOB_INSTANCE where JOB_NAME =  @P0  and JOB_KEY =  @P1

On digging further we analyzed that the call from
org.springframework.jdbc.core.JdbcTemplate.query(PreparedStatementCreator, PreparedStatementSetter, ResultSetExtractor<T>)
to
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(int, Object, int, int, int)
where the connection.getUseUnicode() is coming as true and hence the type nvarchar

Note that this causes high logical reads even for an indexed column. nvarchar are double-byte and even those indexed varchar column gets converted to nvarchar for comparison since the query parameter in the where clause is coming over as nvarchar.

To suppress this behaviour, you need to set sendStringParametersAsUnicode to false in the connection properties of the datasource used by the spring batch. You can choose to set this property in all of the datasources used in your application. But that depends on the usecase. If you are supporting internationalization, then you might have to analyze where to switch this property on/off.

<batch:job-repository id="jobRepository"
table-prefix="SOME"
data-source="someDataSource" isolation-level-for-create="READ_UNCOMMITTED"
transaction-manager="someTransactionManager" />

<bean id="someDataSource" class="org.apache.tomcat.jdbc.pool.DataSource">
<property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
<property name="url" value="jdbc:jtds:sqlserver://someserver/somedatabase" />
        <property name="connectionProperties" value="sendStringParametersAsUnicode=false;"/>
</bean>

Reference :
http://www.digitalsanctuary.com/tech-blog/java/mssql-jtds-nvarchar-and-slow-indexes.html
https://stackoverflow.com/questions/4717520/sql-server-uses-high-cpu-when-searching-inside-nvarchar-strings

https://blogs.msdn.microsoft.com/sqlcat/2010/04/05/character-data-type-conversion-when-using-sql-server-jdbc-drivers/