2

Currently working in the deployment of an OFBiz based ERP, we've come to the following problem: some of the code of the framework calls the resultSet.last() to know the total rows of the resultset. Using the Oracle JDBC Driver v11 and v10, it tries to cache all of the rows in the client memory, crashing the JVM because it doesn't have enough heap space.

After researching, the problem seems to be that the Oracle JDBC implements the Scrollable Cursor in the client-side, instead of in the server, by the use of a cache. Using the datadirect driver, that issue is solved, but it seems that the call to resultset.last() takes too much to complete, thus the application server aborts the transaction

is there any way to implemente scrollable cursors via jdbc in oracle without resorting to the datadirect driver?

and what is the fastest way to know the length of a given resultSet??

Thanks in advance Ismael

VolkerK
  • 95,432
  • 20
  • 163
  • 226
Ismael
  • 344
  • 1
  • 3
  • 14
  • I'll ask a tangent question first; Do you need the data and the size of the resultset at the same time, or could you just do a COUNT(*) type query to find the number of rows? Do you REALLY need the number of rows also, I've seen this too many times where a program does the very expensive operation of getting the total number of rows for pagination when it's really not needed. – Gandalf Jun 29 '09 at 21:32
  • It's need to determine the number of pages in a pagination scheme. We're trying to avoid the use of last, and counting the currentIndex using the iterator next() method checking for null. Is there a fast way to know the number of pages without using the last() method. Our workaround is to execute a COUNT query with the same conditions, and then perform the query for the data – Ismael Jun 29 '09 at 22:04
  • Does the user need to know the number of pages? Is there a valid user case where a user would want to go directly to the last page? We generally just ask for 1 more entry then needed for a page, then if that entry exists put a link for the "next" page - requery to get the next page. Assuming your data has some form of order that you could sort by. – Gandalf Jun 29 '09 at 22:58
  • I gave an example of getting the 'total rows' in a resultset and the result set itself in the same SQL query in Oracle here: http://stackoverflow.com/questions/2840/paging-sql-server-2005-results/11352#11352 – Brian Jun 30 '09 at 14:16

2 Answers2

1

"what is the fastest way to know the length of a given resultSet" The ONLY way to really know is to count them all. You want to know how many 'SMITH's are in the phone book. You count them. If it is a small result set, and quickly arrived at, it is not a problem. EG There won't be many Gandalfs in the phone book, and you probably want to get them all anyway.

If it is a large result set, you might be able to do an estimate, though that's not generally something that SQL is well-designed for.

To avoid caching the entire result set on the client, you can try

select id, count(1) over () n from junk;

Then each row will have an extra column (in this case n) with the count of rows in the result set. But it will still take the same amount of time to arrive at the count, so there's still a strong chance of a timeout.

A compromise is get the first hundred (or thousand) rows, and don't worry about the pagination beyond that.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • +1: while it would be nice to know the number of rows that a query will return, this information WILL have to be computed and therefore will have a cost. Gary's answer is probably the least expensive way to do this. – Vincent Malgrat Jun 30 '09 at 07:07
0

your proposed "workaround" with count basically doubles the work done by DB server. It must first walk through everything to count number of results and then do the same + return results. Much better is the method mentioned by Gary (count(*) over() - analytics). But even here the whole result set must be created before first output is returned to the client. So it is potentially slow a memory consuming for large outputs.

Best way in my opinion is select only the page you want on the screen (+1 to determine that next one exists) e.g. rows from 21 to 41. And have another button (usecase) to count them all in the (rare) case someone needs it.

Michal Pravda
  • 809
  • 4
  • 9