Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance problem in using preparedStatement with MS SQL Server driver

843854Feb 7 2003 — edited Feb 8 2003
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 8 2003
Added on Feb 7 2003
1 comment
680 views