Performance issue while inserting transactions in DB when concurrent users are more
My application has 2 components. One components (JEE 1.6_34, JBoss 6.1, MySQL 5.5, Struts 2, Hibernate 3.3) gets deployed on hundreds of end user computers (500 at present). The other component (JEE 1.6_34, JBoss 6.1, MS SQL Server 2008 R2, Struts 2, Hibernate 3.3) is deployed at central location. The end user computers are used for doing transactions - approximately 250 per day over 8-10 hours of duration. 50% of these transactions are first saved in local computer and then sent to central server asynchronously via JMS every 5-10 mins. Remaining 50% transactions are special transactions and are inserted directly in central server DB i.e. MS SQL Server using JDBC statements. During same time other operations (report generation using Jasper Reports) are also happening on central server DB. All these operations and transaction insertion accesses same set of tables. At present there are around 4.5 Million transactions (in 4 months). Each transaction has other related data which is spread across 7 other tables. We are facing performance issues while doing transactions which are directly saved in central server. The time taken to save each transaction varies from a few seconds to 15 minutes. Acceptable time limit is 10 seconds. Can someone provide some pointers on what should be looked at to improve performance while inserting transaction in central DB? During each insertion DB rearranges the indexes. Could having huge number of transactions (4.5 M) already in DB be one of the reason for overall slowdown? Will archiving older transactions to backup table help cause if less transaction are there in main table, every time transaction is inserted, there will be less re-indexing needed. Another question is can report generation, which is a daily activity and need actual daily data, be done in any different manner to avoid impact on transaction insertion. Is there anything around transaction isolation levels that should be looked at?
Re: Performance issue while inserting transactions in DB when concurrent users are mo