When we transform initial ideas into clear requirements for databases, we typically have to make the following choices:
Frequent maintenance vs doing it once.
As we are clarifying the requirements, we need to determine whether we want to concinue spending considerable time maintaining the system, or if we want to finish it up and move on to other tasks. Race car maintenance vs installing electric wiring is my favorite analogy for this kind of choice.
In some cases we need to sqeeze every last bit of performance out of a highly important query, and we are ready to re-optimize as needed. This is similar to maintaining a race car, which is very labor-intensive, and is done very frequently, pretty much all the time. Another similarity is that the goal is to get the best possible performance, and we are ready to pay a high price for it.. When we deal with race cars, long term considerations are totally beyond our horizon – we need to do our best right now, whatever it takes, and we may go for another cool new approach at any moment. If this is the case, then doing anything defensively for the long term is completely irrelevant.
In other cases we really want to get the job done once, and not to return to it for as long as possible. This is similar to installing electric wiring in a house, because in order to avoid expensive re-wiring, we build in some redundancy into our solution; we have more outlets and more capacity than we actually need right now, and all outlets are grounded even if we only plug table lamps into them.
Typically our wiring solution does not perfectly fit our current needs: some outlets are not positioned exactly where they would be most convenient right now, some others are useless right now. We are likely to have to use extension cords, and we can tolerate that. Yet we are more willing to cope with the minor inconvenience of using an extension cord than to reinstall outlets every time we move furniture.
When we deal with electric wiring, safety and long term stability is highly important, and it makes a lot of sense to do everything very defensively. Also it makes sense to build in more capacity than we need right now.
Of course, most real life situations are not as clearly cut as these two extreme examples – they are typically somewhere in between.
Yet in many real life cases that we would rather do the query tuning just once, and we are ready for some overkill, for some redundancy – that is the price we are willing to pay for not having to return and re-optimize the query later.
Best performance on average vs. acceptable performance all the time.
Depending on the circumstances, the criteria for acceptable performance may be quite different in different situations. For example, as I am writing T-SQL code, book chapters, and articles, I can and do take breaks from time to time. As a result, it may take me an hour or more to finish just one sentence, or one method, or one query, and that’s OK. It is the overall, the average performance that matters, as long as I manage to meet my deadlines.
On the other hand, if I am driving, all my driving decisions must be done quickly, without any exceptions.
Let us apply a similar approach to queries. For instance, let us consider the following two queries which always return identical results:
- Query A runs in 50 ms 99.8% of the time, but takes 1500 ms in 0.2% cases
- Query B always runs between 350ms and 450ms.
Which one of these two queries should we choose? It depends. Of course query A is a better citizen; on average it uses up less resources on the server. As such, in many cases we would really want to choose query A over query B. However, if our query is required to always return in 1000ms or less, then, unfortunately, query A does not meet the requirements, and query B is our only choice.
As we have seen, to choose which query better fits the needs of the customers, we need to know the requirements.
Performance of reads vs. performance of writes.
As we tune select queries we may need to make yet another choice: how much can we slow down our modifications to speed up our selects? As in two previous subsections, let us consider two extreme examples:
- Security video must always record real time, and we don’t expect very high quality from it. Most likely this video recording will never be viewed. Only in the unlikely event that it will be analyzed, only then we shall work on enhancing its quality.
- On the other hand, if we are making a movie, it can take several hours or even days to come up with a short episode. This is OK: because we expect a lot of views, we can afford to invest considerable time and effort in improving quality. Also modifying a DVD is not an option - if we need to change anything, we have to burn another one.
As in the previous choices, most real life situations fall somewhere between these two extreme cases, but we need to determine where we are on this scale.