Hi,

I have the following query.

This is the generated SQls by our application.

Prepared|Query|SELECT COLLECTION0_.ID AS [color=red]ID232_0_, [/color]COLLECTION0_.TOTALDUE AS TOTALDUE232_0_, COLLECTION0_.MINIMUMDUE AS MINIMUMDUE232_0_, COLLECTION0_.PASTDUE AS PASTDUE232_0_, COLLECTION0_.OVERLIMIT AS OVERLIMIT232_0_, COLLECTION0_.LASTPAYMENTAMT AS LASTPAYM6_232_0_, COLLECTION0_.LASTPAYMENTDATE AS LASTPAYM7_232_0_, COLLECTION0_.CREDITLIMIT AS CREDITLI8_232_0_, COLLECTION0_.CURRENTBALANCE AS CURRENTB9_232_0_, COLLECTION0_.PAYMENTDUEDATE AS PAYMENT10_232_0_, COLLECTION0_.CYCLEDATE AS CYCLEDATE232_0_, COLLECTION0_.CYCLESPASTDUE AS CYCLESP12_232_0_, COLLECTION0_.DELINQUENCYHISTORY AS DELINQU13_232_0_, COLLECTION0_.SEGMENTCD AS SEGMENTCD232_0_, COLLECTION0_.ACCOUNTID AS ACCOUNTID232_0_ FROM V_COLLECTIONSCASE COLLECTION0_ WHERE COLLECTION0_.ID = ?

This is the same query after a restart:


Prepared|Query|SELECT COLLECTION0_.ID AS [color=red]ID201_0_, [/color]COLLECTION0_.TOTALDUE AS TOTALDUE201_0_, COLLECTION0_.MINIMUMDUE AS MINIMUMDUE201_0_, COLLECTION0_.PASTDUE AS PASTDUE201_0_, COLLECTION0_.OVERLIMIT AS OVERLIMIT201_0_, COLLECTION0_.LASTPAYMENTAMT AS LASTPAYM6_201_0_, COLLECTION0_.LASTPAYMENTDATE AS LASTPAYM7_201_0_, COLLECTION0_.CREDITLIMIT AS CREDITLI8_201_0_, COLLECTION0_.CURRENTBALANCE AS CURRENTB9_201_0_, COLLECTION0_.PAYMENTDUEDATE AS PAYMENT10_201_0_, COLLECTION0_.CYCLEDATE AS CYCLEDATE201_0_, COLLECTION0_.CYCLESPASTDUE AS CYCLESP12_201_0_, COLLECTION0_.DELINQUENCYHISTORY AS DELINQU13_201_0_, COLLECTION0_.SEGMENTCD AS SEGMENTCD201_0_, COLLECTION0_.ACCOUNTID AS ACCOUNTID201_0_ FROM V_COLLECTIONSCASE COLLECTION0_ WHERE COLLECTION0_.ID = ?

The highlighted ID changes (from ID201_ to ID232_) everytime causing the optimizer like parsing and plan to execute everytime which is performance hit. This query is used many a times.

Is there a way to avoid these IDs or make the SQL consistent throughout?

I am using Hibernate 3.2.5 with Websphere 6.1 / Oracle 10g.

I have searched the FAQ, Docs and forum posts to the best of my knowledge but could not find the exact problem reported. My apologies if this was already dealt with or not really a product bug but with usage.

Thanks for your help.

Before I did anything to work around this, I'd want to be really sure that this is causing a performance problem.

Anyway, at the cost of maintainability, you can write a stored procedure to execute the query, then use a <loader> element in your mapping document to reference a query, then use a <sql-query> element to define the stored procedure call. See http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql-namedqueries.html for how to define the call to the stored procedure, and http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql-load.html for how to get Hibernate to use your stored procedure to load your class.

The trick is that you'd have to use a stored procedure as the query and not just a SQL select statement, because Hibernate will use column aliases in the select statement, but not when calling the stored proc.

Hope this helps,
Oscar

Thanks Oscar.

It's a good suggestion but sadly we do have lot of SQLs like this and creating procedure for all of them is not viable.

I understnad Hibernate generates column alias but is there a way to force hibernate not to do so? The issue is the random number added to the ID and that is not consistent across runs. It creates a new plan to be executed every time at the back end.

Regards
Ponni

This sounds like the same problem as I described in this thread:

http://forum.hibernate.org/viewtopic.php?t=991914

It definitely causes a performance problem. The database parses the SQL text and comes up with a hash value to see if that SQL has been submitted previously and if the access plan is still stored in memory. If not, then the database must recreate the access plan, which takes time and also uses more memory for the SQL cached plan (because there are now more SQL statements that are unique).

I reported a similar problem on DB2 in this thread:
http://forum.hibernate.org/viewtopic.php?t=991914

In our case, each application server creates its own column alias, so if I have 50 application servers, then I need 50 times as much memory to store the same number of cached SQL access plans (or for a given amount of memory, I will have fewer cache hits and the SQL access plan will need to determined by the database all over again).

We need Hibernate to generate the same alias on each application server, or not even use a column alias (is that an option?).

I found this problem reported in Jira with an interesting discussion. I would appreciate comments as to whether the proposed solution solves the problem.

http://opensource.atlassian.com/project ... e/HHH-2448

OK, I see the issue now - I wasn't considering the issue of a large cluster of app servers all hitting the same database instance. Anyway, I put together a patch (against 3.3.1) which makes the aliases consistent, at least for my (admittedly not exhaustive) tests. I would expect the patch to apply fairly cleanly to 3.2.5, but didn't try it.

The patch is attached to the JIRA issue mentioned earlier in this thread.

Excellent, this is good news. I will try to test this in 3.2.5 and share with you the results at the earliest.

Again thanks for all the posts.

Cheers
Ponni