Database system optimization: Business logic Design optimization

When we optimize a system, sometimes we find that we modify SQL, indexing and partitioning do not solve the performance problem. At this point you have to consider business logic optimization and table design refactoring. These two points are really very close to the design.

Business logic Optimization

In practice, we'll talk about business logic optimization first.

Case one:

Our system a document module, the customer clicks very slowly, through the performance analysis, is clicks is to query the database, then the system is through the hibernate to two steps processing:

1, calculate the total number of documents of this type.

2, display the first 20 documents of the latest document.

The time to display the second step is fast, with only 20 records, but it is slow to calculate all the totals for that type. The input of the system is very large (calculate all documents of that type, there may be tens of thousands of data), the output is a total. Because of the complexity of business logic, even indexing, partitioning, and so on speed can not be improved, because can not really do index coverage and partition elimination.

Customer is a point to wait more than 10 seconds is intolerable, at this time may input data volume is very large, the database is likely to use the hash connection, and concurrent users, a large database server pressure.

At this point the conventional optimization method is ineffective. At this time we also found that the customer is in fact the older data is not concerned about, generally only the recent data is more interested in, all of us in the query by default set the time of six months, and then set a clustered index in time. And by default this time sorting, so that it uses the merge join, reduces the input data quantity, the result speed has the obvious enhancement.

Case TWO:

We are in the optimization of a customer system, encountered a situation, in a customer selection function, customers click to select the relevant data, then the page will take a few minutes to come out, the customer is not satisfied, then modify the SQL and index is no way, he entered the amount of data is also very large, And above you also need to calculate the total number and take the latest few data.

At this time we are in the query is associated with the people, through the survey, found that the customer is only interested in their own relevant data. It's just a query for your own relevant data. So at this point in the SQL statement to increase the user ID this limit, while increasing the index of UserID, so the speed is greatly improved.

Total Answer:

Of course, the above two cases, from the input to reduce the input and output data, the main optimization of business logic, to achieve optimization system. Of course, some of the cases and customers to confirm and convince them, sometimes they do not necessarily agree, at this time to explain the purpose of doing so, I believe they will understand.

Table Design Optimization

Table design, when we develop the system has been determined that good design can greatly improve performance, we in the optimization system, encountered a more troublesome problem.

Original: Database refactoring (i): Field merging

This SQL is judged by 5 dimensions, a user ID, an organization ID, a post ID, and level judgment and whether it is public. The SQL statement has 5 "or" Composition queries, table data A large scan of the table, poor performance, but the business requirements and system requirements such judgments. Even if the five fields are indexed in the table, the speed is not fast. Too many "OR", SQL Server Query Analyzer cannot be optimized.

At this time because of design: User ID, Organization ID, post ID is 3, only one has data. So the 3 fields are merged, fewer "Or" statements, so that the database can use the index.

Summarize

Table design is optimized to allow SQL statements to be used to index, or to add redundant fields to reduce their input and output data, or to reduce query data (such as computational static tables), typically such as indexed views, data warehouses, etc.