Tuning Tips#1

1# Identify Performance Bottleneck before think Performance Tuning.  For example, if your query's performance is CPU bound, buying a faster disk will be complete waste of money.

2# Know details about INs and OUTs of database engines. For example, instead of SQL INSERT statement, bulk load utility may be more effective.

3# DELETE * to empty a table can be painfully slow compared with DROP and CREATE, which in turn is much slower than TRUNCATE. But of course, your database software might not support TRUNCATE – which is why you need to know how it works.

4# Structure the data as a MOLAP cube (i.e. a multidimensional online analytical processing one) and cranking up the aggregation until your query performance flies. That may burn up disk space and data processing time, but it can make a huge difference.

5#  For cost-effective for disk-bound speed issuesThink about using solid state drives (SSDs).

6# If possible, perform extract, transform and load (ETL) processing in memory. On a long ETL job, there may be virtue in caching to disk (in case the process fails), but try to keep the data in RAM during the transformations. And cache to an SSD, not a hard drive.

7# Index your analytical structures for analysis, not for transactions. For example, by default many RDBMS engines will index the primary key of a table. That makes lots of sense in a transactional structure, where many of the queries will use those indices – but very little sense in a star schema, where it is quite common for no, none, zero, nada queries to use the primary key indices. On the other hand, all of the analytical columns in dimension tables are highly likely to be searched and yet are often entirely bereft of indices.