CODEWITHSUNDEEP
javaoracle-databasehibernatetime
user710818
user710818

Reputation: 24248

How find the query difference between hibernate and running sql directly?

I have query like:

 delete from tableA where fk in (select id from tableA where fk='somevalue'
 and tableA.date between date1 and date2)

When I run this query from TOAD it takes near 100ms. When I run using 

                   session
                .createSQLQuery(
                        "delete ....")
                .setParameter("...")
                .setParameter("...")
                .setParameter("...")
                .executeUpdate();

It takes near 30 minutes Really query deletes 0 rows. So may be there are some hibernate/oracle issues. How I can found them? Thanks.

Upvotes: 1

Views: 580

Answers (2)

richardtz
richardtz

Reputation: 4993

In the case of such a long time, the small overhead that hibernate may produce won't be noticed.

You can probably speed up the deletion by adding indexes in the fk and date columns ( a composite one may work well in this case ).

UPDATE I'll elaborate a bit, to clarify why I suggest this. Hibernate will have to resolve mappings for the query executed (and in the case of a select statement it will have to create objects and fill them with results). In this case it doesn't look like this overhead will be important, as we are talking about 30 minutes of execution.

So the problem, probably lies somewhere else like :

  • Network isues if executing TOAD and the java process from different machines
  • Some difference between JDBC and OCI
  • A bad translation of the query, which can be checked with the show_sql property. In this case usually there's something wrong with the mappings.

As there are not being any deletions as stated in the question and even with that the query takes 30 mins, that's why I suggested the index. It is true that it may slow deletions, but again, in this case no rows are deleted, so it takes 30 minutes to execute the subquery, not to do any deletions.

This answer may not solve the issue, but I honestly don't think it deserved a downvote.

Upvotes: 1

beder
beder

Reputation: 1074

You're probably talking about the hibernate.show_sql property from Hibernate configurations (ctrl+f for "show_sql")

Setting this property to "true" will print the used SQLs to your standard output

Upvotes: 6

Related Questions