0

I have a table in PostgreSQL "items" and there I have some information like id, name, desc, config etc. It contains 1.6 million records. I need to make a query to get all result like "select id, name, description from items" What is the proper pattern for iterating over large result sets?

I used EntityListIterator: 

EntityListIterator  iterator = EntityQuery.use(delegator)
                    .select("id", "name", "description")
                    .from("items")
                    .cursorScrollInsensitive()
                    .queryIterator();



 int total =  iterator.getResultsSizeAfterPartialList();
 List<GenericValue> items = iterator.getPartialList(start+1, length);
 iterator.close();

the start here is 0 and the length is 10.

I implemented this so I can do pagination with Datatables.

The problem with this is that I have millions of records and it takes like 20 seconds to complete. What can I do to improve the performance?

bebs
  • 319
  • 3
  • 5

2 Answers2

0

If you are implementing pagination, you shouldn't load all 1.6 million records in memory at once. Use order by id in your query and id from 0 to 10, 10 to 20, etc. in the where clause. Keep a counter that denotes up till which id you have traversed.

If you really want to pull all records in memory, then just load the first few pages' records (e.g. from id=1 to id=100), return it to the client, and then use something like CompletableFuture to asynchronously retrieve the rest of the records in the background.

Another approach is to run multiple small queries in separate threads, depending on how many parallel reads your database supports, and then merge the results.

Kartik
  • 7,677
  • 4
  • 28
  • 50
  • that getPartialList does that, I get only 10 records. but the iterator consumes time. Can you give a practical example of what you proposed? – bebs Sep 13 '18 at 06:23
0

What about CopyManager? You could fetch your data as a text/csv outputstream, maybe in this way it would be faster to retrieve.

CopyManager cm = new CopyManager((BaseConnection) conn);
String sql = "COPY (SELECT id, name, description FROM items) TO STDOUT WITH DELIMITER ';'";
cm.copyOut(sql, new BufferedWriter(new FileWriter("C:/export_transaction.csv")));
m4gic
  • 1,461
  • 12
  • 19
  • I don't want to export the data, I need to list them on table to make them searchable etc – bebs Sep 15 '18 at 12:16