Apex,
how many is "1 lack"? I'm sort of familiar with "lakhs", which I assue is what you mean. However, if you write in terms that everyone will understand, eg. in 100.000 records, you'll probably get better responses.
You might be interested in the
setFetchSize() method of the
Statement class. This allows you to set number of rows that will be retrieved from the database in one call. Setting it to a value of about 100 migth be a good idea. I wouldn't set it too high, though. What we want is to establish a nice flow from the database to the client in chunks that can be easily processed.
Also check the performance of your SQL query. If you read all data from a single table, not much can be done to speed things up (depending on your database), but if the data are produced using a more complicated query, there might be ample space for improvements.
Apart from this, at least three more things can be done to speed things up:
1) Retrieve columns from the resultset by their position, not by name. It saves the translation from name to column position for every access.
2) Avoid creation of objects if possible. If you're reading
ints from the database just to write them to a file, don't convert them to
Integers. Nulls have to be handled separately in this case, however it can save substantial processing time (my own experience).
3) If the processing of obtained records takes some time, think about using producer-consumer desing
pattern. You'd have one
thread to extract the data from the resultset as quick as possible and then call the next() method, while another thread would process the data. That way you could be able to crunch the data (eg. format them and write to the file) while next batch is being retrieved from the database. Some fine-tuning with respect to the fetch size and queue size will be necessary. However this is quite a lot of work, so I'd probably not choose this path unless I was really desperate.
');
