Tuesday, December 28, 2021

Best practices to avoid database connectivity performance issues

Application performance is very important to everyone in the business.  Bellow basic steps reduce complications in database connectivity.

Problem 1 : High number of open connections / close connections

Executing multiple queries require to open and close database connection multiple times. 

This create unnecessary overhead on application as well as database.

Ex : Find address information of each employee

select employee_id from employee_table;

       This will return list of employee ids.

       for each employee id

           select address from address_table where empid=?

If there are N number of employees

then N+1 database connections will be open and close.

Solution : Use SQL joins

SQL joins can avoid multiple select queries and it will use only

single connection from data base connection pool to execute the query.

Ex : select emp.empId,emp.name,add.location from employee_table

inner join address_table on employee_table.empid=address_table.empid

Problem 2 : Table row locks

Frequent update on same table record will slow down applications. 

Queries related to same record updates have to wait until previous update statement are 

getting executed.

Ex: Multiple customers are withdrawing money from their accounts. 

       At the same time, we require to update bank's main account. 

       This will be a reason for row lock on bank account table. 

update customer_table set balance=(balance - tx_amount) where customer_id=?

update bank_account_table set balance=(balance - tx_amount) where account_id=?

Solution : Use a kind of ledger and run it in background 

               : Or Distribute transactions in to number of virtual accounts of row locker

Save incoming requests to a ledger and process them accordingly. Get the sum of transaction amounts.

Execute it as one update statement on bank account

Problem 3 : Unnecessary parameters in Connection pooling

Keeping test connection queries will add additional overhead on your database connection pooling.

Solution : Remove similar configuration listed below

<property name="validationQuery" value="select sysdate from dual"></property>

<property name="testOnBorrow" value="true"></property>

<property name="testWhileIdle" value="true"></property>

                 Add correct configurations similar to bellow

<property name="maxTotal" value="800"></property>

<property name="initialSize" value="50"></property>

<property name="maxIdle" value="50"></property>

<property name="defaultAutoCommit" value="true" />


Problem 4 : Frequent select queries on static / master data

Solution    : Use app level caching with invalidation period

Problem 5 : Frequent insert and updates

Solution : Use batch processing

Problem 6 : Query slowness with large tables

Solution : Table partitioning

                  Enable indexes

                  Enforce old data purging policy

Problem 7 : Reading bulk set of data at once

Solution    : Use paginated queries

Problem 8 : Exposing classified data

Solution    : Use database views to expose only required information of tables.

Problem 9 : API slowness

Solution    : Jmeter load testing on APIs with APM tool integration.

                     This will help to identify performance issues with method level breakdowns