How to process 250K records in ResultSet in multithreading or any other faster way?

Question

Currently my SQL resultSet brings around 250K records and which needs to be processed. It is taking 25 secs to complete the process. I am planning to do the Multithreading on it. But couldn't split the data from the ResultSet. When googling it says CachedRowSet, but no proper example for implementing that. Please some one help me on this. Thanks in Advance.

Answer 1

You can write a query by joining more tables and also try to use this two key words

FETCH and OFFSET.
For 1st Ex : fetch = 1, offset = 1000
For 2nd Ex : fetch = 1001, offset = 1000

Please check this link https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

By fetching set after set from database You can use pagination concept It won't affects the UI at any case.

In case of Oracle : You can do it easily on 12c by specifying OFFSET.

In 12c,

SELECT val FROM table ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

To do the same on 11g and prior, you need to use ROWNUM twice, inner query andouter query respectively.

The same query in 11g,

SELECT val FROM (SELECT val, rownum AS rnum FROM (SELECT val FROM table ORDER BY val) WHERE rownum <= 8) WHERE rnum > 4;

Here OFFSET is 4.