SAP JPA setMaxResults issue

[Former Member]

Hello,

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,

Gökhan

[Vlado · Accepted solution]

Hi Gökhan,

As you say the "list is 100 records long indeed", it behaves correctly as defined in the JPA spec - though I agree there is most likely potential for optimization. The best approach would be to open an SAP support ticket.

Cheers,

--Vlado

[Former Member · Accepted solution]

Thanks Vlado, this seems like the only way to figure out why.

Kind Regards