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. Help from anyone experienced in generating credit card statements/bills in any platform/language would be greatly appreciated.
Are you saying that every customer has about 5 transactions per month, and it is taking you close to 10 minutes to retrieve those 5 transactions for a customer !!!
I am assuming your SQL statement looks something like ...
where customerID = ? and transaction_date between ? and ?
From a physical database layout
1. You must have an index on
2. It will be nice (but not necessary) to partition your table on the transaction month
3. The table should have been analyzed
Then you should check the execution plan for your statement and it should be doing a range scan on your index. What does your execution plan look like right now ?
If all these things go okay, the biggest component of your processing will be the network latency
First "divide and concur". is it from Oracle or from java?
Then write your findings and I'll try to help out.
I recommend the following.
1. debug java code. Enter timings in java code to a log file. Especially on the points were the JDBC is used!
2. I Use statspack on oracle, to view statistics on heavy Oracle statements.
3. Monitor cpu usage and memory usage on both application servers and DB servers. see who is busy