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