I've got 2 queries, and they are both exactly the same apart from one difference:
The first query names all of the parameters directly in the SQL string and the second query passes in a parameter using query.setLong(123L).

The first query runs in 4 seconds and the second takes 50+. They both bring back the same results and there shouldn't be an issue with object creation as it is just an aggregation query bringing back a BigDecimal.

Using Hibernate Statistics it is clear that the vast majority of the time in both instances is in the "executing 1 JDBC statements" phase. Also using P6SPY logging it is clear that both of the queries send identical SQL to the database.

So my question is, why is it taking so much time? The issue must be to do with the conversion of the long parameter somehow as it is obviously doing a lot more work for the parameterised query.
Also if i pass the parameter as an object rather than a long, it takes the same amount of time which i find odd as you would expect it to take longer, as this time it actually does have to perform some conversion.

If anyone could help that would be great. Looking for any kind of workaround that isn't "just use the plain sql query" as that's all i've managed to find online and i'm looking for a more conclusive fix.

Thanks

It sounds like an index issue. Just paste the Execution Plan for both queries.

If the 2 queries use the same parameters, then it could be that the first one generates an Execution Plan plan on Oracle or SQL Server (which use an internal Execution Plan Cache) since the DB Engine can use the inlined parameter values when generating the plan. The one with prepared statements might use an old plan which was generated for a different set of values, which have a different cardinality.

If the 2 queries are not identical, then maybe the former one scans less data than the latter. Or maybe the former uses an index, while the latter runs a full-table scan.

If you don't paste the queries and their Execution Plans, it's hard to guess what's the actual root cause of your problem.

Here are the queries - ignore the specifics as i had to change all of the names etc for privacy but they still give an idea of whats happening (the only difference is the last couple of lines of each):

Query query = getSession().createSQLQuery("select sum(this_.amount) as x_ from TABLE1 this_ "
+ "inner join TABLE2 i1_ on this_.item_id=i1_.id "
+ "inner join TABLE2 i2_ on i1_.some_id=i2_.id "
+ "inner join TABLE3 i3_ on i2_.some_id=i3_.id "
+ "inner join TABLE4 i4_ on i2_.some_id=i4_.id "
+ "inner join TABLE5 i5_ on i3_.some_id=i5_.id "
+ "inner join TABLE6 i6_ on this_.id=i6_id "
+ "inner join TABLE7 i7_ on i6_.some_id=i7_.id "
+ "WHERE i7_.someType in ('TYPE_A','TYPE_B') AND i7_.type ='TYPE0' "
+ "AND this_.type ='TYPE1' AND this_.otherType ='TYPE2' "
+ "AND this_.date < TO_DATE('01-DEC-16', 'dd-MON-YY') "
+ "AND i2_.someid=123");

^^so this one takes 4 seconds

Query query = getSession().createSQLQuery("select sum(this_.amount) as x_ from TABLE1 this_ "
+ "inner join TABLE2 i1_ on this_.item_id=i1_.id "
+ "inner join TABLE2 i2_ on i1_.some_id=i2_.id "
+ "inner join TABLE3 i3_ on i2_.some_id=i3_.id "
+ "inner join TABLE4 i4_ on i2_.some_id=i4_.id "
+ "inner join TABLE5 i5_ on i3_.some_id=i5_.id "
+ "inner join TABLE6 i6_ on this_.id=i6_id "
+ "inner join TABLE7 i7_ on i6_.some_id=i7_.id "
+ "WHERE i7_.someType in ('TYPE_A','TYPE_B') AND i7_.type ='TYPE0' "
+ "AND this_.type ='TYPE1' AND this_.otherType ='TYPE2' "
+ "AND this_.date < TO_DATE('01-DEC-16', 'dd-MON-YY') "
+ "AND i2_.someid=:someid");
query.setLong("someid", 123L);

^^and this takes 48 seconds

They both evaluate to exactly the same SQL query after Hibernate processes them and inputs the parameters.

Is there a way to see the execution plan that Hibernate is assigning to each one? When i run the query through Oracle SQL Developer it also takes 4 seconds and i can see the execution plan that it is taking, but as both of these Hibernate queries resolve to the exact same SQL then i'm not sure how to see what it is doing behind the scenes.

Thanks

This is not about Hibernate. This is about Oracle Execution Plan. hen you use bind parameters, Oracle might reuse a plan that's been built for other parameter value.

You need to take the plan from Oracle statistics. Check out this blog post for more details.