SQL Server JDBC - index not used when column is nullable and using jdbc

Author

index not used when column is nullable and using jdbc

Palaniappan N

2005-07-14, 7:23 am

I have a table with a few columns, and one of the non-PK columns is indexed
and defined as nullable. Most of my selects happen on this column, but I
notice that as the no. of rows in this table increase, the time for selects
keeps going up. This happens while accessing this table using jdbc from a
java program.

I guess this is due to the index not being used by the server (query
optimizer?). When I look at the query execution plan in Query Analyzer, it
shows that the index will be used. What's going on? Is this a quirk of using
a jdbc driver?

thanks & regards,
Palani

Joe Weinstein

2005-07-14, 11:23 am

Palaniappan N wrote:

> I have a table with a few columns, and one of the non-PK columns is indexed
> and defined as nullable. Most of my selects happen on this column, but I
> notice that as the no. of rows in this table increase, the time for selects
> keeps going up. This happens while accessing this table using jdbc from a
> java program.
>
> I guess this is due to the index not being used by the server (query
> optimizer?). When I look at the query execution plan in Query Analyzer, it
> shows that the index will be used. What's going on? Is this a quirk of using
> a jdbc driver?
>
> thanks & regards,
> Palani

It *probably* has nothing to do with the driver. You can verify that by
timing the query from a non-jdbc application. The only possibility is
if the column is a varchar and you are parameterizing the search value.
Try timing a select where the search value is specified in the SQL, like
"select * from mytable where mycol = 'myval'". If that's much faster,
try adding the property SendStringParameters
AsUnicode=false to your
connection.
Joe Weinstein at BEA

Palaniappan N

2005-07-15, 7:23 am

Thanks for the reply. I already had set 'SendStringParameter
sAsUnicode=false'
in the connection string.

I found out that on installing SP3a on SqlServer 2000, the issue disappeared.

regards,
Palani

"Joe Weinstein" wrote:

>
>
> Palaniappan N wrote:
>
>
> It *probably* has nothing to do with the driver. You can verify that by
> timing the query from a non-jdbc application. The only possibility is
> if the column is a varchar and you are parameterizing the search value.
> Try timing a select where the search value is specified in the SQL, like
> "select * from mytable where mycol = 'myval'". If that's much faster,
> try adding the property SendStringParameters
AsUnicode=false to your
> connection.
> Joe Weinstein at BEA
>
>

Sponsored Links