Recently received a project design work, this project has been done, but only have a dozen clients, there is a lot of difficult to overcome problems: data synchronization problem (project has client database to support offline operations), query Efficiency problem, occupying a problem with a wide network bandwidth, etc., there is no need to be heavy, today said the beauty of redundancy, and there is a point in this project.
When analyzing the query efficiency, it is found that the query is indeed complicated, connected to the tab, and in the SELECT also involves a large number of calculations, a SQL statement has a number of KB, ignoring the network factor, the statement in the database single query time Just reach nearly 2 seconds (Oracle), after the index has been optimized, but the query time is still more than one second, carefully analyzes, find some of the tables in several tables, and found a VIEW single check data in the period Just a second, initially locked the biggest problem, and then found that this view is not even connected to some tables and calculates some field values. It is so big that the price is so big. The subsequent optimization method is simpler, and the need for the necessary View, and the form of redundant fields is not large, but the insertion and update cost is not large, but it greatly improves the efficiency of the query, the final result The secondary query is 200ms, and the expected effect is achieved.
Similar examples are often encountered. For example, in the most common user management, users and roles are multi-relationships, roles and functions are also more, if we often use the corresponding relationships of users and functions, in general, the inquiry has been indexed and indexed. There is no problem with the amount of data, and there is not much data in the case of nothing more. But what should I do if the existing performance is not satisfied? It is a good choice to build a redundancy table, because this type of data is not very frequent, so the cost of maintaining the redundancy table is not too big, but it greatly improves query efficiency (at least half)
Throwing these don't say, there will be some examples in the actual code writing, remember to mention Over-Eager Evaluation concept in efficient C books, of course, if it is called redundancy calculations, it is actually This idea is to use possible redundant calculations to achieve the purpose of improving code efficiency. When an application is a disk read, if the user only reads a few bytes, we may read the data before and after the byte, of course This also has some weighing, for example, a class of business is only cared about such a little, you still do more work, it is white, but in the example, according to experience, when a user needs data, This often also needs data near these data, so that this redundant reading has brought high performance improvement.
