We have a system that handles transactions using a magnetic swipe card, similar to credit cards. There are close to 10 million users using this type of cards and the transactions for a member per month is close to 5. All transactions go to a database table ACTIVITY. Database used is Oracle 9i.
We have a requirement to generate monthly statements (similar to CC statements) showing the customer details and transaction details. All the statement details are to be written into a text file. We found that, our statement generation system is too slow. It takes close to 10 minutes to for writing each customers data into the text file.
The statement generation is done using a Scheduled job (through Quartz) and uses JDBC directly(Oracle Thin Driver).
We have a statement preperation thread that places the Statement object (containing all needed info for each customer) in to a queue. This thread prepares a ResultSet with the customerId of all members and slices the result set into 5 blocks; then spawn 5 threads to create the 'Statement' objects for respective block. Another one that picks statements from the queue and write to the text file. The writing part does not take much time. But the statement preperation thread is the real bottleneck.
Please suggest some ideas/architectures to reduce the time required to generate the statement.