Database tuning and optimization are big buzz words these days. How do you optimize your database? What does optimizing your database get you?
Sure, you now have all of your data in a safe place, your database. But as your data grows and as you expand your database and how you use your data, you will have to enhance the way the data is accessed. This is so that as the data size and its complexity increases, your operations are not adversely affected as a result of slower response times. This should be a part of provisions for scalability. After all if you have hundreds of records, you will access them a lot faster than if you have millions. This is why all major relational databases feature ‘Views’. Views are snapshots of your most desired data. Views are dynamic and get updated as data gets updated. Very nice tool to get better database response times.
Now even with views when you have millions of records, speed of data access can become an issue. Especially for applications such as financial or banking applications that potentially have to process millions of records per minute (or even per second). With such massive volumes of data, you would need to make your views more efficient. This is why Oracle came up with ‘Materialized Views’. These are views that get saved on the disk! Yes in the case of materialized views, your views are not quite dynamic, but they almost are! You can refresh your materialized views every 10 seconds! I guess you can still call that real-time updating of views.
With Materialized views, access to data is much faster, although the view has to be read from a disk but since it can be scheduled to be refreshed (e.g. every 10 seconds), it can still be considered real-time (i.e. dynamic) data.
There are a lot of little tricks with an efficient use of Materialized Views, a discussion that I will leave for another time.
To optimize your database, other than views discussed above, you may have to change things both on the database side and the application side:
On the database side, you also need to look at how well your data is indexed. Your most accessed data items need to be indexed more efficiently. For instance, foreign keys used in joins need to be indexed. However remember that indexing does slow inserts so there are some precautions to take.
On the application side, every time you call a database routine to perform an operation, you will need to login to the database, open it, make your call and then close the database and grab the returned data (if any). You will need to see how many trips you make to the database within a single module and minimize that number as much as possible.
In conclusion, database optimization involves in-depth knowledge of database tools and techniques.