(Further to our chat in mid Apr 2019…) Hi Sunil,
I generally agree that RDBMS such as Sybase, Oracle, DB2, MSSQL, … have good performance for Select and slower performance for Inserts, as you concluded in our last call. However, this conclusion is full of ifs and buts.
- Read performance can be improved with partitioning — Imagine if the table is relatively large like 100 million rows. Even if the queries use an index, the index can have fairly large footprint (with implications). Often the Asia users only query the Asia records while Europe users only query Europe records.
- Read performance can be improved with pre-computed data tables. If the same calculation is frequently requested via some frequent queries, then the calculation results can be saved in a pre-computed result table.
- Read performance can be improved with stored procedures.
- Read performance can benefit from de-normalization.
- Read performance can improve if entire table is in-memory, as confirmed by my 2009 DB2 trainer in NY.
Now I think most RDBMS performance tuning techniques target Select as slow Select is the most common pain and the most severe pain.
Insert is typically much slower than read, but user expectation is also less demanding. In my observation, most RDBMS databases are either mostly-select or mostly-insert. The mostly-insert database can benefit from batch insert (bcp in sybase/MSSQL), or a background writer thread (Gemfire).
However, sometimes real-time insert is needed. I think the most common strategy is sharding (horizontal partitioning) like splitting 100 million rows into two tables of 50 millions each.
A related strategy is normalization (vertical partitioning). Normalization removes duplicate data and helps inserts.