Method for optimizing query efficiency of embedded database

Abstract

A method for optimizing the query efficiency of an embedded database relates to the field of database query, and can reduce the number of data records contained in an array A1 and an array A2 by about 5% through bidirectional upper and lower limit comparison data screening before query comparison is started. After each time of halving comparison, dynamically reducing the scale of the array A2, and gradually reducing the number of times required by the halving comparison to one time along with the reduction of the content of the array A2 in an exponential level reduction manner along with the shrinkage of the data set each time. And if the array A1 and the array A2 respectively comprise one million records, after each comparison, only one record of the array A2 is deleted as a reference to finish the whole comparison, so that the comparison work between the array A1 and the array A2 can be reduced by about 7%. Computing resources are saved, and database query efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the field of database query, and in particular to a method for optimizing embedded database query efficiency. Background Art

[0002] Currently, a common database query method uses a binary search and circular comparison to retrieve data. Suppose there are two tables T1 and T2 in a database. The traditional method first extracts the required information from table T1 based on the values ​​in the ID1 column and stores it in memory in ascending order. Then, the data in the ID2 column is read from table T2 and stored in memory in ascending order. The third step is to extract the values ​​corresponding to ID1 from the memory buffer one by one. Then, using a binary algorithm, search the T2 buffer for records matching the currently extracted ID1 value. If a match is found, the information from the two buffers is combined and fed back to the user. The next record for ID1 is read, and the third step is repeated. If no matching data is found, the next record for ID1 is read directly, and the third step is repeated. Once all records for ID1 have been queried, the query ends.

[0003] The computing resources required by traditional approaches are measured by the number of comparisons. Using the binary algorithm, searching for matching information for one record among one million records requires an average of 20 comparisons. This 1-to-1 search requires at least 20 million comparisons. Therefore, traditional database query methods waste computing resources, take long queries, and result in low query efficiency. Summary of the Invention

[0004] In order to overcome the deficiencies of the above technologies, the present invention provides an embedded database query method that saves computing resources and improves query efficiency.

[0005] The technical solution adopted by the present invention to overcome the technical problems is: A method for optimizing embedded database query efficiency, comprising: S1. Construct array A1 according to table T1 stored in the embedded database, construct array A2 according to table T2 stored in the embedded database, and construct buffer Buff in the database; S2 retains the information of the overlapping data in array A1 and array A2 and deletes the data information in array A2 that has no possible match, and determines whether array A2 has a null value. If so, execute step S12, if not, execute step S3; S3 retains the information of the overlapping data in array A1 and array A2 and deletes the data information in array A1 that has no possible match, and determines whether array A1 has a null value. If so, execute step S12, if not, execute step S4; S4. Construct variable X based on array A1; S5. Set the upper limit variable Vb and the lower limit variable Vl, and construct the variable Vm according to the variable X; S6. If variable X is less than the lower limit variable V1, execute step S11. If variable X is equal to the lower limit variable V1, set variable Y and execute step S8, Y=V1. If variable X is greater than the lower limit variable V1 and less than the upper limit variable Vb, execute step S7. If variable X is equal to the upper limit variable Vb, set variable Y and execute step S8, Y=V1. If variable X is greater than the upper limit variable Vb, execute step S12. S7. If variable X is greater than variable Vm, delete all rows of data in array A2 that are less than or equal to variable Vm and execute step S8. If variable X is less than variable Vm, set Vb = Vm and execute step S8. If variable X is equal to variable Vm, set variable Y to Y = Vm, delete all rows of data in array A2 that are less than variable Vm, set Vl = Vm and execute step 11. S8. If several rows of data matching variable X are found in variable Y, execute step S9. If no row of data matching variable X is found in variable Y, execute step S5. If upper limit variable Vb is equal to lower limit variable Vl, execute step S11. If upper limit variable Vb is not equal to lower limit variable Vl, execute step S5. S9. Record the variables X and Y into the buffer Buff of the database; S10. Reduce the amount of data in array A2 based on variable Y; S11. Determine whether there is any data in array A1. If yes, return to step S4; if no, go to step S12. S12. Return the data in the buffer Buff to the caller.

[0006] Furthermore, step S1 includes the following steps: S1-1. Table T1 stores N rows of data. The primary key of the row data is , , , table T2 stores N rows of data, The primary key of the row data is , , ; S1-2. Read N rows of data from table T1 into memory and sort the N rows of data by the primary key. The values ​​of are sorted from small to large and stored in array A1; S1-3. Read N rows of data from table T1 into memory, and sort the N rows of data by the primary key. The values ​​are sorted from small to large and stored in array A2.

[0007] Furthermore, step S2 includes the following steps: S2-1. Extract the row of data corresponding to the smallest primary key value from array A1 and mark it as A1[0]. Extract the row of data corresponding to the largest primary key value from array A1 and mark it as A1[max]. S2-2. Delete all rows of data in array A2 that are smaller than A1[0] and / or larger than A1[max]; S2-3. Determine whether array A2 contains a null value.

[0008] Furthermore, step S3 includes the following steps: S3-1. Extract the row of data corresponding to the smallest primary key value from array A2 and mark it as A2[0]. Extract the row of data corresponding to the largest primary key value from array A2 and mark it as A2[max]. S3-2. Delete all rows of data in array A1 that are smaller than A2[0] and / or larger than A2[max]; S3-3. Determine whether array A1 contains a null value.

[0009] Furthermore, A1[0] is put into the variable X.

[0010] Furthermore, step S5 includes the following steps: S5-1. Set the upper limit variable Vb and the lower limit variable Vl, Vb = A2 [max], Vl = A2 [0]; S5-2. Find in the range of the lower limit variable Vl and the upper limit variable Vb in array A2 The corresponding row data is extracted A corresponding row of data is obtained and the row of data is put into the variable Vm.

[0011] Furthermore, step S10 includes the following steps: S10-1. Delete all rows of data corresponding to the variable Y in array A2; S10-2. Delete variable Y or mark it as invalid.

[0012] The beneficial effects of the present invention are as follows: before initiating a query comparison, by filtering data using a two-way upper and lower limit comparison, the number of data records contained in arrays A1 and A2 can be reduced by approximately 5%. After each halved comparison, the size of array A2 is dynamically reduced. With each shrinkage of the data set, the number of halved comparisons required decreases exponentially with the reduction in the content of array A2, gradually decreasing to 1. If arrays A1 and A2 each contain 1 million records, after each comparison, only one record from array A2 is deleted as a reference to complete the entire comparison. This reduces the comparison work between arrays A1 and A2 by approximately 7%. In practice, if the degree of overlap between the two result sets is low (e.g., less than 40%), the deletion operation reduces the length of array A2 more rapidly, reducing overall computing resource consumption by 40% to 50%. This saves computing resources and improves database query efficiency. DETAILED DESCRIPTION

[0013] The present invention will be further described below.

[0014] A method for optimizing embedded database query efficiency, comprising: S1. Construct an array A1 according to table T1 stored in the embedded database, construct an array A2 according to table T2 stored in the embedded database, and construct a buffer Buff in the database.

[0015] S2. Retain the information of the overlapping parts of the data in array A1 and array A2 and delete the data information in array A2 that has no possible match, and determine whether there is a null value in array A2. If so, execute step S12; otherwise, execute step S3.

[0016] S3. Retain the information of the overlapping parts of the data in array A1 and array A2 and delete the data information in array A1 that has no possible match, and determine whether there is a null value in array A1. If so, execute step S12; otherwise, execute step S4.

[0017] S4. Construct variable X based on array A1.

[0018] S5. Set the upper limit variable Vb and the lower limit variable Vl, and construct the variable Vm based on the variable X.

[0019] S6. If variable X is less than lower limit variable V1, indicating that the smallest member in array A2 is greater than variable X, then execute step S11. If variable X is equal to lower limit variable V1, indicating a direct hit, then set variable Y and execute step S8, Y=V1. If variable X is greater than lower limit variable V1 and less than upper limit variable Vb, then execute step S7. If variable X is equal to upper limit variable Vb, indicating a direct hit, then set variable Y and execute step S8, Y=V1. If variable X is greater than upper limit variable Vb, then the value of the smallest member in array A1 is greater than the largest member in array A2, and subsequent operations on arrays A1 and A2 are unlikely to have matching records, then execute step S12.

[0020] S7. If variable X is greater than variable Vm, because X represents the minimum value of all matching values ​​in A1[], array A2 members smaller than the minimum value are not worth comparing. Therefore, to reduce the computational overhead of the comparison, all rows of data in array A2 that are less than or equal to variable Vm are deleted, and step S8 is executed. If variable X is less than variable Vm, it indicates that the value that variable X may match is within the range between the minimum and median values ​​of array A1. Therefore, Vb is set to Vm and step S8 is executed to narrow the query range. If variable X is equal to variable Vm, it indicates that a matching record has been found. Then, variable Y is set to Y=Vm to prepare for later feedback to the customer. Because all subsequent members of array A1 must be greater than variable X, all records in array A2 that are less than variable Vm are invalid. Therefore, all rows of data in array A2 that are less than variable Vm are deleted, a new data range is reset, and Vl is set to Vm before executing step 11 to prepare for the next comparison.

[0021] S8. If several rows of data matching variable X are found in variable Y, execute step S9. If no row of data matching variable X is found in variable Y, return to execute step S5. If upper limit variable Vb is equal to lower limit variable Vl, it means that there are no other members between upper limit variable Vb and lower limit variable Vl, indicating that no records matching variable X are found, and execute without stopping S11. If upper limit variable Vb is not equal to lower limit variable Vl, return to execute step S5.

[0022] S9. Record the variable X and the variable Y into the buffer Buff of the database.

[0023] S10. Reduce the amount of data in array A2 based on variable Y.

[0024] S11. Determine whether there is any data in array A1. If yes, return to step S4; if no, go to step S12.

[0025] S12. Return the data in the buffer Buff to the caller, completing the entire call.

[0026] By shrinking the data set in a timely manner and reducing the computing resources occupied by invalid data, we can achieve the effect of reducing computing resource consumption and shortening query time. The reduction is mainly carried out in three aspects: 1. Through steps S2 and S3, the data collection is preliminarily completed, and two-way data screening is performed on the two data sets to eliminate a large amount of invalid data information.

[0027] 2. After each median comparison is completed in step S4, invalid data before the median is deleted in a timely manner. After each matching data is found, the used records in the data set are deleted to further reduce the data records.

[0028] In one embodiment of the present invention, step S1 includes the following steps: S1-1. Table T1 stores N rows of data. The primary key of the row data is , , , table T2 stores N rows of data, The primary key of the row data is , , .

[0029] S1-2. Read N rows of data from table T1 into memory and sort the N rows of data by the primary key. The values ​​are sorted from small to large and stored in array A1.

[0030] S1-3. Read N rows of data from table T1 into memory, and sort the N rows of data by the primary key. The values ​​are sorted from small to large and stored in array A2.

[0031] In one embodiment of the present invention, step S2 includes the following steps: S2-1. Extract the row of data corresponding to the smallest primary key value from array A1 and label it A1[0]. Extract the row of data corresponding to the largest primary key value from array A1 and label it A1[max]. A1[0] is the first member in array A1 and the row of data corresponding to the smallest primary key value. A1[max] is the last member in array A1 and the row of data corresponding to the largest primary key value.

[0032] S2-2. Delete all row data in array A2 that are smaller than A1[0] and / or larger than A1[max].

[0033] S2-3. Determine whether array A2 contains a null value. If array A2 contains a null value, it means that there is no row data that meets the requirements.

[0034] In one embodiment of the present invention, step S3 includes the following steps: S3-1. Extract the row of data corresponding to the smallest primary key value from array A2 and label it A2[0]. Extract the row of data corresponding to the largest primary key value from array A2 and label it A2[max]. A2[0] is the first member of array A2 and the row of data corresponding to the smallest primary key value. A2[max] is the last member of array A2 and the row of data corresponding to the largest primary key value.

[0035] S3-2. Delete all row data in array A1 that are smaller than A2[0] and / or larger than A2[max].

[0036] S3-3. Determine whether array A1 contains a null value. If array A1 contains a null value, it means that there is no row data that meets the requirements.

[0037] In this embodiment, A1[0] is put into variable X. Variable X contains the row data corresponding to the primary key of the corresponding record to be found in array A2.

[0038] In one embodiment of the present invention, step S5 includes the following steps: S5-1. Set the upper limit variable Vb and the lower limit variable Vl, Vb=A2[max], Vl=A2[0].

[0039] S5-2. Find in the range of the lower limit variable Vl and the upper limit variable Vb in array A2 The corresponding row data is extracted A corresponding row of data is obtained and the row of data is put into the variable Vm.

[0040] In one embodiment of the present invention, step S10 includes the following steps: S10-1. Delete all rows of data corresponding to the variable Y in array A2 to further reduce the amount of data in array A2.

[0041] S10-2. Delete variable Y or mark it as invalid to prepare for the next operation.

[0042] Finally, it should be noted that the above descriptions are merely preferred embodiments of the present invention and are not intended to limit the present invention. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art will be able to modify the technical solutions described in the aforementioned embodiments or substitute equivalents for some of the technical features. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention shall be included within the scope of protection of the present invention.

Claims

1. A method for optimizing embedded database query efficiency, characterized in that: include: S1. Construct array A1 according to table T1 stored in the embedded database, construct array A2 according to table T2 stored in the embedded database, and construct buffer Buff in the database; S2 retains the information of the overlapping data in array A1 and array A2 and deletes the data information in array A2 that has no possible match, and determines whether array A2 has a null value. If so, execute step S12, if not, execute step S3; S3 retains the information of the overlapping data in array A1 and array A2 and deletes the data information in array A1 that has no possible match, and determines whether array A1 has a null value. If so, execute step S12, if not, execute step S4; S4. Construct variable X based on array A1; S5. Set the upper limit variable Vb and the lower limit variable Vl, and construct the variable Vm according to the variable X; S6. If variable X is less than the lower limit variable V1, execute step S11. If variable X is equal to the lower limit variable V1, set variable Y and execute step S8, Y=V1. If variable X is greater than the lower limit variable V1 and less than the upper limit variable Vb, execute step S7. If variable X is equal to the upper limit variable Vb, set variable Y and execute step S8, Y=V1. If variable X is greater than the upper limit variable Vb, execute step S12. S7. If variable X is greater than variable Vm, delete all rows of data in array A2 that are less than or equal to variable Vm and execute step S8. If variable X is less than variable Vm, set Vb = Vm and execute step S8. If variable X is equal to variable Vm, set variable Y to Y = Vm, delete all rows of data in array A2 that are less than variable Vm, set Vl = Vm and execute step 11. S8. If several rows of data matching variable X are found in variable Y, execute step S9. If no row of data matching variable X is found in variable Y, execute step S5. If upper limit variable Vb is equal to lower limit variable Vl, execute step S11. If upper limit variable Vb is not equal to lower limit variable Vl, execute step S5. S9. Record the variables X and Y into the buffer Buff of the database; S10. Reduce the amount of data in array A2 based on variable Y; S11. Determine whether there is any data in array A1. If yes, return to step S4; if no, go to step S12. S12. Return the data in the buffer Buff to the caller.

2. The method for optimizing embedded database query efficiency according to claim 1, characterized in that: Step S1 includes the following steps: S1-1. Table T1 stores N rows of data. The primary key of the row data is , , , table T2 stores N rows of data, The primary key of the row data is , , ; S1-2. Read N rows of data from table T1 into memory and sort the N rows of data by the primary key. The values ​​of are sorted from small to large and stored in array A1; S1-3. Read N rows of data from table T1 into memory, and sort the N rows of data by the primary key. The values ​​are sorted from small to large and stored in array A2.

3. The method for optimizing embedded database query efficiency according to claim 1, characterized in that: Step S2 includes the following steps: S2-1. Extract the row of data corresponding to the smallest primary key value from array A1 and mark it as A1[0]. Extract the row of data corresponding to the largest primary key value from array A1 and mark it as A1[max]. S2-2. Delete all rows of data in array A2 that are smaller than A1[0] and / or larger than A1[max]; S2-3. Determine whether array A2 contains a null value.

4. The method for optimizing embedded database query efficiency according to claim 1, characterized in that: Step S3 includes the following steps: S3-1. Extract the row of data corresponding to the smallest primary key value from array A2 and mark it as A2[0]. Extract the row of data corresponding to the largest primary key value from array A2 and mark it as A2[max]. S3-2. Delete all rows of data in array A1 that are smaller than A2[0] and / or larger than A2[max]; S3-3. Determine whether array A1 contains a null value.

5. The method for optimizing embedded database query efficiency according to claim 3, characterized in that: Put A1[0] into variable X.

6. The method for optimizing embedded database query efficiency according to claim 4, characterized in that: Step S5 includes the following steps: S5-1. Set the upper limit variable Vb and the lower limit variable Vl, Vb = A2 [max], Vl = A2 [0]; S5-2. Find in the range of the lower limit variable Vl and the upper limit variable Vb in array A2 The corresponding row data is extracted A corresponding row of data is obtained and the row of data is put into the variable Vm.

7. The method for optimizing embedded database query efficiency according to claim 1, characterized in that: Step S10 includes the following steps: S10-1. Delete all rows of data corresponding to the variable Y in array A2; S10-2. Delete variable Y or mark it as invalid.

Images