Saturday, January 8, 2011

Salary structure - multiple options

Instead of having single salary structure, we started offering multiple options to our new employees,

  1. Pure stock option => X number of company shares.
  2. Stock option (Major portion) + Cash amount (Minor portion) => X number of company shares + Y amount of cash.
  3. Cash amount (Major portion) + Stock option (Minor portion) => Y amount of cash + X number of company shares.
  4. Pure cash amount => Y amount of cash.
We also allowed him to switch to other option within three month from the joining date.

Primay key look up : NOSQL vs SQL

To demonstrate this usecase, we retrieve bunch of columns for a primary key. Typical SQL would be,

"SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE WHERE PRIMARY_KEY={X}".

Lets analyze what happens when we execute above SQL,

1) Query passes through SQL engine.
a) Lexical analysis & Parsing of SQL statement (Exception will be raised if any syntax error found).
b) SQL optimization to choose optimal execution path for the statement.

2) Searching the index for primary key
Most databses store table indexs on tree structure. B+ tree is most commonly and widely used indexing structure. Even with, highly optimized B+ index structure, some form of tree search is required to locate the primary key.
3) Primary key is located, perform data retrieval.
Primary key is located, corresponding row is returned for that primary key.
Is SQL way of retrieving data based on primary key effective?. Lets see how we implement same usecase with NOSQL,

"GET [TABLE][PRIMARY_KEY]"

In NOSQL, primary key and corresponding columns are stored as Hash (key => primarykey, value => column values as string/jason). With NOSQL, looking up primary key is performed in constant time and there is no need for lexical analysis, parsing and optimization. Does this pattern sound familer?. Many large websites use MySQL with Memcached. Memcached will serve as in-memory NOSQL.

NOSQL is better for fast retrieval of data using primary key. MySQL + Memcached combination could be used to achieve the same.