Best SQL DB Design for an overused table that causes Database locking

Question

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.

"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" Why? That seems to be kind of an odd thing to do. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Nov 21, 2016 at 2:06
Just reading from a table doesn't lock it - at least not in any modern DBMS. Which DBMS are you using? — user330315
– user330315, Commented Nov 23, 2016 at 6:59

Gordon Linoff · Accepted Answer · 2016-11-21 00:44:23Z

0

Do you have an index on (START_DATE, END_DATE, ID) on all three tables?

This should speed that query and any read-only locks should be on the index.

answered Nov 21, 2016 at 0:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Co Over a year ago

Hi Gordon, yes the above 3 columns are indexed.

Gordon Linoff Over a year ago

@MichaelCo . . . There needs to be one index with those three columns in that order.

Collectives™ on Stack Overflow

Best SQL DB Design for an overused table that causes Database locking

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related