SQL - Query Performance

OLTP

OLTP query are query that comes from an OLTP application. This kind of query retrieve few rows and their performance is generally improved with a index.

OLAP

OLAP query are query that comes from a OLAP application. As they are aggregate query, their performance is generally improved by:

• the creation of a aggregate table that will act as a cache. A SQL engine may be able to rewrite the SQL against the aggregate table. See also: Recommending the best aggregation tables
• the creation of partition to enable:
  • parallel processing
  • and partition pruning.
• the changement of data structure to go from a row store to a column store data structure.
• the clustering of the data in a distributed environment.