Best Script Design
I have a requirement where I have to select about 60 million plus records from the database. Once I have all the records in the ResultSet, then I need to shape some columns according to the client's requirements (date format and number format), and then I have to write all the records to a file (secondary memory).
- I am currently fetching records on a daily basis (7 fetches within 7 days) from the DB and putting them into a HashMap. Reading from HashMap and shaping some columns and finally writing to file (separate file for 7 days).
-
Finally, I merge all 7 files into one file.
-
But this whole process takes 6 hours. To improve this process, I created 7 streams over 7 days and all streams write separate files.
-
Finally, I merge all 7 files into one file. This process takes 2 hours. But my program is going to OutOfMemory after 1 hour and so on.
Please suggest a better design for this scenario, should I use some kind of caching mechanism, if so, which one and how?
Note. The client doesn't want to change anything in the database, like creating indexes or stored procedures, they don't want to touch the database. Thanks in advance.
a source to share
Do you need to have all records in memory to format them? You can try and pass records through process and file permissions. If you have the ability to even split the query, you can start processing the results before you retrieve them yet.
Depending on your database backend, they might have tools to do this, like SSIS for Sql Server 2005+.
Edit
I am a .net developer so let me suggest what I will do in .net and hopefully you can convert to comparable technologies from the Java side.
ADO.Net has a DataReader which is a read-only, read-only (Firehose) cursor of a result set. It returns data as the request progresses. It is very important. Essentially my logic is:
IDataReader reader=GetTheDataReader(dayOfWeek);
while (reader.Read())
{
file.Write(formatRow(reader));
}
Since this is doing so while we are returning rows that you are not going to block on network access, which I assume is a huge bottleneck for you. The key point here is that we do not store it in memory for a long time, as we are looping the reader, it will discard the results, and the file will write the string to disk.
a source to share
I think Josh is suggesting the following:
You have loops where you loop through all of the resulting records of your request (just using pseudocode here):
while (rec = getNextRec() )
{
put in hash ...
}
for each rec in (hash)
{
format and save back in hash ...
}
for each rec in (hash)
{
write to a file ...
}
instead, do it like this:
while (rec = getNextRec() )
{
format fields ...
write to the file ...
}
then you will never have more than 1 record in memory at a time ... and you can handle unlimited records.
a source to share
Obviously reading 60 million records at the same time uses all of your memory, so you cannot do that. (i.e. your model with 7 threads). Reading 60 million records one at a time uses up all of your time - so you can't (i.e. your initial read into the file model).
So ... you have to compromise and do a little of both.
Josh has this right - open a cursor to your database that simply reads the next record, one by one, in the simplest, most functional way. The "firehose" cursor (otherwise known as read-only, direct-only) is what you want here as it places the least load on the database. The DB won't let you update records or go backwards in the recordset, which you don't want anyway, so it doesn't need to process memory for the records.
Now you have this cursor, you are given one record at a time in the DB - read it and write to a file (or multiple files), this should finish pretty quickly. Then your task is to concatenate the files into 1s with the correct order, which is relatively easy.
Considering the number of records you have to process, I think this is the optimal solution for you.
But ... seeing that while you are doing well, why not just reduce the number of threads until you reach your memory limits. Batch processing starts overnight - that's a lot of companies, this seems to be another of those processes.
a source to share