EJB design: Database Intensive Operation from Java - CC Statement like app

  1. 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.
  2. Looks like this activity table is really huge with more than 10 * 5 = 50 mill rows. Can u check how much time the sql query takes to run when u query by cust id? U can use toad to see the time it takes. My guess is that the database access time is the most. You could explain plan and see whether everything is optimised.

    Here are some other options.
    1. Maybe we can create multiple activity tables each with an implicit range of cust ids? That will at-least reduce the search time.
    2. Make sure that prepared statements are used.
    3. Make sure that there are not a lot of hops from this batch machine to the database server.
    4. Check the network speed between these 2 m/c - they hopefully have gigabit speed cards...

    Otherwise - Pray God that most customers dont use their cards!! Just kidding