Hibernate produce different SQL for every query

问题:

I've just tested my application under the profiler and found out that sql strings use about 30% of my memory! This is bizarre.

There are a lot of strings like this stored in app memory. This is SQL queries generated by hibernate, note the different numbers and trailing underscores:

select avatardata0_.Id as Id4305_0_,...... where avatardata0_.Id=? for update
select avatardata0_.Id as Id4347_0_,...... where avatardata0_.Id=? for update

Here is the part I can't understand. Why does hibernate have to generate different sql strings with different identifiers like "Id4305_0_" for each query? Why can't it use one query string for all identical queries? Is this some kind of trick to bypass query caching?

I would greatly appreciate if someone would describe me why it happening and how to avoid such resource wasting.

UPDATE

Ok. I found it. I was wrong assuming memory leak, It was my fault. Hibernate is working as intended.

My app created 121(!) SessionFactories in 10 threads, they produced about 2300 instances of SingleTableEntityPersisters. And each SingleTableEntityPersister generates about 15 SQL queries with different identifiers. Hibernate was forced to generate about 345.000 different SQL queries. Everything is fine, nothing weird :)

回答1:

There is a logic behind the query string that hibernate generates. Its primary aim is to get unique aliases for tables and columns names.

From your query,

select avatardata0_.Id as Id4305_0_,...... where avatardata0_.Id=?

avatardata0_ ==> avatardata is the alias of the table and 0_ is appended to indicate it is the first table in the query. So if it were the second table(or Entity) in the query it should have been shown as avatardata1_. It uses the same logic for the column aliases.

So, this way all the possible conflicts are avoided.

You are seeing theses queries because you have turns on the show_sql flag the configuration. This is intended for the debugging of queries. Once you application started working you are supposed turn it off.

Read more on the API docs here.

I am not much aware of the memory consumption part, but you repeat your tests with the above flag turned off and see if there is any improvement.

回答2:

Assuming you are using sql server, you might want to check the parameter type declaration for '?', making sure the declaration results in the same, fixed length declaration every time.

Dynamic length parameters would result in separate execution plans for each query. This could possibly comsume a lot of resources. What we see as the same procedure, get's interpreted by sql server as a different query, rendering a separate execution plan.

Thus,

exec myprocedure @p1 varchar(3)='foo' 

and

exec myprocedure @p1 varchar(6)='foobar' 

would result in different plans. Simply by the fact that the declarations of @p1, differ in size.

There is a lot to know about this behaviour. If the above applies to you, I would recommend you read up on 'parameter sniffing'.

回答3:

No... you can generate you common query inside the hibernate. The logic behind is to mapping with table and fetch the record from there. It is used common query for all the database. Please create a common query like that :

Example :

select t.Id as Id4305_0_,...... from t where t.Id=?