Hi, the following problem has been troubling me and I appreciate any help any one can provide. Thanks in advance. - Carlos
I am using MS SQL Server 2000 and has a relatively large table (200000 rows) with a column (called OrderState) that is a Varchar type. An index was created on this column to speed up searches. My application is written in Java and it uses PreparedStatement to select records from this table. The performance was good until I recently switched to use mssqlserver JDBC driver provided by Microsoft (I was using JDBC-ODBC driver before). With the new mssqlserver driver, every string parameter in the prepared statement is converted to unicode (NVARCHAR), as shown below:
exec sp_executesql N'SELECT OrderId from OrderInformation
where OrderState = @P1', N'@P1 nvarchar(4000) ', N'CN_TS'
The problem with this is that, in searching for an index value that will match N'CN_TS', SQL Server will perform an index scan (instead of an index seek) and convert every index value to NVARCHAR in order to compare it to N'CN_TS'. As a result, the search becomes very slow.
This did not happen when I was using the JDBC-ODBC driver, which converts the same prepared statement to:
exec sp_prepexec @P1 output, N'@P1 char(8000)', N'SELECT order_id from order_information
where order_state = @P1', 'CN_TS'
In this case, SQL Server simply performs an index seek, which is fast.
One way to solve the problem is to change the type of OrderState to NVARCHAR (from VARCHAR), but I am hoping to have a better solution.
Any ideas?