Why Jpa query bound parameters are dramatically slower than raw parameters with like clause?

Question

When I execute query by setting parameter externally, query execution time is getting dramatically slow.

StopWatch stopWatch = new StopWatch();
stopWatch.start();
Query nativeQuery = mEntityManager.createNativeQuery(
    "select first 100 * from ALL$ACC allacc0_ where allacc0_.code like ? order by allacc0_.code asc");
nativeQuery.setParameter(1, "FIMA%");
List resultList = nativeQuery.getResultList();
stopWatch.stop();
System.out.println(stopWatch.prettyPrint() + " Total row count: " + resultList.size());

StopWatch '': running time (millis) = 30868 Total row count: 4

stopWatch = new StopWatch();
stopWatch.start();
Query nativeQuery1 = mEntityManager.createNativeQuery(
    "select first 100 * from ALL$ACC allacc0_ where allacc0_.code like 'FIMA%' order by allacc0_.code asc");
List resultList1 = nativeQuery1.getResultList();
stopWatch.stop();
System.out.println(stopWatch.prettyPrint()+ " Total row count: " + resultList1.size());

StopWatch '': running time (millis) = 10 Total row count: 4

Do you know why?

spring-data-jpa 2.1.3.RELEASE
jaybird.version 3.0.5

Answer 1

The problem is that Firebird cannot optimize LIKE when a bind variable is used because it doesn't know what value you are going to use, so it has to assume the worst case and creates a plan that cannot use an index for the field.

On the other hand, when you use a literal that only ends with a %-wildcard (and doesn't contain wildcards _ or % elsewhere), Firebird can optimize to use an index. For example, when you use allacc0_.code like 'FIMA%', then Firebird will execute your query as if you used allacc0_.code starting with 'FIMA', and starting with can use an index if available.

If you want equivalent behavior with parameters, then rewrite your query to use starting with instead:

Query nativeQuery = mEntityManager.createNativeQuery("select first 100 * from ALL$ACC allacc0_ where allacc0_.code starting with ? order by allacc0_.code asc");
nativeQuery.setParameter(1, "FIMA");

This is also documented in the Firebird Language Reference for LIKE:

About LIKE and the Optimizer

[..] the LIKE predicate does not use an index. However, if the predicate takes the form of LIKE 'string%' , it will be converted to the STARTING WITH predicate, which will use an index.

So—if you need to search for the beginning of a string, it is recommended to use the STARTING WITH predicate instead of the LIKE predicate.