How to write fast database applications?
I would to write short notices about writing effective database applications.
- What can be done on database side simply, it should be done there! Don't move useless data from server to client. When you can use one statement instead thousands statements simply, use it. Thousands queries in total means slow application.
- Slow queries is mainly your problem. There are few typical reasons why queries are slow - some can be solved simply, some not. Aggregation over billion rows cannot be fast. You have to know well your slow queries. Without this knowledge, you cannot do any well positioned optimization. If you have to run slow query, don't repeat it if it is possible. Try to remove slow query every time - cache, indexes, redesign, partitioning, .. Try to understand, why is your query slow - missing index, bad estimation, big data, too rows, locks, ...
- Don't use relational database as cache - any other software is better.
- Use cache when you expect higher load - it is not necessary for small data, small load (intranet for less than hundreds users, data less 10GB), every else the cache is necessary - slow queries, often queries. Memory is good - use it intelligently.
- Ensure good monitoring of your database, your server - you have to know where is your hardware bottleneck - IO, CPU, writing to transaction log, checkpoints.
- Don't use relational databases everywhere - transactional relational databases are perfect for typical OLTP purposes - else where some simple application over files can be better - last decade there are lot of specialized databases - it can be faster, cheaper, more robust on special workloads.