Skip to Content
Former Member
Mar 28, 2014 at 06:15 PM

SAP JPA setMaxResults issue



I'm operating on a large table with a few million of records using SAP JPA running on AS Java 7.3.1 and Oracle 11g. While analyzing a performance issue, I came across a DB trace that led me to think that the setMaxResults() method on Query objects may not be working the way I think they were.

I execute a SELECT and apply setMaxResults(100) to the DB along with a WHERE clause, but Oracle SQL trace shows only the SELECT and WHERE clauses. I expect setMaxResult to be applied as a ROWNUM parameter at the end of the WHERE clause, so if I execute a statement like:

Query q = "SELECT ent FROM ENTLargeTable ent WHERE ent.type = 1";

List list = em.createQuery(queryString).setMaxResults(100).getResultList();

DB trace shows:

SELECT id, name, type FROM LargeTable WHERE type = 1 ORDER BY id

where I expect it to be something like:

SELECT id, name, type FROM LargeTable WHERE type = 1 and ROWNUM < 100 ORDER BY id

My list is 100 records long indeed, but I run into Oracle TMP and performance related issues probably because this is trying to scan the whole table first.

Appreciate any ideas on this.

Kind Regards,