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 |
