Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mapping to varchar and nvarchar in hibernate

If there are 2 columns in database, eg.

code varchar(3)
name nvarchar(50)

How to tell hibernate to pass varchar for searching by code?

In the hibernate mappings string is mapped to nvarchar and it produces queries like:

Select code, name From table where code=N'AAA'  (instead of code='AAA')

This is very bad as it causes index scan instead of index seek operation (scanning all index nodes instead of directly going to requested one)

As code is used in millions of rows as well as in several indexes and foreign keys, changing it from varchar to nvarchar will cause performance degradation (more IO operations as nvarchar uses twice more space than varchar).

Is there any way to tell hibernate to do mapping according to database type, not to Java type?

Thanks

like image 809
Niikola Avatar asked Aug 20 '09 15:08

Niikola


People also ask

Can you compare VARCHAR and nvarchar?

The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.

Can nvarchar be converted to VARCHAR?

Use Virtual View to load the data to Data Vault, and in Virtual View, use the CONVERT() function of SQL Server to extract NVARCHAR(MAX) data as VARCHAR.

Which is faster VARCHAR or nvarchar?

Use varchar unless you deal with a lot of internationalized data, then use nvarchar . Just use nvarchar for everything.

What encoding is nvarchar?

For NCHAR / NVARCHAR , the character encoding is UCS-2 or UTF-16, depending if the database collation is using the SC option. The length N in N[VAR]CHAR(N) defines a number of byte-pairs, not bytes. For UCS-2 this corresponds to a number of characters.


1 Answers

Probably you already solved this, but I had a similar problem.

I'm using jTDS JDBC driver and I solved the index scan problem by adding:

;sendStringParametersAsUnicode=false;prepareSQL=0

to the end of the jTDS connection string.

Probably it would not had solved your problem because by doing this, jTDS will only use VARCHAR (no NVARCHAR anymore).

Also, I had to disable the prepared SQL, because Hibernate is using 'like' instead of '=' when generating the queries and by using 'like' combined with a variable (SELECT ... WHERE column LIKE @var) causes an index scan (MSSQL 2000).

like image 147
Gilberto Olimpio Avatar answered Oct 26 '22 08:10

Gilberto Olimpio