I'm using Hibernate 3.2 SP1 with SQL Server 2000 and JSQLConnect JDBC
Driver.

My underlying SQL tables all have Primary Keys of type VARCHAR(30),
and there are foreign key relationships between the tables too.

In my Hibernate mapping I am declaring <id> elements with a type
of org.hibernate.type.StringType. In my primary and foreign key
mappings I am using column sql-type="varchar(30)".

I am using pure HQL throughout - no raw native SQL queries.

My problem is that I am seeing poor query performance in SQL, and
when I turn on SQL Server profiling the reason is evident. The SQL
queries submitted are using NVARCHAR values rather than VARCHAR
i.e.

select * from Foo f where f.BAR_FK=N'....'

rather than

select * from Foo f where f.BAR_FK='....'

This causes SQL to do index scans rather than seeks because it
believes it needs to convert between VARCHAR and NVARCHAR.

What I'm not sure of is whether the appearance of the N'...' values
is due to something Hibernate is doing, or due to the operation of
the JDBC driver.

Is there anything I can do at the Hibernate level to force the use
of true VARCHAR rather than NVARCHAR string values for PK/FK
columns in the emitted SQL?

Thanks

Alan

Hi Alan,

It has been years since you have reported this but were you able to find a solution for this issue?

Thanks!

Alas no - other than dropping down to using stored procs that handle the NVARCHAR->VARCHAR conversion explicitly before executing the required query.

I meet the same problem.