index not used when column is nullable and using jdbc

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
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 SendStringParametersAsUnicode=false to your
connection.
Joe Weinstein at BEA
|||Thanks for the reply. I already had set 'SendStringParametersAsUnicode=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 SendStringParametersAsUnicode=false to your
> connection.
> Joe Weinstein at BEA
>