Logical Approach to Optimize Database Performance

Overview

Performance tuning is the major concern while processing data on databases. Though, there can be multiple approaches to tune the database performance at database side. But application specific approaches to optimize the performance always be kept in mind, because performance hit is solely depends on how you are going to get interacted your application with databases.This post demonstrates one of the good approaches to tune the database performance.

Problem Description

• Scenario: Let us go through the problem and scenario first.

Let say, we want to store user information related to health plans where user can have multiple health plans. Below are the corresponding tables to store the data:

User Table: User Id User Name U1 Narendra Health Plan Table: Health Plan Id Health Plan Name 1 HP1 2 HP2 3 HP3 4 HP4 5 HP5 6 HP6 7 HP7 8 HP8 9 HP9 10 HP10 . . . . n HPn

User_Health_Plan_Mapping: User Id Health Plan Id U1 1 U1 2 U1 3 U1 4 U1 5 U1 6 U1 7 U1 8 U1 9 U1 10 . . . . U1 n

In terms of storing health plan information into database there can be a traditional approach i.e. storing mappings between users and health plans (see the aforementioned User_Health_Plan_Mapping table).

• Disadvantages of this traditional approach

In this scenario, one thing must be observed that whenever health plan list is changed by user through UI, all old mappings need to be deleted and new mappings need to be inserted.

If we have to INSERT and DELETE ‘N number of rows’ only for one user, what about 50, 100 or 500 users?

Here we have to perform ‘delete’ and ‘insert’ data base operations multiple time. This can be the big performance hit.

Solution

How to get rid of this costly execution?
 A mathematical equation ‘2 to the power N’ (2N) can be used to get rid of this costly execution.

Series	21	22	23	24	25	…..	2N
Value	2	4	8	16	32	…..	N

Below is the revised solution to avoid multiple mappings between user and health plans.

Add a new column in Health Plan table which will contain ‘2 to the power N’ digits:

User Table: User Id User Name U1 Narendra

Health Plan Table: Health Plan Id Health Plan Name 2_To_Power_N Value 1 HP1 2 2 HP2 4 3 HP3 8 4 HP4 16 5 HP5 32 6 HP6 64 7 HP7 128 8 HP8 256 9 HP9 512 10 HP10 1024 . . . . . . n HPn N

Now in mapping table instead of having mappings with Health Plan Id, mapping should be with sum of the 2 to the power N digits.

User Id	Health Plan Id	SUM_of_2_To_Power_N
U1	1	2046 Where 2046 = 2+4+8+16+32+64+128+256+512+1024
U1	2
U1	3
U1	4
U1	5
U1	6
U1	7
U1	8
U1	9
U1	10

Benefits

• Only one row is required per user.
• Only one row UPDATE operation is required rather multiple DELETE and INSERT operations.

Other Concerns

Now you must have one question in your mind. How to display actual health plan on UI if SUM is stored into database instead of multiple User-Health Plan mappings?

The solution is decryption of SUM of 2 to the power N digits.

For example:  32 + 16+ 4 =44 (SUM) --> Decryption Algorithm  -->   32, 16, 4 (List of separate digits)

Decryption Algorithm (Java Utility Class):

import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.List; import java.util.Map; /** * Utility Class to decrypt the sum of 2 to the power N digits. * @author nverma */ public final class CommonUtil {    /**     * Instantiates a new common util.     */    private CommonUtil() {    }    /**     * Method gives list of individually added digits for given sum of     * '2 to the power n'     * For example: getAddedDigitsFor2PowerNSum(268L)     * [Input]: sum = 268L (256 + 8 + 4)     * 2^1 = 2     * 2^2 = 4 <--     * 2^3 = 8 <--     * 2^4 = 16     * 2^5 = 32     * 2^6 = 64     * 2^7 = 128     * 2^8 = 256 <--     * .     * .     * .     * [Output]: [256, 8, 4]     * @param sum of '2 to the power n' where n = 1,2,3,4...     * @return List<Long> of individual digits     */    public static List<Long> getAddedDigitsFor2PowerNSum(Long sum) {        Long input = sum;        Integer maxIndex = 50;        /*         * Create map like below:         * (value is 2 to power n where n = 1,2,3,4...)         * evenNo.put(1, 2L);         * evenNo.put(2, 4L);         * evenNo.put(3, 8L);         * evenNo.put(4, 16L);         * evenNo.put(5, 32L);         * evenNo.put(6, 64L);         * evenNo.put(7, 128L);         * evenNo.put(8, 256L);         * evenNo.put(9, 512L);         * evenNo.put(10, 1024L);         */        Map<Integer, Long> twoPowerNMap = new HashMap<Integer, Long>();        Long twoPowerN = 2L;        for (int index = 1; index <= maxIndex; index = index + 1) {            twoPowerNMap.put(index, twoPowerN);            twoPowerN = twoPowerN * 2;        }        // Get all individual digits for sum of '2 to the power N'        // where N = 1,2,3,4...        List<Long> result = new ArrayList<Long>();        Long counter = input;        Integer indexCounter = 0;        while (counter > 0) {            while (input > 1) {                input = input / 2;                indexCounter = indexCounter + 1;            }            // Get the integer from index position            Long valOfIndex = twoPowerNMap.get(indexCounter);            result.add(valOfIndex);            indexCounter = 0;            input = counter - valOfIndex;            counter = input;        }        return result;    } }

***If you think you need more detail and understanding on this approach keep on commenting...:)