Why would Sybase not use a functional index?

Question

I've created a functional index on a sybase table.

create index acadress_codpost_lower on acadress(LOWER(l5_codpost))

I then run a complex query that uses the index. Without the index it takes 17.086 seconds. With the index it takes 0.076 seconds.

I've run it from two different SQL clients and on both development and pre-prod Sybase servers. In all cases I see the acceleration from the index.

However when we run an identical query from Java (and I know it's identical since I've logged the generated SQL and used that directly in the SQL clients) then the performance is exactly the same as before we added the indexes.

What possible reason might there be for identical SQL queries to use the index when run from ACE and SQuirreL but not from Java?

My first thought is that maybe Sybase is caching execution plans for the Prepared Statements and not using the index. We've tried restarting the Java server several times (other services use the Sybase server so it's harder to bounce) and it has made no difference.

The other possibility is that we are using a very old version of the Sybase driver:

jConnect (TM) for JDBC(TM)/7.00(Build 26502)/P/EBF17993/JDK16/Thu Jun 3 3:09:09 2010

Is it possible that functional indexes are not supported by this version of JConnect?

Does anyone know if either of these theories might be correct, or whether there is something else I've missed?

Answer 1

I've been looking into this off and on for the past week or so and while I still do not have a definitive answer I do have a plausible theory.

I tried the suggestions from the comments and thanks to them I was able to narrow the cause down to a single change, if I have the query:

"where LOWER(aca.l5_codpost) like '"+StringEscapeUtils.escapeSql("NG179GT".toLowerCase())+"'"

Then the query uses the index and returns extremely quickly.

If on the other hand I have:

where LOWER(aca.l5_codpost) like :postcode

query.setString("postcode", "NG179GT".toLowerCase());

Then it does not use the index.

The theory is that Sybase is optimizing the query plan with no information about the contents of :postcode, so it is not using the index. It doesn't recompile the query once it does know the contents so it never uses the index.

I've tried forcing the index using (index acadress_codpost_lower) and that made no difference.

I've tried set forceplan off and set literal_autoparam off and neither made any difference.

The only thing that I can find that changes the behavior is directly embedding the option into the query plan vs having it as a parameter.

So the work around is embedding the parameter into the query string, although I'd still like to know what's actually happening and solve the problem properly.