Friday, December 3, 2010

Some db performance optimizations

1. Materialized views : 
Views where the results are cached, and updated time-to-time.

2. Save the result of aggregate operations, and refresh periodically ( or use triggers)
(for e.g. stackoverflow updates your accept rate, not immediately, but after some time)

3. Vertically split tables (by columns), keep the primary key column in both the partitions.
If the original table is being maintained, no need to carry over the RI constraints.

4. Redundant data
table1(empid,empname,deptId) table2(deptId,deptName)
if empname,deptName are usually fetched together, ponder
copying the deptName to the first table.

5. While dealing with repeating groups, you can consider having multiple columns
rather than multiple rows in order fasten the access.
but before doing this know that,
-> it will be difficult to aggregate the data
-> it will be accessed collectively

No comments:

Blog Archive