Data query method, storage medium, and computer program product

Abstract

The present invention provides a data query method, a storage medium, and a computer program product. The method of the present invention includes first estimating the basic data volume of a local database accessed by a database link and the network transmission data volume of at least one remote database according to a query statement with a database link, then determining the maximum data volume of the basic data volume and the network transmission data volume, and judging whether the maximum data volume belongs to the remote database. If the maximum data volume belongs to the remote database, the result set database volume corresponding to the basic data volume and the network transmission data volume is estimated according to the query statement, and judging whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume. If the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database, and the query statement is sent to the target database so that the target database calculates the other end data according to the query statement to obtain the calculation result.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a data query method, a storage medium, and a computer program product. Background Art

[0002] In the database, the access to objects in remote homogeneous or heterogeneous databases is usually achieved based on database links. In this process, a connection with the remote database is first established, and then SQL statements are assembled and sent to the remote database to read the data in the remote database to the local computer, and then the calculation is completed and returned to the client.

[0003] When a query involves multiple different node databases, the data in the remote database needs to be pulled to the local, and then subsequent calculations are performed, including connection, projection, etc. When the amount of data to be transmitted is particularly large, it will take a long time to execute. Summary of the invention

[0004] An object of the present invention is to provide a data query method with high query efficiency.

[0005] In particular, the present invention provides a data query method, which comprises:

[0006] Obtaining a query statement with a database link, and estimating the basic data volume of a local database accessed by the database link and the network transmission data volume of at least one remote database according to the query statement;

[0007] Determining a maximum amount of data between the basic amount of data and the amount of network transmission data, and judging whether the maximum amount of data belongs to the remote database;

[0008] If yes, estimate the result set database volume corresponding to the basic data volume and the network transmission data volume according to the query statement, and determine whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume;

[0009] If the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database, and the query statement is sent to the target database so that the target database collects data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain a calculation result.

[0010] Optionally, after determining whether the maximum amount of data belongs to the remote database, the method further includes:

[0011] If the maximum data volume belongs to the local database, the local database is determined as the target database, all the data to be transmitted are collected in the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result.

[0012] Optionally, after determining whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the step further includes:

[0013] If the maximum data volume is less than or equal to the sum of the result set data volume and the basic data volume, the local database is determined as the target database, all data to be transmitted are collected in the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result.

[0014] Optionally, after sending the query statement to the target database so that the target database collects data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain a calculation result, it also includes: receiving the calculation result returned by the target database.

[0015] Optionally, estimating the basic data volume of the local database accessed by the database link and the network transmission data volume of at least one remote database according to the query statement includes:

[0016] After estimating the network transmission data volume of at least one remote database accessed by the database link according to the query statement, the basic data volume of the local database accessed by the database link is estimated.

[0017] Optionally, estimating the network transmission data volume of at least one remote database accessed by the database link according to the query statement includes:

[0018] Identify the remote objects involved in the query statement, group the remote objects accessed by the same database link, obtain statistical information of all the remote objects through the database link, and estimate the network transmission data volume of at least one of the remote databases based on the filtering conditions on the remote objects contained in the query statement and the connection conditions between objects belonging to the same group.

[0019] Optionally, estimating the basic data volume of the local database accessed by the database link according to the query statement includes:

[0020] The local objects involved in the query statement are identified, statistical information of the local objects is obtained through the database link, and the basic information volume is estimated according to the filtering conditions on the local objects and the connection conditions between objects belonging to the same group contained in the query statement.

[0021] Optionally, before obtaining the query statement with the database link, the step further includes: establishing a connection between the local database and at least one remote database.

[0022] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer-executable program is stored. When the computer-executable program is executed by a processor, the data query method according to any one of the above items is implemented.

[0023] According to yet another aspect of the present invention, a computer program product is provided, which includes a computer executable program, and when the computer executable program is executed by a processor, the data query method according to any one of the above items is implemented.

[0024] The present invention first estimates the basic data volume of the local database accessed by the database link and the network transmission data volume of at least one remote database according to the query statement with the database link, then determines the maximum data volume of the basic data volume and the network transmission data volume, and judges whether the maximum data volume belongs to the remote database. If the maximum data volume belongs to the remote database, the result set database volume corresponding to the basic data volume and the network transmission data volume is estimated according to the query statement, and it is judged whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume. If the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database, and the query statement is sent to the target database so that the target database collects the data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain the calculation result. Based on the technical solution provided by the present invention, when the maximum data volume belongs to the remote database and the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database and the subsequent calculation is performed by it, so as to avoid the remote database with the maximum data volume from transmitting data to other ends, reduce the performance loss caused by the network transmission of a large amount of data, and improve the data query efficiency.

[0025] Furthermore, the present invention determines the local database as the target database when the largest amount of data belongs to the local database, and determines the remote database with the largest amount of data as the target database. By the same principle, the amount of network data transmission can be reduced and the data query efficiency can be improved.

[0026] Furthermore, the present invention determines the local database as the target database when the maximum data volume is less than or equal to the sum of the result set data volume and the basic data volume, and collects all the data to be transmitted to the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result. By the same principle, the network data transmission volume can be reduced and the data query efficiency can be improved.

[0027] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below.

[0028] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0030] Figure 1 is a flow chart of a data query method according to an embodiment of the present invention;

[0031] Figure 2 is a complete flow chart of a data query method according to an embodiment of the present invention;

[0032] Figure 3 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention;

[0033] Figure 4 is a schematic diagram of a computer program product according to an embodiment of the present invention. DETAILED DESCRIPTION

[0034] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0035] It should be noted that the technical features in the embodiment and optional embodiments of the present invention can be combined with each other under the premise of no conflict.

[0036] Figure 1FIG. 1 is a flow chart of a data query method according to an embodiment of the present invention. Figure 1 As shown, the data query method proposed by the present invention at least includes the following steps S102-S108.

[0037] Step S102: obtaining a query statement with a database link, and estimating the basic data volume of a local database accessed by the database link and the network transmission data volume of at least one remote database according to the query statement;

[0038] In this step, the query statement may be an SQL statement. Before obtaining the query statement with the database link, a connection needs to be established between the local database and at least one remote database.

[0039] In this step, the network transmission data volume of at least one remote database accessed by the database link is estimated based on the query statement, and then the basic data volume of the local database accessed by the database link is estimated. The at least one remote database can be one or more. The local database is the database that the user logs in to query, and there is no essential difference from the remote database.

[0040] It should be noted that the order of estimating the basic data volume of the local database accessed by the database link and the network transmission data volume of at least one remote database may be determined according to actual conditions, and the present invention does not impose too many restrictions.

[0041] Step S104: determining the maximum data volume between the basic data volume and the network transmission data volume, and judging whether the maximum data volume belongs to the remote database;

[0042] Step S106: If yes, estimate the result set database volume corresponding to the basic data volume and the network transmission data volume according to the query statement, and determine whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume;

[0043] In this step, the final result set data volume is estimated based on the complete SQL execution plan and statistical information contained in the query statement.

[0044] Step S108: If the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the largest data volume is determined as the target database, and the query statement is sent to the target database so that the target database collects data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain the calculation result.

[0045] It should be noted that if data calculation is performed in a local database, the data results are generated directly in the local database without considering the data result transmission time. However, if data calculation is performed in a remote database, it is necessary to consider the time it takes for the remote database to return the calculation results to the local database. This is why after the above step S106 determines that the maximum data volume belongs to the remote database, it is still necessary to determine whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume.

[0046] The present invention first estimates the basic data volume of the local database accessed by the database link and the network transmission data volume of at least one remote database according to the query statement with the database link, then determines the maximum data volume of the basic data volume and the network transmission data volume, and judges whether the maximum data volume belongs to the remote database. If the maximum data volume belongs to the remote database, the result set database volume corresponding to the basic data volume and the network transmission data volume is estimated according to the query statement, and it is judged whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume. If the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database, and the query statement is sent to the target database so that the target database collects the data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain the calculation result. Based on the technical solution provided by the present invention, when the maximum data volume belongs to the remote database and the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database and the subsequent calculation is performed by it, so as to avoid the remote database with the maximum data volume from transmitting data to other ends, reduce the performance loss caused by the network transmission of a large amount of data, and improve the data query efficiency.

[0047] In one embodiment of the present invention, after determining in step S104 whether the maximum data volume belongs to the remote database, the method further includes: if the maximum data volume belongs to the local database, determining the local database as the target database, aggregating all data to be transmitted to the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result.

[0048] Those skilled in the art can understand that by determining the local database as the target database when the largest amount of data belongs to the local database, and determining the remote database with the largest amount of data as the target database, the same principle can be used to reduce the amount of network data transmission and improve data query efficiency.

[0049] In one embodiment of the present invention, after determining in step S106 whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the method further includes: if the maximum data volume is less than or equal to the sum of the result set data volume and the basic data volume, determining the local database as the target database, and aggregating all the data to be transmitted to the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation results.

[0050] It can be understood by those skilled in the art that, by determining the local database as the target database when the maximum data volume is less than or equal to the sum of the result set data volume and the basic data volume, all data to be transmitted are aggregated to the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation results. By the same principle, the amount of network data transmission can be reduced and the data query efficiency can be improved.

[0051] In one embodiment of the present invention, the above-mentioned steps of estimating the network transmission data volume of at least one remote database accessed by a database link based on a query statement include: identifying the remote objects involved in the query statement, grouping the remote objects accessed by the same database link, obtaining statistical information of all remote objects through the database link, and estimating the network transmission data volume of at least one remote database based on the filtering conditions on the remote objects contained in the query statement and the connection conditions between objects belonging to the same group.

[0052] The basic data volume of the local database accessed by the database link is estimated according to the query statement, including: identifying the local objects involved in the query statement, obtaining statistical information of the local objects through the database link, and estimating the basic information volume according to the filtering conditions on the local objects contained in the query statement and the connection conditions between objects belonging to the same group.

[0053] Next, an example is used to explain the above filtering conditions and the connection conditions between objects belonging to the same group.

[0054] The student information of each school is recorded separately in different databases. Each database records student information and class information separately. Student information includes name, gender, class number, student number, height, weight, etc. Class information includes class number, number of students in the class, grade, name of the head teacher, etc.

[0055] The current scenario is to query the average height of all first-year junior high school boys of each age group. For each database (each set of remote objects / local databases), it is necessary to filter out first-year junior high school boys based on the student information table (remote object / local object) and class information table (remote object / local object), then aggregate this information into a database (target database), and then calculate the average height of each age group.

[0056] The filtering condition for the student information table is: whether the student is a boy or not.

[0057] The filtering condition for the class information table is: the class is the first grade of junior high school.

[0058] The connection condition between objects belonging to the same group is: the class number in the student information table is equal to the class number in the class information table.

[0059] Figure 2 is a complete flow chart of a data query method according to an embodiment of the present invention. Figure 2 As shown, the data query method proposed by the present invention includes the following steps S202-S220.

[0060] Step S202: obtaining a query statement, and parsing the remote objects and local objects contained in the query statement;

[0061] In this step, the query statement carries a database link, and the database link is used to access at least one remote database and a local database, the remote database corresponds to a remote object, and the local database corresponds to a local object;

[0062] Step S204: grouping the remote objects accessed by the same database link into a group, which is n groups in total, and obtaining statistical information of all remote objects through the database link;

[0063] Step S206: for a group of remote objects, obtain the statistical information of the database objects in the current group, and calculate the network transmission data volume of the group according to the filtering conditions and the connection conditions in the group, n=n-1;

[0064] Step S208: Determine whether n is greater than 0. If not, execute step S210; if yes, return to step S206;

[0065] Step S210: Obtain statistical information of local objects through database links, estimate the basic information volume according to the filtering conditions on local objects contained in the query statement and the connection conditions between objects belonging to the same group, and determine the maximum data volume between the basic data volume and the network transmission data volume;

[0066] Step S212: Determine whether the maximum data volume belongs to the remote database. If yes, execute step S214; if no, execute step S216;

[0067] Step S214: Estimate the result set database volume corresponding to the basic data volume and the network transmission data volume according to the query statement; then execute step S218;

[0068] Step S216: determining the local database as the target database;

[0069] In this step, when the largest amount of data belongs to the local database, the local database is determined as the target database, and all the data to be transmitted are collected in the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result. This can prevent the database with the largest amount of data from transmitting data externally, reduce the network data transmission volume, and improve data query efficiency.

[0070] Step S218: Determine whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume. If so, execute step S220; if not, execute step S216;

[0071] In this step, when the maximum data volume belongs to the remote database and is less than or equal to the sum of the result set data volume and the basic data volume, the local database is determined as the target database. Similarly, the network data transmission volume is reduced and the data query efficiency is improved.

[0072] Step S220: determine the remote database with the largest amount of data as the target database.

[0073] In this step, when the largest amount of data belongs to the remote database and is greater than the sum of the result set data volume and the basic data volume, the remote database with the largest amount of data is determined as the target database and subsequent calculations are performed by it. This avoids the remote database with the largest amount of data from transmitting data to other ends, reduces the performance loss caused by network transmission of large amounts of data, and improves data query efficiency.

[0074] Based on the same inventive concept, the present invention also provides a computer-readable storage medium 30 and a computer program product 40. Figure 3 is a schematic diagram of a computer-readable storage medium 30 according to an embodiment of the present invention, Figure 4 is a schematic diagram of a computer program product 40 according to one embodiment of the present invention.

[0075] See also Figure 3 As shown, a computer-readable storage medium 30 stores a computer-executable program 31 , and when the computer-executable program 31 is executed by a processor, the data query method of any of the above embodiments is implemented.

[0076] See also Figure 4 As shown, the computer program product 40 includes a computer executable program 31, and when the computer executable program 31 is executed by a processor, the data query method of any of the above embodiments is implemented.

[0077] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable storage medium 30 for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0078] For the purpose of the description of the present embodiment, the computer-readable storage medium 30 can be any device that can contain, store, communicate, propagate or transmit a program for use with an instruction execution system, device or equipment or in conjunction with these instruction execution systems, devices or equipment. More specific examples (non-exhaustive list) of computer-readable storage medium 30 include the following: an electrical connection portion (electronic device) with one or more wirings, a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and editable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium can even be paper or other suitable medium on which the program can be printed, because the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting or processing in other suitable ways as necessary, and then stored in a computer memory.

[0079] It should be understood that each part of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0080] The computer program product 40 can be run on a computer device. The computer device may include a memory, a processor, and a computer program stored in the memory and running on the processor. The computer device may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device may be a cloud computing node. The computer device may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device may be implemented in a distributed cloud computing environment where remote processing devices linked through a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0081] The computer device may include a processor adapted to execute stored instructions, and a memory that provides temporary storage space for the operation of the instructions during operation. The processor may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0082] The computer device may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows input and output of data with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is usually shown as a communication network.

[0083] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A data query method, characterized in that: include: Obtaining a query statement with a database link, and estimating the basic data volume of a local database accessed by the database link and the network transmission data volume of at least one remote database according to the query statement; Determining a maximum amount of data between the basic amount of data and the amount of network transmission data, and judging whether the maximum amount of data belongs to the remote database; If yes, estimate the result set database volume corresponding to the basic data volume and the network transmission data volume according to the query statement, and determine whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume; If the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the remote database with the maximum data volume is determined as the target database, and the query statement is sent to the target database so that the target database collects data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain a calculation result.

2. The method according to claim 1, characterized in that After determining whether the maximum amount of data belongs to the remote database, the method further includes: If the maximum data volume belongs to the local database, the local database is determined as the target database, all the data to be transmitted are collected in the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result.

3. The method according to claim 1, characterized in that After determining whether the maximum data volume is greater than the sum of the result set data volume and the basic data volume, the method further includes: If the maximum data volume is less than or equal to the sum of the result set data volume and the basic data volume, the local database is determined as the target database, all data to be transmitted are collected in the local database so that the local database calculates the data to be transmitted according to the query statement and obtains the calculation result.

4. The method according to claim 1, characterized in that: After sending the query statement to the target database so that the target database collects data corresponding to the basic data volume and other network transmission data volumes and calculates the data according to the query statement to obtain a calculation result, it also includes: receiving the calculation result returned by the target database.

5. The method according to claim 1, characterized in that The estimating, according to the query statement, the basic data volume of the local database accessed by the database link and the network transmission data volume of at least one remote database comprises: After estimating the network transmission data volume of at least one remote database accessed by the database link according to the query statement, the basic data volume of the local database accessed by the database link is estimated.

6. The method according to claim 5, characterized in that The estimating, according to the query statement, the network transmission data volume of at least one remote database accessed by the database link comprises: Identify the remote objects involved in the query statement, group the remote objects accessed by the same database link, obtain statistical information of all the remote objects through the database link, and estimate the network transmission data volume of at least one of the remote databases based on the filtering conditions on the remote objects contained in the query statement and the connection conditions between objects belonging to the same group.

7. The method according to claim 5, characterized in that The estimating the basic data volume of the local database accessed by the database link according to the query statement includes: The local objects involved in the query statement are identified, statistical information of the local objects is obtained through the database link, and the basic information volume is estimated according to the filtering conditions on the local objects and the connection conditions between objects belonging to the same group contained in the query statement.

8. The method according to claim 1, characterized in that The step of obtaining the query statement with the database link also includes: A connection is established between a local database and at least one remote database.

9. A computer-readable storage medium having a computer-executable program stored thereon, wherein the computer-executable program, when executed by a processor, implements the data query method according to any one of claims 1 to 8.

10. A computer program product, comprising a computer executable program, wherein when the computer executable program is executed by a processor, the data query method according to any one of claims 1 to 8 is implemented.

Images