Using setMaxResults(1) before uniqueResult() for hibernate optimization?

Question

Is writing

session.createCriteria(Person.class) .add(Restrictions.eq("id", personId)) .setMaxResults(1) .uniqueResult();

better than writing

session.createCriteria(Person.class) .add(Restrictions.eq("id", personId)) .uniqueResult();

from an optimization point of view? Will the first query be faster?

Hulen

That depends on the underlaying database and cannot be answered generally. Use a jbdc logger or have a look into your sql log and analyse the execution plan of the resulting queries. – Soccer

Answer 1

Sometimes explicitly limiting the result set rows to the expected number may give a hint to the database to build a more optimized query execution plan.

However, in most databases querying by primary key is the most optimal filter condition anyway so any additional conditions will bring no benefit. Actually, the additional sql fragment will just increase the statement parsing time and time spent by the db optimizer discarding the redundant filter conditions.

Anallese

So, if I don't query by primary key but by something else, on a table with 1m+ rows, would/could there be a significant time save? And if there is not a time save, there is no drawback to it, so might as well do it anyway? – Hulen

@Hulen Good developers don't do things without knowing exactly what they do. If you write redundant code "just because", then you're not exactly relaying a feeling of "I'm a competent programmer". Not to mention that even if there were an improvement in this case, it would be a micro-optimization which is another telltale sign of a poor developer. You'll get better results concentrating on things that actually matter. – Catricecatrina

@Hulen In that case it all depends on the database, actual query, indexes, etc. Don't optimize prematurely. In general, confusing code may do much more harm than what is the benefit of unnoticable performance gains. If you find out that adding it significantly improves performance, then add it and document it in the comment in the code why you added it. – Anallese