Search code examples
javasqlhibernatecriteria

Is it performative to use criteria.list () to determine the number of rows returned?

I have a Criteria object that I use to get a common list. But in a specific case, I'd like to use this same Criteria object to know only the amount of rows returned.

My question is if i use:

Integer count = criteria.list().size();

It is the same perfomatically as:

criteria.setProjection(Projections.rowCount());
Integet count = (Integer)criteria.uniqueResult()
Solution

  • Your projection will typically out-perform the list().size() method call in almost all cases. In order to understand why, we need to highlight the differences between the two approaches.

    When you ask Hibernate to perform a list().size(), what happens?

    1. We issue a select to the target table at the database level.
    2. The database has to read the data pages from disk, loading the results into memory. This consumes valuable database server resources like cpu cycles, disk i/o, and memory.
    3. The database server must stream those results to the application server. This process is influenced heavily by the result set's number of rows, width, and network connection latency.
    4. Instantiate the collection elements on the application server and add each constructed instance to the Hibernate Session's persistence context (the 1LC). This again consumes both memory and cpu cycles to instantiate and store these objects.
    5. If your collection element contains any eagerly loaded associations, then Hibernate is going to repeat steps 1 through 4 again until the object graph is loaded based on fetch modes. Obviously, this is entirely unnecessary when all you are after is a count of the collection elements.
    6. Ask the JVM to give you a collection count, minor but still wasted cpu cycles.

    When you ask Hibernate to perform a Projection.rowCount(), what happens?

    1. We issue a projection query to the target table at the database level.
    2. Rather than the database needing to read the data pages from disk, if your projectin query is just counting based on the primary key and any where conditions are indexed, the query will return quickly, regardless if we're talking a small or large table of rows.
    3. Rather than stream a result-set of multiple rows and columns back to the application server, all that gets returned is a single value, the count. This consumes hardly any network bandwidth.
    4. Since we return a single value, Hibernate has little effort to return that value. Furthermore, since its an aggregate value, we aren't going to be placing any information into the Session's persistence context (the 1LC), so no unnnecessary memory consumption here.
    5. No associations will be loaded since we didn't return entities.
    6. No need for the JVM to count anything, the JDBC result is the count.

    TLDR:

    The projection will use far less cpu, memory, and disk i/o both at the database server and application server levels and won't be as influenced by network latency given that the result is a single value unlike larger result sets might.

    While I understand people prefer not to prematurely optimize, I do think it is important that when we need something from a resource, we actually ask that resource to give us precisely what we want rather than trying to acquire the desired result indirectly. That almost always will be technical debt you'll have to come back and fix later.