Problem: We have 3 TRANSACTION table with about 1.5 Million records each, and at the moment they don't have any relationship at all and are independent at each other. In our Web Portal, whenever we click on the "Transaction" Tab, our SQL query will query those 3 tables and count the current number of rows for each table.
The sample query is described below:
Query 1:
select Count (ID)
from TRANSACTION_REQUEST
where START_DATE ='2016-21-10' AND END_DATE = '2016-21-11';
Query 2:
select Count(ID)
from TRANSACTION_SUCCESS
where START_DATE = '2016-21-10' AND END_DATE = '2016-21-11';
Query 3:
select Count(ID)
from TRANSACTION_FAIL
where START_DATE = '2016-21-10' AND END_DATE = '2016-21-11';
Every time the above query is executed, we know that the tables are locked as we don't apply any ISOLATION method (Read Only). We know that we can apply ISOLATION (via Spring, Hibernate) but the problem is the code is very huge and this is just a temporary fix. And because of the table locking, some Apps are becoming slow and or doesn't work because they do access the same tables. We don't want to apply READ_COMMITTED_SNAPSHOT to "ON" on the System level because it consume huge amount of Disk Space and might cause other sort of problems too.
Since we've identified the problem, we're opting for a long term solution even if it costs the company. Hopefully you could help us with a good DB design to tackle the above problem.
