Performance drop in 5.3.11 when compared to 5.3.10

Hibernate ORM
1

Hi,
in our project we’ve done upgrade of JBoss which comes with 5.3.11 version of Hibernate and we are observing huge drop in query performance. Fast version takes split of a second and the slow runs 7 seconds for example.
Once I replaced new hibernate libraries with previous version 5.3.10 it was fast again. We log SQL before it is sent to database as “criteria.toString()” and these are identical for both versions. But when I catch running query directly in database, there is different code.

DYNAMIC_SQL dyn489: create proc dyn489 as
select top 3000 'X' as DUMMY_FOR_TOP_CLAUSE
    , table1.column1 as y1_
    , table1.column2 as y2_
    , table1.column3 as y3_
    , table1.column4 as y4_
    , table1.column5 as y5_
    , table1.column6 as y6_
    , table2.column7 as y7_
    , 'A' || table1.column8 as column8
    , 'B' || table1.column9 as column9
    , 'C' || table1.column10 as column10
    , table1.column11 as y11_
    , table1.column12 as y12_
    , table1.column13 as y13_
    , table4.column14 as y14_
    , table3.column15 as y15_
from TABLE1 table1
    left outer join TABLE2 table2 on table1.column6 = table2.column19 inner
    join TABLE3 table3 on table1.column3 = table3.column3 and table1.column16 = table3.column16
    left outer join TABLE4 table4 on table1.column16 = table4.column16
where (table1.column12 <> ? and table1.column1 is not null and table1.column1 <> ? and (table1.column10 is null or (table1.column10 <> ? and table1.column10 <> ?)) and table1.column6 is not null and table1.column6 <> ? and (table2.column17 is null or table2.column17 = ?) and table2.column7 = ? and table1.column18 <> ?)
order by table1.column16 asc, table1.column3 desc

So there is some dynamic SQL code and the core of the query differs in parameters. Slow version (above) has ? as parameters, where the fast version has concrete values instead of these question marks.

Database where it runs is Sybase ASE 15.7. It seems like the DB was unable to do all necessary optimizations, runs on wrong indexes or something like that.

Is this a bug in 5.3.11 version or is it misuse of the Hibernate? I need to point out that our code is rather old and using some already deprecated calls.

Thanks for any ideas.
/Petr

2

Update:

Root cause has been found and it has nothing to do with version of the Hibernate but with parameters handling. By default all literals are sent to DB as parameters, thus preventing database to create optimal query plan. On the other hand this approach should save time for query compilation. If the query compilation time is not an issue you can inline all literals using setting in persistence.xml

<property name="hibernate.criteria.literal_handling_mode" value="inline"/>

Some materials I´ve found along the way to study:
https://use-the-index-luke.com/sql/where-clause/bind-parameters

https://bertwagner.com/2017/08/08/why-parameter-sniffing-isnt-always-a-bad-thing-but-usually-is/