Sunday, 6 November 2011

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 IdUser Name
U1Narendra

Health Plan Table:

Health Plan IdHealth Plan Name
1HP1
2HP2
3HP3
4HP4
5HP5
6HP6
7HP7
8HP8
9HP9
10HP10
..
..
nHPn


    User_Health_Plan_Mapping:

User IdHealth Plan Id
U11
U12
U13
U14
U15
U16
U17
U18
U19
U110
..
..
U1n





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.

Series2122232425…..2N
Value2481632…..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 IdUser Name
U1Narendra


   Health Plan Table:

Health Plan IdHealth Plan Name2_To_Power_N Value
1HP12
2HP24
3HP38
4HP416
5HP532
6HP664
7HP7128
8HP8256
9HP9512
10HP101024
...
...
nHPnN


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 IdHealth Plan IdSUM_of_2_To_Power_N

U1
1
2046

Where 2046 = 2+4+8+16+32+64+128+256+512+1024
U12
U13
U14
U15
U16
U17
U18
U19
U110

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...:)

No comments:

Post a Comment

Post a Comment