Optimize database for web usage (lots more reading than writing)

Question

I am trying to layout the tables for use in new public-facing website. Seeing how there will lots more reading than writing data (guessing >85% reading) I would like to optimize the database for reading.

Whenever we list members we are planning on showing summary information about the members. Something akin to the reputation points and badges that stackoverflow uses. Instead of doing a subquery to find the information each time we do a search, I wanted to have a "calculated" field in the member table.

Whenever an action is initiated that would affect this field, say the member gets more points, we simply update this field by running a query to calculate the new values.

Obviously, there would be the need to keep this field up to date, but even if the field gets out of sync, we can always rerun the query to update this field.

My question: Is this an appropriate approach to optimizing the database? Or are the subqueries fast enough where the performance would not suffer.

Answer 1

The least expensive database query is the one that you don't have to run against the database at all.

In the scenario you describe, using a high-performance cache technology (example: memcached) to store query results in your application can be a lot better strategy than trying to trick out the database to be highly scalable.

Answer 2

There are two parts:

1. Caching
2. Tuned Query
   1. Indexed Views (AKA Materialized views)
   2. Tuned table

The best solution requires querying the database as little as possible, which would require caching. But you still need a query to fill that cache, and the cache needs to be refreshed when it is stale...

Indexed views are the next consideration. Because they are indexed, querying against is faster than an ordinary view (which is equivalent to a subquery). Nonclustered indexes can be applied to indexed views as well. The problem is that indexed views (materialized views in general) are very constrained to what they support - they can't have non-deterministic functions (IE: GETDATE()), extremely limited aggregate support, etc.

If what you need can't be handled by an indexed view, a table where the data is dumped & refreshed via a SQL Server Job is the next alternative. Like the indexed view, indexes would be applied to make fetching data faster. But data change means cleaning up the indexes to ensure the query is running as best it can, and this maintenance can take time.

Answer 3

The First Rule of Program Optimization: Don't do it.
The Second Rule of Program Optimization (for experts only!): Don't do it yet.

Michael A. Jackson

If you are just designing the tables, I'd say, it's definitely premature to optimize. You might want to redesign your database a few days later, you might find out that things work pretty fast without any clever hacks, you might find out they work slow, but in a different way than you expected. In either case you would waste your time, if you start optimizing now.

The approach you describe is generally fine; you could get some pre-computed values, either using triggers/SPs to preserve data consistency, or running a job to update these values time-to-time.

Answer 4

All databases are more than 85% read only! Usually high nineties too.

Tune it when you need to and not before.