Thanks & Regards
Vijayabalakrishnan K
No more Blub for me, thank you, Vicar.
chris webster wrote:As Martin says, ask yourself if you really need to fetch 2 million records out of the database, across your network and into your application server. Can you get the final result you need via some clever SQL or PL/SQL in the database, which is designed to process large volumes of data efficiently, without moving them around your network?
Thanks & Regards
Vijayabalakrishnan K
Martin Vajsar wrote:The first thing you should do would be to think really hard how to avoid fetching millions of records. Sometimes you really need to, but in many cases it would be feasible to move the processing to the database, either using a clever SQL statement, or to a stored procedure (in Oracle, you can create Java stored procedures, so you don't even need to know PL/SQL and perhaps could reuse some of the existing code). I'm pretty sure this would decrease the response time much more significantly than arriving at the optimal fetch size setting.
Regardless, the fetch size is a JDBC driver responsibility. When you use JDBC's recordsets (as all ORM and persistence frameworks invariably do at the end), you can access only one row at a time, but - behind the scenes - the driver fetches them in batches specified by the fetch size when he runs out of records from the previous batch. I therefore don't think that increases of the number of records processed by the framework are a good indication of the fetch size in effect.
I don't think there is substantial reason to assume that the fetch size is not being honored. It is possible to verify the fetch size by activating the SQL trace capability in the Oracle database. However, you need some privileges and knowledge to be able to do so. Another possible way to guess at that would be to record the rate of arrival of new records with fetch size set to 1 and 100 (repeat several times). If there is no clear difference between the two, probably the fetch size is not in effect. Make sure that there isn't other lengthy processing that would mask the time differences in the fetch operations, though.
Thanks & Regards
Vijayabalakrishnan K
Vijay Kandaswamy wrote:But, As per the requirement I need to pull those records(2 million) through my application only.
"Leadership is nature's way of removing morons from the productive flow" - Dogbert
Articles by Winston can be found here
Vijay Kandaswamy wrote:
Thanks for the suggestion chris.
But, As per the requirement I need to pull those records(2 million) through my application only. So I am looking for the above option(fetchSize) believing that might help. We already proposed PL/SQL approach but they need the records to be pulled via application.
No more Blub for me, thank you, Vicar.
chris webster wrote:
Vijay Kandaswamy wrote:
Thanks for the suggestion chris.
But, As per the requirement I need to pull those records(2 million) through my application only. So I am looking for the above option(fetchSize) believing that might help. We already proposed PL/SQL approach but they need the records to be pulled via application.
So what is the requirement? For example, if you're doing something like creating a flat file (e.g. CSV), you could do this via PL/SQL and the UTL_FILE package - write a file straight from the database to an Oracle directory on the server, then just copy the file to wherever it needs to go. Alternatively, it's part of an ETL process, maybe using a proper ETL tool would be a better approach e.g. OWB, ODI, Informatica if you have them, or the open source Talend if you don't already have an ETL tool.
Just curious - I've seen plenty of situations where people insist they have to move vast amounts of data around, only to discover that they don't really need to!
Thanks & Regards
Vijayabalakrishnan K
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime. |