Why doesn't Sybase use a functional index?

Question

I created a functional index in a sybase table.

create index acadress_codpost_lower on acadress(LOWER(l5_codpost)) 

Then I run a complex query that uses an index. Without an index, 17.086 seconds are required. With an index, it takes 0.076 seconds.

I ran it from two different SQL clients and on both Sybase servers for development and pre-use. In all cases, I see acceleration from the index.

However, when we run an identical query from Java (and I know that it is identical, since I registered the generated SQL and used it directly in SQL clients), then the performance will be the same as before adding indexes.

What is the possible reason for identical SQL queries to use the index when starting from ACE and SQuirreL, but not from Java?

My first thought is that Sybase can cache execution plans for prepared statements and not use an index. We tried several times to restart the Java server several times (other services use the Sybase server, so it is difficult for it to bounce), and this does not matter.

Another 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 one of these theories might be correct, or is there something else that I missed?

Answer 1

I studied this and continued for the past week or so, and while I still do not have a definitive answer, I have a plausible theory.

I tried the suggestions from the comments, and thanks to them I was able to narrow things down to one change, if I have a request:

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

The query then uses the index and returns very 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 an index.

The theory is that Sybase optimizes the query plan without content information :postcode , so it does not use an index. It does not recompile the query when it knows the content, therefore, it never uses the index.

I tried to get the index to use (index acadress_codpost_lower) , and that didn't make any difference.

I tried set forceplan off and set literal_autoparam off and didn't make any difference.

The only thing I can find that changes the behavior is to directly include the option in the query plan, as well as it as a parameter.

So, the work around embeds the parameter in the query string, although I would still like to know what is actually happening and solve the problem properly.