Query performance !!!

[jc7442]

I use mysql. I try to perform a query on a Person class. Person has two field id and name. Person.id is the primary key. I have 100 pesons in the DB:

(1) Folowing query takes 2.5s in hibernate

Query q = em
 .createQuery("SELECT p FROM Person p WHERE p.id = :anId");
 q.setParameter("anId", id);

(2) Writting the same query in a SQL browser takes 0.016s

(3) Writing the query using EntityManager.find takes 0.016s

em.find(Person.class, id);

Query seems not to use index when invoke through hibernate ?? 2.5s for a very simpl query, that is quite long !

Have I miss something in the configuration ?

[epbernard]

An index on a 100 entries table is of course useless and most DB query optimizer will skip it.

Besides that, 2.5 s for a query like that (esp compared to find() which actually do the exact same amount for work, same query etc) is wrong. There is something else somewhere.

[epbernard]

"epbernard" wrote:
An index on a 100 entries table is of course useless and most DB query optimizer will skip it.

Note that this is absolutly not related to hibernate. Only your DB will do such choice.

[jc7442]

Besides that, 2.5 s for a query like that (esp compared to find() which actually do the exact same amount for work, same query etc) is wrong. There is something else somewhere.

Yes, you are right. In fact query executed alone is as fast as find method. In my case, I have a session statefull (it caches other persistent objects), that invokes several other session bean stateless. In that case, query becomes very inefficient (2,5s) and find stay the same.

If I change my design to stateless bean, it is faster (about 0.2 s) than statefull but it is still slower than the same the sale work perform by find.

Unfortunately, I do not succed to reproduce that on a simple example. I will try to investigate a little more.

Any idea of what may be wrong ???

[jc7442]

I have try to investigate a little more. I get some very strange things, but unfortunately I'm not able to reproduce that on a simple testcase.

I try to load from an XML to DB a full graph of objects. To build this graph, I need to retieve some objects by query. Process can be divided into several parts that does not works on the same classes. Just changing the order of this part may change the time needed for a query to something very close to 0s to more than 0.3s.

If instead of a query I use EntityManager.find method, the order has no impact on the performance.

I just try to understand what may have such effects. Is there some cache or stuff like this that may be used by query and not by find ?