Cursor-based Database Query Method and Gateway Device

Abstract

The present disclosure provides a database query method and a gateway device based on a cursor, relating to the technical field of databases. A database query method based on a cursor according to the present disclosure includes: monitoring the connection between a client and a database, where the database server is located in a public cloud; when it is determined that the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number, obtaining a first predetermined number of data starting from the cursor position from the database server and storing the data in the gateway; and when the client queries data, providing the data to the client by the gateway. Through such a method, without making improvements to the client software itself, data caching based on the cursor position can be achieved through processing at the gateway, and the gateway provides data to the client, avoiding excessive latency in long-distance data transmission and improving the efficiency of database data query.

Description

Technical Field

[0001] The present disclosure relates to the field of database technologies, and in particular, to a database query method based on a cursor and a gateway device. Background Art

[0002] When a user system runs in a local area network environment, the impact of network transmission delay is usually not considered, and there is a situation of high-frequency access to the network during a single operation; especially when querying a database based on a cursor, it is common to read the cursor line by line. This reading method has not been too obvious for the overall system usage perception in a low-latency local area network environment, but if the system is placed in a public cloud operating environment with a latency of more than 5 milliseconds, it will be significantly perceived that the operation response is slow. At the same time, many application software cannot optimize the query efficiency through source code transformation, or the transformation cost is too high. Summary of the Invention

[0003] An object of the present disclosure is to improve the efficiency of database data query.

[0004] According to one aspect of some embodiments of the present disclosure, a database query method based on a cursor is proposed, including: listening for the connection between a client and a database, where the database server is located in a public cloud; in the case where it is determined that the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number of times, obtaining a first predetermined number of data starting from the cursor position from the database server and storing the data in a gateway; when the client queries data, providing the data to the client by the gateway.

[0005] In some embodiments, the database query method based on a cursor further includes: in the case where the number of remaining data that has not been provided to the client in the gateway is less than a second predetermined number of data, obtaining a first predetermined number of data starting from the next data after the last data cached in the gateway from the database server and storing the data in the gateway.

[0006] In some embodiments, in the case where it is determined that the number of times the client queries the cursor one by one exceeds a predetermined number of times, the database query method based on a cursor further includes: the gateway replaces the database server to provide a data query service to the client.

[0007] In some embodiments, the gateway is located between the local area network where the client is located and a cloud dedicated line connecting to a cloud host.

[0008] In some embodiments, the database query method based on a cursor further includes: when the client starts to connect to the database, performing the operation of listening for the connection between the client and the database.

[0009] By such a method, without making improvements to the client software itself, through processing at the gateway, data caching based on the cursor position can be achieved, and the gateway provides data for the client, avoiding excessive latency in long-distance data transmission and improving the efficiency of database data query.

[0010] According to one aspect of some embodiments of the present disclosure, a gateway device is proposed, including: a monitoring unit configured to monitor the connection between the client and the database, where the database server is located in the public cloud; a caching unit configured to, when it is determined that the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number, obtain the first predetermined number of data starting from the cursor position from the database server and cache it; a data providing unit configured to, when the client queries data, provide the data to the client from the cache.

[0011] In some embodiments, the caching unit is further configured to: when the number of remaining data items not provided to the client is less than a second predetermined number, obtain the first predetermined number of data starting from the next item after the last item cached in the gateway from the database server and store it in the cache.

[0012] In some embodiments, the data providing unit is further configured to, when it is determined that the number of times the client queries the cursor one by one exceeds a predetermined number, instead of the database server, provide a data query service to the client.

[0013] In some embodiments, the gateway device is located between the local area network where the client is located and the cloud dedicated line connecting to the cloud host.

[0014] According to one aspect of some embodiments of the present disclosure, a gateway device is proposed, including: a memory; and a processor coupled to the memory, the processor being configured to execute any one of the above cursor-based database query methods based on instructions stored in the memory.

[0015] Such a gateway device can achieve data caching based on the cursor position without making improvements to the client software itself, and the gateway provides data for the client, avoiding excessive latency in long-distance data transmission and improving the efficiency of database data query.

[0016] According to one aspect of some embodiments of the present disclosure, a computer-readable storage medium is proposed, on which computer program instructions are stored, and when the instructions are executed by a processor, the steps of any one of the above cursor-based database query methods are implemented.

[0017] By executing instructions on such a computer-readable storage medium, it is possible to implement data caching based on the cursor position without improving the client software itself, and have the gateway provide data for the client, avoiding excessive latency in long-distance data transmission and improving the efficiency of database data querying. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The drawings described herein are used to provide a further understanding of the present disclosure and form a part of the present disclosure. The illustrative embodiments of the present disclosure and their descriptions are used to explain the present disclosure and do not constitute an improper limitation of the present disclosure. In the drawings:

[0019] Figure 1 is a flowchart of some embodiments of the cursor-based database query method of the present disclosure.

[0020] Figure 2 is a flowchart of some other embodiments of the cursor-based database query method of the present disclosure.

[0021] Figure 3 is a schematic diagram of some embodiments of the gateway device of the present disclosure.

[0022] Figure 4 is a schematic diagram of some other embodiments of the gateway device of the present disclosure.

[0023] Figure 5 is a schematic diagram of some further embodiments of the gateway device of the present disclosure. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0024] The technical solutions of the present disclosure will be further described in detail below with reference to the drawings and embodiments.

[0025] When the database is migrated from the local environment to the public cloud environment, the latency between the client and the database will increase significantly. Especially when the client needs to use a cursor to extract data from the database one by one, the entire operation response time will be extremely long.

[0026] Flowcharts of some embodiments of the cursor-based database query method of the present disclosure are as Figure 1 shown.

[0027] In step 101, monitor the connection between the client and the database. When the database server is located on a public cloud host, connect the local area network running the client software and the cloud host network through a cloud dedicated line; the cloud dedicated line gateway is located at the edge of the local area network, with one end connected to the local area network and the other end connected to the cloud dedicated line. The operations in the cursor-based database query method of the present disclosure are executed by the cloud dedicated line gateway.

[0028] In step 102, it is determined whether the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number of times. In some embodiments, both the predetermined time duration and the predetermined number of times can be adjusted according to requirements, user usage habits, and the type of database. If the number of times the client queries the cursor one by one within the predetermined time duration exceeds the predetermined number of times, step 103 is executed; otherwise, step 101 is continued to monitor the client operations.

[0029] In step 103, the first predetermined number of data starting from the cursor position is obtained from the database server and stored in the gateway. In some embodiments, the first predetermined number can be adjusted according to requirements, user usage habits, and the type of database.

[0030] In step 104, when the client queries data, the gateway provides the data to the client.

[0031] Through such a method, it is possible to achieve data caching based on the cursor position through processing at the gateway without making improvements to the client software itself, and the gateway provides data to the client, avoiding excessive delay in long-distance data transmission and improving the efficiency of database data query.

[0032] In some embodiments, based on a gateway device on a cloud dedicated line between an existing local area network and a cloud host, an upgraded software can be loaded to implement the functions of monitoring the connection between the client and the database, caching data in the database server, and providing data to the client, thereby eliminating the need to improve the database software itself, improving versatility, and also improving feasibility.

[0033] The flowchart of another embodiment of the disclosed cursor-based database query method is as Figure 2 shown.

[0034] In step 201, when the client starts to connect to the database, step 202 is executed.

[0035] In step 202, a gateway device between the local area network where the client is located and the cloud dedicated line connecting to the cloud host monitors the connection between the client and the database.

[0036] In step 203, it is determined whether the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number of times. If the number of times the client queries the cursor one by one within the predetermined time duration exceeds the predetermined number of times, step 204 is executed; otherwise, step 202 is continued to monitor the client operations.

[0037] In step 204, the gateway device obtains the first predetermined number of data starting from the cursor position from the database server and stores it in the gateway.

[0038] In step 205, the gateway device replaces the database server to provide data query services to the client. When the client queries data, the gateway provides the data to the client.

[0039] In step 206, the gateway device determines whether the number of remaining data items in the cache that have not been provided to the client is less than a second predetermined number, where the second predetermined number is less than the first predetermined number. If the number of remaining data items that have not been provided to the client is less than the second predetermined number, step 207 is executed; otherwise, step 205 is continued.

[0040] In step 207, the gateway device obtains from the database server the first predetermined number of data items starting from the next item after the last item cached in the gateway, and stores them in the gateway, so as to be able to replenish in time before the cache is about to be exhausted, improve sustainability, ensure continuous improvement of the database data provision rate, and improve the user experience.

[0041] Through such a method, it is possible to monitor the cursor query operations to be optimized through network listening and start the optimization function in a timely manner, which is completely transparent to the database system and application software, and there is no need to transform the relevant software system; through the cache mechanism, the remote extraction of data row by row is transformed into a one-time remote extraction of data, and then the local extraction row by row is performed. Due to the time delay difference, the overall time of software network interaction can be greatly reduced; in a transparent manner, the deployment is more convenient, there is no software transformation cost, which is conducive to popularization and application.

[0042] In some embodiments, to avoid users from repeatedly viewing previous data, the data cached in the gateway can be retained for a period of time. When the predetermined cache duration is reached or the cache space is exhausted, the expired data is cleared or the earliest cached data is overwritten to ensure the utilization rate of the storage space of the gateway device.

[0043] Some embodiments of the gateway device of the present disclosure are schematically shown as Figure 3 shown.

[0044] The listening unit 301 can listen to the connection between the client and the database. Among them, when the database server is located on the public cloud host, the local area network running the client software and the cloud host network are connected through a cloud dedicated line; the cloud dedicated line gateway is located at the edge of the local area network, one end is connected to the local area network, and the other end is connected to the cloud dedicated line. The operations in the cursor-based database query method of the present disclosure are performed by the cloud dedicated line gateway.

[0045] The cache unit 302 can, when it monitors that the number of times the client queries the cursor item by item within a predetermined time duration exceeds a predetermined number, obtain from the database server the first predetermined number of data items starting from the cursor position and store them in the gateway.

[0046] The data providing unit 303 can provide data to the client based on the cached data when the client queries data by moving the cursor. In some embodiments, the data providing unit 303 can also take over the service function and provide data query services to the client instead of the database server.

[0047] Such a gateway device can, without making improvements to the client software itself, through processing at the gateway, implement data caching based on the cursor position, provide data for the client by the gateway, avoid excessive latency in long-distance data transmission, and improve the efficiency of database data query.

[0048] In some embodiments, the caching unit 302 can also, when the number of remaining data items that the network management device has not provided to the client is less than a second predetermined number, obtain from the database server a first predetermined number of data items starting from the next item after the last item cached at the gateway, and store them in the cache.

[0049] Such a gateway device can replenish the cache in a timely manner before it is about to be exhausted, improve sustainability, ensure continuous improvement of the database data providing rate, and improve the user experience.

[0050] The structural schematic diagram of an embodiment of the gateway device of the present disclosure is as Figure 4 shown. The gateway device includes a memory 401 and a processor 402. Among them: The memory 401 can be a magnetic disk, a flash memory, or any other non-volatile storage medium. The memory is used to store the instructions in the corresponding embodiments of the cursor-based database query method described above. The processor 402 is coupled to the memory 401 and can be implemented as one or more integrated circuits, such as a microprocessor or a microcontroller. The processor 402 is used to execute the instructions stored in the memory, which can avoid excessive latency in long-distance data transmission and improve the efficiency of database data query.

[0051] In one embodiment, it can also be as Figure 5 shown. The gateway device 500 includes a memory 501 and a processor 502. The processor 502 is coupled to the memory 501 through the BUS bus 503. The gateway device 500 can also be connected to an external storage device 505 through a storage interface 504 to call external data, and can also be connected to a network or another computer system (not shown) through a network interface 506. Details are not described here.

[0052] In this embodiment, by storing data instructions in the memory and then processing the above instructions by the processor, it is possible to avoid excessive latency in long-distance data transmission and improve the efficiency of database data query.

[0053] In another embodiment, a computer-readable storage medium stores computer program instructions, which when executed by a processor implement the steps of the method in the corresponding embodiment of the database query method based on a cursor. Those skilled in the art should understand that the embodiments of the present disclosure can be provided as a method, an apparatus, or a computer program product. Therefore, the present disclosure can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present disclosure can take the form of a computer program product implemented on one or more computer-usable non-transitory storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0054] The present disclosure is described with reference to the flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present disclosure. It should be understood that each flow and / or block in the flowchart and / or block diagram, as well as the combination of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, such that the instructions executed by the processor of the computer or other programmable data processing devices generate a device for implementing the functions specified in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0055] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory generate a manufactured article including an instruction device that implements the functions specified in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0056] These computer program instructions can also be loaded onto a computer or other programmable data processing device, such that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0057] So far, the present disclosure has been described in detail. To avoid obscuring the concept of the present disclosure, some details well known in the art have not been described. Those skilled in the art can fully understand how to implement the technical solutions disclosed herein based on the above description.

[0058] The methods and apparatuses of the present disclosure may be implemented in many ways. For example, the methods and apparatuses of the present disclosure may be implemented by software, hardware, firmware, or any combination of software, hardware, and firmware. The above order of steps for the methods is for illustrative purposes only, and the steps of the methods of the present disclosure are not limited to the specific order described above, unless otherwise specifically stated. In addition, in some embodiments, the present disclosure may also be implemented as a program recorded in a recording medium, and these programs include machine-readable instructions for implementing the methods according to the present disclosure. Therefore, the present disclosure also covers a recording medium storing a program for executing the methods according to the present disclosure.

[0059] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present disclosure and not to limit them; although the present disclosure has been described in detail with reference to the preferred embodiments, those of ordinary skill in the art should understand that it is still possible to modify the specific implementation manners of the present disclosure or perform equivalent replacements for some technical features; without departing from the spirit of the technical solutions of the present disclosure, they should all be covered within the scope of the technical solutions claimed in the present disclosure.

Claims

1. A database query method based on a cursor, comprising: monitoring the connection between the client and the database, wherein the database server is located in a public cloud; when it is determined that the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number of times, obtaining a first predetermined number of data starting from the cursor position from the database server and storing them in the gateway; when the client queries data, providing the data from the gateway to the client; further comprising: when it is determined that the number of times the client queries the cursor one by one exceeds a predetermined number of times, the gateway replaces the database server to provide a data query service to the client.

2. The method according to claim 1, further comprising: when the number of remaining data items not provided to the client in the gateway is less than a second predetermined number of items, obtaining a first predetermined number of data starting from the next item after the last item cached in the gateway from the database server and storing them in the gateway.

3. The method according to claim 1, wherein, the gateway is located between the local area network where the client is located and the cloud dedicated line connecting to the cloud host.

4. The method according to claim 1, further comprising: when the client starts to connect to the database, performing the operation of monitoring the connection between the client and the database.

5. A gateway device, comprising: a monitoring unit configured to monitor the connection between the client and the database, wherein the database server is located in a public cloud; a caching unit configured to, when it is determined that the number of times the client queries the cursor one by one within a predetermined time duration exceeds a predetermined number of times, obtain a first predetermined number of data starting from the cursor position from the database server and cache them; a data providing unit configured to, when the client queries data, provide the data from the cache to the client; when it is determined that the number of times the client queries the cursor one by one exceeds a predetermined number of times, replace the database server to provide a data query service to the client.

6. The gateway device according to claim 5, wherein, the caching unit is further configured to: when the number of remaining data items not provided to the client is less than a second predetermined number of items, obtain a first predetermined number of data starting from the next item after the last item cached in the gateway from the database server and store them in the cache.

7. The gateway device according to claim 5, wherein, the gateway device is located between the local area network where the client is located and the cloud dedicated line connecting to the cloud host.

8. A gateway device, comprising: a memory; and a processor coupled to the memory, the processor being configured to execute the method according to any one of claims 1 to 4 based on instructions stored in the memory.

9. A computer-readable storage medium having computer program instructions stored thereon, and when the instructions are executed by a processor, implementing the steps of the method according to any one of claims 1 to 4.

Images