2

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Erce Tilav
  • 1,073
  • 1
  • 12
  • 20

2 Answers2

2

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thanks for your answer. Then "Spring Boot Data Query Method" List findAllByCodeStartingWith(Pageable pageable, String aCode); is generating SQL Query wrong way. – Erce Tilav Dec 14 '18 at 07:30
  • @evracle That is possible, I'll add it to my things to investigate. It might help if you file an improvement ticket with Spring Data JPA and/or Hibernate on this. – Mark Rotteveel Dec 14 '18 at 12:49
-1

you should enable forced parameter support in DB level

MS SQL Parameterization = FORCED

Oracle CURSOR_SHARING = FORCE

Damith Benaragama
  • 9
  • 2