22

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 :)

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
Andrey Frolov
  • 1,534
  • 10
  • 19
  • 1
    It might be helpful if you could provide a simple mapping configuration, test code and simple table DDL that reproduces this situation. In my experience, I've never seen Hibernate create such high column alias index counts. – Go Dan Feb 27 '12 at 14:02
  • Good point. I will try tomorrow. :) – Andrey Frolov Feb 27 '12 at 14:14
  • @AndrewFrolov: Hope my answer fits the question better now, with your updates.In any case glad you found and fixed the issue! – ManuPK Mar 06 '12 at 14:16
  • 2
    You may want to answer your own question with the answer you found, and accept it. You could also be very helpful, and change the question text / title / tags to reflect what the issue really was, so others can find it. – David Manheim Jun 14 '12 at 15:15
  • @AndrewFrolov - Hey I am encountering the same issue. I see lot of Hibernate similar queries with different aliases being executed and is resulting in performance issues. How did you rectify this issue? What changes did you make (turn sql_logging off, it is off in my case) or any other changes? – aces. Aug 29 '12 at 18:31
  • 1
    I just reduced the number of SessionFactories I create. – Andrey Frolov Sep 03 '12 at 11:22
  • nothing weird, though this behavior can result in poor performance in Oracle, see http://stackoverflow.com/a/15257317/32453 – rogerdpack Mar 06 '13 at 20:28
  • How did you reduce the number of SessionFactories? – Ascalonian Jul 17 '14 at 15:42

3 Answers3

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.

ManuPK
  • 11,623
  • 10
  • 57
  • 76
  • 1
    show_sql is off. I am not talking about log, I am talking about memory. I see this queries in profiler, in app memory, probably in internal hibernate cache. I understand why hibernate create an alias for each column in a resulting set. I don't understand why hibernate can't reuse generated statement's for next query. – Andrey Frolov Feb 27 '12 at 13:43
  • Got that. I Have answered about the **why names long** part? Even I am not sure about the solution! – ManuPK Feb 27 '12 at 13:51
  • Do you have any documentation where it explains *why* they do this? It can cause performance problems at times... http://stackoverflow.com/a/15257317/32453 – rogerdpack Mar 06 '13 at 20:29
  • Hello @ManuPK, is there a way to turn off the aliases that `Hibernate` generate? I understand the reason you mention above, but it's quite an eyesore when trying to troubleshoot the queries. – jumping_monkey Dec 03 '19 at 07:31
1

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'.

Mike Mackintosh
  • 13,917
  • 6
  • 60
  • 87
souplex
  • 981
  • 6
  • 16
-1

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=?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pushpendra
  • 548
  • 3
  • 10