SQL Server忽略varchar列上的索引，并从Java查询时执行tablescan [英] Sql Server ignores index on varchar column and does tablescan when queried from Java

问题描述

问题:我有一个带有一个varchar列和数百万行的SQL Server表，该表已建立索引.从SQL Server查询工具中运行查询很快速，因为它使用了索引.当我从Java JDBC PreparedStatement运行查询时，它花费了很多时间，并且调查显示SQL Server进行了表扫描.我该如何解决这个问题?

QUESTION: I have a SQL Server table with one varchar column and millions of rows, it is indexed. Running a query from within SQL Server query tool is quick as it uses the index. When I run a query from Java JDBC PreparedStatement it takes many minutes and investigation shows SQL Server does a tablescan. How do I fix this problem?

推荐答案

答案:Java导致此问题的原因是Java将查询参数的unicode字符串传递给SQLServer. SQLServer不会在varchar索引上使用它.

ANSWER: The problem results from Java passing a unicode string for the query parameter to SQLServer. SQLServer will not use this on a varchar index.

如果您希望该列保留varchar(或不能更改它)并有权访问Java代码，请将sendStringParametersAsUnicode连接字符串属性设置为"false"(默认为"true").搜索"JDBC驱动程序的MSDN国际功能"以获取更多详细信息，但也适用于CHAR，VARCHAR或LONGVARCHAR列.

If you want the column to stay varchar (or cannot change it) and have access to the Java code, set the sendStringParametersAsUnicode connection string property to "false" (it defaults to "true"). Search "MSDN International Features of the JDBC Driver" for more details but also applies with CHAR, VARCHAR or LONGVARCHAR columns.

如果您无权访问Java代码但可以更改数据库，则将数据库中的varchar列更改为nvarchar可以解决此问题，但需要增加数据存储需求.

If you don't have access to the Java code but can change the database, changing the varchar column in the database to nvarchar will fix the problem at the cost of doubling data storage requirements.

示例

jdbc:sqlserver://localhost:1433;databaseName=mydb;sendStringParametersAsUnicode=false

这篇关于SQL Server忽略varchar列上的索引，并从Java查询时执行tablescan的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！