Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 11 '12 at 13:07

This question came from our site for professional and enthusiast programmers.

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. –  Andrew Russell Jan 11 '12 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 '12 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 '12 at 12:23

4 Answers 4

up vote 9 down vote accepted

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.

Benefits:

  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

Problems:

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

share|improve this answer
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. –  Lieven Keersmaekers Jan 11 '12 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 '12 at 12:29
2  
Java programmers "discover" this idea pretty often. Database designers know better. –  Mike Sherrill 'Cat Recall' Jan 11 '12 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 –  Andrew Russell Jan 15 '12 at 23:15

In addition to Andrew Russel's great answer, see also When is it better to store flags as a bitmask rather than using an associative table?.

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.

share|improve this answer

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.

DON'T TRY THIS AT HOME!

share|improve this answer

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:

Merits

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

Demerits

  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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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