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.
Asked
Active
Viewed 1,666 times
2
-
Do you need to wait for all 250K records before moving to the next part of your application? If not, then you can run the ResultSet code in another thread and use a producer/consumer pattern. – Michael Markidis Jun 01 '16 at 18:06
-
@karthikeyan Govindaraj Try to take data by set basis. Take data 1000 after 1000(pagination). Then it will be fast and gives better performance.. – Karthikeyan Subramaniam Jun 01 '16 at 18:09
-
@KarthikeyanSubramaniam i can't do that. because I am taking from the join of tables. So if i take set by set, there might be chances of getting the same record again right? – gkarthiks Jun 01 '16 at 18:10
-
@MichaelMarkidis yes, I need to process that and display it on the screen. – gkarthiks Jun 01 '16 at 18:11
-
this question might help you http://stackoverflow.com/questions/2530531/reading-the-same-resultset-from-multiple-threads – Gelunox Jun 01 '16 at 18:11
-
@KarthikeyanGovindaraj Then you could still run the ResultSet code in another thread and have the main application thread update a progress bar for the user while they are waiting the 25 seconds. – Michael Markidis Jun 01 '16 at 18:15
-
2Also, is there anyway you can refine the query so that it doesn't bring back 250K records. I doubt you are showing all 250K records to the UI. – Michael Markidis Jun 01 '16 at 18:17
-
can you use map reduce(with Hadoop)? – Jeryl Cook Jun 01 '16 at 18:23
-
1**Multithreading will not help.** There is still only one network, and it is not multithreaded. You should try to reduce the size of the result set before it is *sent,* with a more selective query, or do the processing at the server with an update. – user207421 Jun 02 '16 at 00:37
1 Answers
0
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.
Karthikeyan Subramaniam
- 453
- 5
- 17
-
-
-
For Oracle also similar Please this link http://www.dba-oracle.com/t_offset_fet_first_rows_only.htm – Karthikeyan Subramaniam Jun 01 '16 at 18:34
-
mine is oracle 11g so I cannot use that aswell. Also i need to join around 19 tables to get the resultset. – gkarthiks Jun 02 '16 at 00:10
-
You can do it easily on 12c by specifyingOFFSET. 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. – Karthikeyan Subramaniam Jun 02 '16 at 00:25
-
Yes, but will oracle keep track of the rownum for a join of 19 tables? It will be a problem right. If I execute the query for first 1K records, then if i am executing the same query, while hitting the DB for the second time it should be a single table right. If it is multiple join then will it keep track of that? – gkarthiks Jun 06 '16 at 21:48
-