This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know whether this a better approach for the explained scenario in the link below,

DB Optimization Blog Link

If there are another better option kindly advice.

  • 3
    Please include the content from the blog posting into this question (you can edit the post), this will give us all more context for the question and you can personally ask Narenda for permission. Jan 11, 2012 at 7:57
  • This is posted in a public email list for more suggestions. So i am trying to find even interesting solution for the same. Narendra is fine with this. He will come to know eventually as i find his thoughts are very interesting.
    – Futur
    Jan 11, 2012 at 12:22
  • This link is about to be shared with the mail list where in Narendra will see it and explore more too.
    – Futur
    Jan 11, 2012 at 12:23

4 Answers 4


So that solution is:

  1. Creating a Bit Array in the user table to register a user to a health plan.
  2. Then mapping the binary digit position to the health plan table.


  1. Very little storage required, You might save $20 over the lifetime of the project.
  2. Faster updates/deletes albeit perhaps not measurable.
  3. Faster queries when not selecting by Health Plan


  1. The maximum number of distinct plans has to stay small. (32 or 64 depending on field size)
  2. Nowhere to store plan start date, plan end date or other relevant data that you will probably need.
  3. You need to decode the plan every time you use or apply it using bit operations.
  4. There is no quick and easy way to do generic type queries that link the tables quickly.
  5. Slower queries for health plan driven queries (i.e. all 'H10' Plans) without special indexes and query tools.

Issues with the original premise:

  1. When subscribing to a new plan / leaving a plan you can just insert or delete the 'changed' records.

    Insert will add a new record ('U1',3).

    Delete will only need to delete the relevant record User_Health_Plan_Mapping(U1,10)

  2. You seem to assume that db optimization is about minimizing the storage, it is more about the following.

    • Placing data so that it is easy to understand.
    • Storing it long term (think 5 generations of the code that talks to it)
    • Making the data extensible, supportable, searchable, reliable, secure, robust, backed up.
    • Optimizing the whole ecosystem (time to market, development time, operational time, hardware cost, business process, call center hours)
  3. Optimizing the tables / indexes for the specific use-cases that is required of your application. Which is not about processor power, but indexes, direct access to hard disk and cache optimization (unless you have a trivial sized database that can stay in memory).

  • 1
    +1 The mentioned optimization trick might have its merits but does indeed come with serious drawbacks. To OP, you should ask yourself how many transactions/second we are talking about. If your DBMS is able to handle the amount you require, don't bother creating a maintenance problem to fix a non existing performance problem. Jan 11, 2012 at 8:08
  • Thanks @Andrew Russell . What you have explained gives lot more insights. Thanks a lot for your time. Btw, is there any other better approach for handling this? Anyways i am pulling in Narendra also to come up with his scenario explanations.
    – Futur
    Jan 11, 2012 at 12:29
  • 2
    Java programmers "discover" this idea pretty often. Database designers know better. Jan 11, 2012 at 12:59
  • @Futur, as Catcall implies, this is a skilled profession that the dba's and database developers have been working in forever. It is the skill of Entity / Relationship modelling. The literature goes back to 1970 or maybe earlier on this. seas.upenn.edu/~zives/03f/cis550/codd.pdf Jan 15, 2012 at 23:15

In addition to Andrew Russel's great answer, see also https://stackoverflow.com/questions/5708239/when-is-it-better-to-store-flags-as-a-bitmask-rather-than-using-an-associative-t/5708369#5708369.

I'm guessing a very common query would be "find all users who are subscribed to healthplan 1"; in this design, that would be a horrible query without all sorts of special indices. Maintaining those indices on insert/update would almost certainly negate the benefits of the reduced number of insert/deletes.

If you really do have a performance problem with the insert/update methods on your table, buy better hardware, look into partitioning, or see if you can archive data.


It's called a bit mask, and while it does save space, it is otherwise a bad idea.

Consider how you might join based on child records - a real hassle and it would perform poorly.



Thanks all for providing your comments and suggestions.

The approach mentioned in http://www.nverma-tech-blog.blogspot.com/2011/11/logical-approach-to-optimize-database.html has it's own merits and demerits:


  1. Reduces the number of INSERT and DELETE operations.
  2. Does not take much decryption time to separate health plans for a user.


  1. 2 to power n series grows exponentially. The maximum number of plan mappings must stay small to fit the maximum size of data types.
  2. Decryption of users specific to a health plan will take extra processing power during searches.

Per my requirement, I had at max 50-70 mappings per single user and did not require any search specific to the mapped items. Hence, I opted for this one.

More ideas and comments are most welcome.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.