on 03-28-2014 6:15 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.