CN100365626C - A Database Optimization Method - Google Patents

Abstract

The invention discloses a method for optimizing a database. The method comprises the following steps: A. Determining the original performance template of the user's database, according to the calculation relationship between the performance index of the non-original collected data and the performance index of the original collected data in the determined original performance template of the database Obtain the temporary performance index, and add the temporary performance index to the original performance template of the database; B. Use the temporary performance index obtained in step A to modify the original performance template of the database to obtain a new performance template of the database. By using the database optimization method of the invention, the calculation amount of the database performance template is reduced, and the processing efficiency is improved.

Description

A Database Optimization Method

technical field

The invention relates to database management technology, in particular to a database optimization method.

Background technique

In the performance management system, through performance data analysis, users can obtain the characteristics of various indicators in the system. In order to clearly understand the performance data collected from each node of the system, system developers usually save them in the form of a database. The performance management system analyzes and processes the data stored in the database according to the user's needs, and provides the results to the user through tables or graphics.

The data that users care about in the database is called performance indicators. The performance index includes two kinds of data: one is the original collection data obtained directly from the database, and the other is the data obtained by analyzing and calculating the original collection data in the database. When users use it, they group logically related performance indicators to form multiple database performance templates. The performance management system reads the original collected data from the performance template of the database, that is, the above-mentioned first performance index; then analyzes and calculates the read performance index to obtain the above-mentioned second performance index.

Taking the traffic statistics (Traffic Statistics) database performance template shown in Figure 1 as an example, the method for processing the performance indicators in the database by the existing performance management system is described. The user-defined traffic statistics performance template represents the basic traffic information based on a given network element, and the performance indicators include incoming traffic (Incoming Traffic), outgoing traffic (Outgoing Traffic), average traffic (AverageTraffic), average subtraffic (Average SubTraffic ), traffic ratio (Traffic Ratio) and composite traffic (MultiTraffic). In addition, the template also includes items such as Template Name, Performance Index, Measuring Entity, and Formula.

In the database performance template of flow statistics, the input flow and output flow are the original collected data, while the other four performance indicators are calculated from the input flow and output flow according to the formula specified in the database performance template.

The process of each performance indicator in the traffic statistics database performance template is as follows:

1. Input traffic: the original collected data, the database is directly obtained from the outside.

2. Output flow: the original collected data, the database is directly obtained from the outside.

3. Average flow rate: first read the calculation formula of the average flow rate from the flow statistics; then, read the values of the input flow rate and the output flow rate, and then calculate the sum of the input flow rate and the output flow rate; finally, calculate the sum of the input flow rate and the output flow rate Divide by 2 to get the value of the average flow.

4. Average sub-flow: first read the calculation formula of average sub-flow from the flow statistics; then, read the values of input flow and output flow, and then calculate the difference between input flow and output flow; finally, input flow and output flow The difference is divided by 2 to obtain the value of the average subflow.

5. Flow rate: first read the calculation formula of flow rate from the flow statistics; then, read the values of input flow and output flow, and then calculate the sum of input flow and output flow, the difference between input flow and output flow; finally, Divide the sum of the input flow and the output flow by the difference between the input flow and the output flow to obtain the value of the flow rate.

6. Composite flow: first read the calculation formula of composite flow from the flow statistics; then, read the values of input flow and output flow, and then calculate the sum of input flow and output flow, the difference between input flow and output flow; finally, Multiply the sum of the input flow and output flow by the difference between the input flow and output flow to obtain the value of the composite flow.

It can be seen from the processing process of each performance index in the performance template of the traffic statistics database above that the disadvantages of the existing database processing methods are:

When calculating the average flow, average sub-flow, flow rate and composite flow, the sum of the input flow and the output flow and the difference between the input flow and the output flow are recalculated multiple times, which reduces the processing efficiency of the database performance template for performance indicators .

Contents of the invention

In view of this, the purpose of the present invention is to provide a database optimization method to improve the processing efficiency of performance indicators.

To achieve the above object, the present invention provides a database optimization method, the method comprising the following steps:

A. Obtain the temporary performance index according to the calculation relationship between the performance index of the non-original collected data and the performance index of the original collected data in the original performance template of the database, and add the temporary performance index to the original performance template of the database;

B. The calculation relationship of the non-original collected data performance index related to the temporary performance index obtained in step A is replaced by the temporary performance index.

The method further includes: adding a display flag for the performance index in the database performance template in the step B.

The method of adding display flags is: adding displayable flags to non-temporary performance indicators; adding non-displayable flags to temporary performance indicators.

The method further includes: determining the calculation sequence level of all performance indicators according to the calling relationship of each performance indicator.

The method for determining the calculation sequence level of all performance indicators is: set the calculation sequence level of the performance indicators of the original collected data to level 0, and set the performance indicators calculated by calling 0-(K-1) level performance indicators to level K.

The present invention also provides a database optimization device, which includes:

It is used to obtain the temporary performance index according to the calculation relationship between the performance index of the non-original collected data and the performance index of the original collected data in the original performance template of the database, and add the temporary performance index to the unit in the original performance template of the database;

It is used to replace the calculation relationship of the non-originally collected data performance index related to the obtained temporary performance index with the temporary performance index to obtain a new unit of the database performance template.

By applying the present invention, the calculation relationship repeatedly used in the database performance index processing process is used as a temporary performance index, and the display flag and the calculation order level are set for each performance index, thereby improving the processing efficiency of the performance template in the database. Specifically, the present invention has the following beneficial effects:

1. The present invention sets the calculation relationship repeatedly used in the performance index processing process of the database performance template as a temporary performance index, which reduces the calculation amount of the database performance template and improves the processing efficiency.

2. The present invention only presents the performance indicators of the database that the user cares about by setting the display flags, which improves the convenience of using the database and makes the user interface more friendly.

3. The present invention sets the calculation sequence level according to the call relationship of the performance indicators in the database performance template, and the database processes each performance indicator according to the calculation sequence level, which clarifies the processing sequence and improves the processing efficiency.

Description of drawings

FIG. 1 is a schematic diagram of a performance template of a traffic statistics database in the prior art.

Fig. 2 is an overall flowchart of the database optimization method of the present invention.

Fig. 3 is a schematic diagram of the optimized traffic statistics database of the present invention.

Detailed ways

In order to make the purpose and technical solution of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings and examples.

The present invention is a database optimization method, the basic idea of which is: use the calculation relationship repeatedly used in the performance index processing process as a temporary performance index, and set the calculation order level for the temporary performance index and the real performance index at the same time, so that the performance management system follows the calculation Each performance index is processed at the sequence level; and a flag bit (Flag) is added to each performance index, and the performance management system decides whether to display the performance index to the user according to the flag bit.

As shown in Figure 2, the database optimization method of the present invention comprises the following steps:

Step 201. Determine the original performance template of the user's database.

In this step, the performance management system determines the original performance template according to the user's requirements for performance indicators. For example, the performance indicators that users care about include A, B, C, M1, M2, M3, and M4. Among them, it is assumed that the performance indicators A, B, and C are the original collected data. The so-called original collected data means that the database performance template can be obtained directly from the outside. Its numerical data; and the remaining four performance indicators are obtained through the calculation of A, B, and C, and the above seven performance indicators form the original performance template of the database as shown in Table 1.

Performance formula A B C M1 A/(A+B+C)×100 M2 B/(A+B+C)×100 M3 (A+B)×(A-B) M4 (A-B)/(A+B)

Table 1

Step 202. Analyze the original performance template of the database, obtain a temporary performance index according to the calculation relationship between various performance indexes, and add the temporary performance index to the original performance template of the database.

It can be seen from Table 1 that in the calculation process of M1, M2, M3 and M4, the sum of A and B, the difference between A and B and the sum of A, B and C are used repeatedly, so this step will The results T1, T2 and T3 corresponding to the above three calculation relationships are used as temporary performance indicators, where T1 is equal to the sum of A and B, T2 is equal to the difference between A and B, and T3 is equal to the sum of T1 and C. After adding the above three temporary performance indicators into the original performance template of the database, the database performance template shown in Table 2 is obtained.

Performance formula A B C M1 A/(A+B+C)×100 M2 B/(A+B+C)×100 M3 (A+B)×(A-B)

M4 (A-B)/(A+B) T1 A+B T2 A-B T3 T1+C

Table 2

Step 203. Use the temporary performance index obtained in step 202 to modify the original performance template of the database.

In this step, the non-original data performance indicators that need to be converted are modified according to the temporary performance indicators, that is, the calculation formulas of M1, M2, M3 and M4 in Table 2 are converted into the form expressed by the original collected data and temporary performance indicators, and obtained The database performance template shown in Table 3.

Performance formula A B C M1 A/T3×100 M2 B/T3×100 M3 T1×T2 M4 T2/T1 T1 A+B T2 A-B T3 T1+C

table 3

Step 204. Add a display flag for the performance index in the modified database performance template.

This step sets the displayable flag bit 1 and the non-displayable flag bit 0, that is, when the flag bit is equal to 0, the performance index is hidden from the user; when the flag bit is equal to 1, the flag bit is displayed to the user. As far as the database performance template in Table 3 is concerned, since A, B, C, M1, M2, M3, and M4 are the performance indicators in the original performance template of the database, they are the parts that users care about, so the above seven performance indicators are displayed to For the user, the flag bit is set to 1, and T1, T2, and T3 are temporary performance indicators, which should be hidden from the user, and the flag bit is set to 0. After performing this step, the database performance template shown in Table 4 is obtained. Of course, the displayable flag bit can also be set to 0, while the non-displayable flag bit can be set to 1.

Performance flag bit formula A 1 B 1 C 1 M1 1 A/T3×100 M2 1 B/T3×100 M3 1 T1×T2 M4 1 T2/T1 T1 0 A+B T2 0 A-B T3 0 T1+C

Table 4

Step 205. Determine the calculation sequence levels of all performance indicators according to the calling relationship of each performance indicator.

In this step, different calculation order levels are set for all performance indicators, that is, the calculation order level of the original collected data is set to 0, and the items that directly call the performance indicators of level 0 to be calculated are set to level 1, and the 0~(K -1) The items to be calculated for the grade performance index are set to K grade.

For the database performance template in Table 4, the performance indicators A, B, and C are the original collected data, and the database can directly obtain their values from the outside, so the calculation order of A, B, and C is 0; the performance indicators T1 and T2 are directly called The original collected data is used for calculation, so its calculation order level is level 1; performance indicators M3, M4 and T3 call level 0 and level 1 performance indicators, so their calculation order level is level 2; performance indicators M1 and M2 call level 0 Level, level 1 and level 2 performance indicators, the order of calculation is level 3. After processing in this step, the database performance template shown in Table 5 is obtained.

Performance flag bit formula Calculation order rank A 1 0 B 1 0 C 1 0 M1 1 A/T3×100 3 M2 1 B/T3×100 3 M3 1 T1×T2 2 M4 1 T2/T1 2 T1 0 A+B 1 T2 0 A-B 1 T3 0 T1+C 2

table 5

The database performance template in Table 5 processes each performance index according to the calculation order level, that is, first calculates the performance index of level 0, and then calculates the performance index of level 1 until the performance index with the largest value of the calculation order level is calculated.

So far, the performance management system has completed the optimization of the database performance template.

Step 204 in the present invention can be performed after step 205, or the above two steps can be performed simultaneously.

For the traffic statistics database performance template shown in Figure 1, when optimizing according to the method of the present invention, the specific process is as follows:

First, determine the original performance template of the user database and extract temporary performance indicators.

Since the sum of the input flow and the output flow and the difference between the output flow and the output flow are reused in the calculation of the average flow, the average subflow, the flow rate and the composite flow, the sum of the input flow and the output flow is set as the temporary performance The index TempTraffic1 sets the difference between the input traffic and the output traffic as the temporary performance index TempTraffic2.

Then, the calculation formula of the performance index in the original performance template of the database is modified by using the temporary performance index.

At this time, the average traffic is equal to the temporary performance index TempTraffic1 divided by 2, the average sub-traffic is equal to TempTraffic2 divided by 2, the traffic rate is equal to the quotient of the temporary performance index TempTraffic1 and the temporary performance index TempTraffic2, and the composite traffic is equal to the temporary performance index TempTraffic1 and the temporary performance index TempTraffic2 The product.

Then, flag bits are added for the performance indicators in the modified traffic statistics.

Display the various performance indicators in the traffic statistics before modification to the user, and hide the temporary performance indicators, that is, add a flag with a value of 1 to the input flow, output flow, average flow, average sub-flow, flow rate, and composite flow; and A flag bit with a value of 0 is added to the temporary performance indicator TempTraffic1 and the temporary performance indicator TempTraffic2.

Finally, according to the call relationship of each performance index, determine the calculation sequence level.

In traffic statistics, the input flow and output flow are the original collected data, so their calculation order level is 0; the temporary performance indicators TempTraffic1 and TempTraffic2 are directly obtained by using the input flow and output flow, so their calculation order level is 1 ; and the average traffic, average sub-traffic, traffic rate and compound traffic will call temporary performance indicators TempTraffic1 and temporary performance indicators TempTraffic2, so their calculation sequence level is 2.

Figure 3 shows the optimized traffic statistics database performance template, where CalculationOrder is the calculation order level, Flag is the display flag bit, and NULL means there is no content in the item.

The above descriptions are only preferred embodiments of the present invention, and are not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included in the present invention. within the scope of protection.

Claims (6)

1. A database optimization method, characterized in that the method comprises the following steps: A. Obtain the temporary performance index according to the calculation relationship between the performance index of the non-original collected data and the performance index of the original collected data in the original performance template of the database, and add the temporary performance index to the original performance template of the database; B. Replace the calculation relationship of the non-originally collected data performance index related to the temporary performance index obtained in step A with the temporary performance index to obtain a new database performance template. 2. The method according to claim 1, further comprising: adding a display flag for the performance index in the database performance template in step B. 3. The method according to claim 2, wherein the method of adding display flags is: adding displayable flags for non-temporary performance indicators; adding non-displayable flags for temporary performance indicators. 4. The method according to any one of claims 1 to 3, characterized in that, the method further comprises: determining the calculation sequence level of all performance indicators according to the calling relationship of each performance indicator. 5. The method according to claim 4, characterized in that, the method for determining the calculation sequence level of all performance indicators is: the calculation sequence level of the original collection data performance indicators is set to 0, and the call 0～(K- 1) The performance index for calculating the level performance index is set to K level. 6. A database optimization device, characterized in that the device comprises: It is used to obtain the temporary performance index according to the calculation relationship between the performance index of the non-original collected data and the performance index of the original collected data in the original performance template of the database, and add the temporary performance index to the unit in the original performance template of the database; It is used to replace the calculation relationship of the non-originally collected data performance index related to the obtained temporary performance index with the temporary performance index to obtain a new unit of the database performance template.

Images