6

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?

Tim B
  • 40,716
  • 16
  • 83
  • 128
  • 1
    Maybe relevant: [statement cache](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1570/html/sag2/CHDHGEFE.htm) What exact version of Sybase do you have? And are the queries identical? (just a wild guess, the statement cache may be case sensitive) – ypercubeᵀᴹ Jan 28 '15 at 17:19
  • 1
    Also see these tips: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00967.1550/html/MigrationGuide/MigrationGuide14.htm – ypercubeᵀᴹ Jan 28 '15 at 17:33
  • How identical are the queries from the 'other' systems vs. the Java-based query? The prepared statement may be the problem too, are any of the values parametrized? A good test to do would be to pull the identical SQL that runs from the console, and feed it in to a regular JDBC statement from Java, and run it "as is". – rolfl Jan 28 '15 at 17:33
  • I know almost nothing about Sybase, but in SQL Server, the query optimizer [won't consider](https://technet.microsoft.com/en-us/library/ms175088(v=sql.105).aspx) indexes on computed columns and indexed views if the client has certain `SET` options that don't match the object definition. Perhaps Sybase has some similar rules for function-based indexes, and ACE is using different client options than Java? – James Lupolt Jan 28 '15 at 17:34
  • Additionally, you should include your transaction isolation level as well. This may impact the types of locking done, for example, read-stability may require a different lock strategy. – rolfl Jan 28 '15 at 17:35
  • 1
    Thanks for the suggestions, when I get back in the office tomorrow morning I'll look into them all... – Tim B Jan 28 '15 at 18:18
  • Did you try to force index? Do you use dynamic sql? How does your where clause looks like? – Robert Jan 28 '15 at 18:49

1 Answers1

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.

Tim B
  • 40,716
  • 16
  • 83
  • 128