Generated SQL is runs slow in Hibernate but fast manually

rdblyth · **Joined:** Mon Jan 11, 2010 2:42 pm **Posts:** 6

A particular query that is generated by a Criteria runs slowly in Hibernate, about 4 seconds. If I take that same query and run it by hand then it runs very fast (.15 seconds). I am using Oracle and the table contains about million records. Not really noticing slowness agains the other tables, but this table contains a lot more records than my other tables.

Does anyone have any ideas why the big difference?

sandymcp · **Joined:** Sat Mar 07, 2009 6:58 pm **Posts:** 13

Without a clue as to what this particular query might be doing? No, I don't think anyone will have an idea!

rdblyth · **Joined:** Mon Jan 11, 2010 2:42 pm **Posts:** 6

Here is the query:

select count(ACTIONS.ID) from ACTIONS A
inner join ITEMS I on I.ITEM_ID = A.ITEM_ID
inner join USERS U on U.USER_ID = A.USER_ID
where A.STATUS='New' and A.USER_ID='123'

I understand I don't need the inner joins need to figure out why my Criteria is generating the joins. Regardless If I run this by hand (a very simple query) and I run the Criteria in Hibernate I get a big difference in speed.

sandymcp · **Joined:** Sat Mar 07, 2009 6:58 pm **Posts:** 13

Do you have eager fetches configured on the joined tables?

rekk1986 · **Joined:** Wed Jan 06, 2010 2:30 am **Posts:** 5

Hi all,
I think These happens because the select query retrieves the tables and when you add "select count" it retrieves a number.
Do you have another explanations?
Regard.

rdblyth · **Joined:** Mon Jan 11, 2010 2:42 pm **Posts:** 6

I fixed the query to remove the the joins and I get the same result. I don't have eager fetching on any of the associations. Here is the new query:

select count(ACTIONS.ID) from ACTIONS A
where A.STATUS='New' and A.USER_ID='123'

rdblyth · **Joined:** Mon Jan 11, 2010 2:42 pm **Posts:** 6

A little more information that may help. If I make a copy of the table I am querying and cut the table form million rows to a couple of thousand, then the time it takes Hibernate to execute the query is comparable to running it by hand.

Also if a create a sql query in Hibernate using the sql generated by my Criteria it runs fast. This makes me wonder if there is some large overhead with converting my Criteria to SQL?

rdblyth · **Joined:** Mon Jan 11, 2010 2:42 pm **Posts:** 6

I found the cause of the problem. The USER_ID column in my database is a String and in my hibernate mapping it was a Long. The conversion of a String to Long for the million records was the cause of the slow down.

Thanks everyone for their help.