I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.
In case the user supplies very unselective arguments, the query takes a lot of time to complete. In case the query would return more than 1000 rows, the user should be supplied with an error message.
The query does not have an order by clause, but just joins three (large) tables.
The idea now was to use the query hint FIRST_ROWS(1000) to speed up. Tests in SQL developer showed the cost metric is considerably reduced (factor 10).
The query is executed through the Entitymanager using a native query.
However, the behavior within the application is as follows: duration of OraclePreparedStatement.executeQuery is reduced by 60%. However, now a lot more of time is spent in EJBQueryImpl.getResultList which before took less than 100ms creating a high performance degradation compared with leaving out the query hint.
Code is essentially:
Query query = em.createNativeQuery(sqlQuery); // sqlQuery has query hint "FIRST_ROWS(" + (maxResults + 1) + ")"
query.setMaxResults(maxResults + 1);
List<Object[]> resultList = query.getResultList();
if (maxResults != 0 && resultList.size() > maxResults) {
resultSetTruncated = true;
}
Query is:
SELECT ... FROM
T1
LEFT OUTER JOIN T2 on T1.pk1 = T2.pk1
AND T1.pk2 = T2.pk2
AND T1.colx = T2.pk3
LEFT OUTER JOIN
T3
ON T2.pk1 = T3.pk1
AND T2.coly = T3.pk1
WHERE
T1.pk1 IN ('081111')
AND EXISTS (
SELECT 1 FROM T4 WHERE
T1.PK1 = T4.PK1
AND T2.PL2 = T4.PK2
AND T4.KIND = 'N'
AND T4.NORMALIZED LIKE 'DA%'
)
Table T3 is very large and the specified conditions in the case of interest are not very selective.
