Grails query forcing implicit conversion on string parameters

Question

I am running a simple grails query with a string parameter. When looking at the query that is generated in SQL Server profiler, the query forces an implicit conversion from nvarchar to varchar which causes performance issues.

Simple Grails query:

Book.findByTitle("To Catch A Mocking Bird")

Book Domain:

class Book {
    String title
    Date dateCreated
    Date lastUpdated

    static constraints = {
    }
}

SQL Table Column (as generated by Grails):

<table>
  <thead>
    <tr>
      <th>table_name</th>
      <th>column_name</th>
      <th>data_type</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>book</td>
      <td>id</td>
      <td>bigint</td>
    </tr>
    <tr>
      <td>book</td>
      <td>version</td>
      <td>bigint</td>
    </tr>
    <tr>
      <td>book</td>
      <td>date_created</td>
      <td>datetime2</td>
    </tr>
    <tr>
      <td>book</td>
      <td>last_updated</td>
      <td>datetime2</td>
    </tr>
    <tr>
      <td>book</td>
      <td>title</td>
      <td>varchar</td>
    </tr>
  </tbody>
</table>

Query in profiler:

declare @p1 int
set @p1=5
exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000)',N'/* criteria query */ select TOP(@P0) this_.id as id1_24_0_, this_.version as version2_24_0_, this_.title as title3_24_0_, this_.date_created as date_cre5_24_0_, this_.last_updated as last_upd7_24_0_, from book this_ where this_.title=@P1                ',1,N'To Catch A Mocking Bird'
select @p1

Note that the parameter is a String "To Catch A Mocking Bird" The table "title" column is type "varchar" The sp_prepexec statement takes a parameter string as nvarchar(4000). This is causing an implicit conversion which can have negative performance issues.

Since the query statement is generated by GORM/Hibernate in Grails, I don't have control of the creation of the query statement.

Is this a bug, or is there some configuration that needs to be set so that the statements takes a varchar instead of nvarchar?

Environment:

1. Grails: 3.3.8
2. Gorm: 6.1.10
3. Hibernate: 5.1.5
4. SQL Server: 12.0.2269

Answer 1

You should configure your JDBC Driver. By default, String parameters will be sent to the database server in Unicode format so if you have not configured it in your connection url, it will send VARCHAR parameter value as NVARCHAR.

Set sendStringParametersAsUnicode param to false in your datasource config. More info here.