System and methods for querying and updating databases

Patent metadata

Field	Value
Publication number	US-11514066-B2
Application number	US-202016784982-A
Country	US
Kind code	B2
Filing date	Feb 7, 2020
Priority date	Nov 8, 2019
Publication date	Nov 29, 2022
Grant date	Nov 29, 2022

Abstract

Systems and method for improving query performance by querying an appropriate database engine based on the operation of the query request is provided. In one aspect, this approach involves querying a row-oriented database, querying a column-oriented database, or blacklisting the query request. In particular, updating the column-oriented database involves delete and insert operations. By maintaining updated databases and querying appropriate database engines, the response time of a query request may be improved.

First claim

The invention claimed is:

1. A system, comprising:
a row-oriented database comprising a first set of data entries;
a column-oriented database comprising a second set of data entries; and
a computing device comprising a processor configured to execute computer-executable instructions stored on a non-transitory computer-readable medium, wherein the computer-executable instructions, when executed, cause the processor to:
receive a query request associated with one or more entries in the first set of data entries or the second set of data entries;
determine whether the query request comprises performing a filtering operation to identify the one or more entries that match a particular criteria or performing an analysis operation that comprises executing one or more algorithms on the one or more entries;
query the row-oriented database in response to the query request comprising the filtering operation; and
query the column-oriented database in response to the query request comprising the analysis operation.

2. The system of claim 1 , comprising a replicator component configured to copy the first set of data entries from the row-oriented database to the column-oriented database.

3. The system of claim 2 , wherein the query request is sent to the replicator component in response to detecting an absence of the one or more entries in the column-oriented database.

4. The system of claim 3 , wherein the replicator component is configured to copy the first set of data entries from the row-oriented database to the column-oriented database in response to receiving the query request.

5. The system of claim 1 , wherein the analysis operation comprises aggregating one or more values associated with the one or more entries, and wherein the filtering operation comprises retrieving one or more values associated with the one or more entries within the row-oriented database.

6. The system of claim 1 , wherein the computer-executable instructions, when executed, cause the processor to:
query the column-oriented database in response to the query request comprising the filtering operation after querying the row-oriented database;
analyze a first performance of the query request for the row-oriented database and a second performance of the query request for the column-oriented database; and
perform a subsequent query request comprising the filtering operation on the row-oriented database or the column-oriented database based on the first performance and the second performance.

7. The system of claim 1 , wherein the computer-executable instructions, when executed, cause the processor to:
query the row-oriented database in response to the query request comprising the analysis operation after querying the column-oriented database;
analyze a first performance of the query request for the row-oriented database and a second performance of the query request for the column-oriented database; and
perform a subsequent query request comprising the analysis operation on the row-oriented database or the column-oriented database based on the first performance and the second performance.

8. A method for updating a column-oriented database comprising a set of data entries, comprising:
receiving, via a processor, an indication of a modification to one or more entries associated with the set of data;
identifying, via the processor, the one or more entries in the set of data entries in response to receiving the indication;
deleting, via the processor, the one or more entries of the set of data entries in the column-oriented database;
forming, via the processor, one or more delete data structures comprising the one or entries in a first reserve section of the column-oriented database;
inserting, via the processor, one or more updated entries in place of the one or more entries of the set of data in the column-oriented database;
joining, via the processor, the one or more delete data structures with an additional set of entries associated with one or more previously executed query requests;
deleting, via the processor, the one or more delete data structures and the additional set of entries from the first reserve section; and
generating, via the processor, one or more additional delete data structures associated with one or more additional modifications to at least a portion of the set of data entries in a second reserve section in the column-oriented database, wherein the second reserve section is different from the first reserve section.

9. The method of claim 8 , comprising removing the first reserve section after the one or more delete data structures and the additional set of entries are deleted.

10. The method of claim 8 , wherein the one or more delete data structures and the additional set of entries are deleted from the first reserve section via a batching process, wherein the batching process comprises collecting and storing the one or more delete data structures and the additional set of entries until a scheduled time.

11. The method of claim 8 , wherein the modification comprises an update, insert, or delete operation performed on the one or more entries associated with the set of data entries.

12. A non-transitory computer-readable medium, comprising computer-executable instructions that, when executed by one or more processors, cause the one or more processor to:
receive a query request directed to a column-oriented database;
determine whether the query request corresponds to one of a predefined list of query requests, wherein the predefined list of query requests comprises a blacklist of query requests that have been denied access to the column-oriented database; and
query a row-oriented database based on the query request in response to the query request corresponding to the one of a predefined list of query requests.

13. The non-transitory computer-readable medium of claim 12 , wherein the computer-executable instructions cause the one or more processor to query the column-oriented database based on the query request in response to the query request not corresponding to the one of the predefined list of query requests.

14. The non-transitory computer-readable medium of claim 12 , wherein the predefined list of query requests comprises one or more point queries, one or more large field projections, one or more date trend requests, one or more time trend requests, and any combination thereof.

15. The non-transitory computer-readable medium of claim 12 , wherein the computer-executable instructions cause the one or more processor to:
determine whether an expected response time for the query request to be performed via the row-oriented database is less than a first threshold amount of time;
query the row-oriented database based on the query request in response to the expected response time being less than the first threshold amount of time; and
update the predefined list of query requests to include the query request in response to the expected response time being less than the first threshold amount of time.

16. The non-transitory computer-readable medium of claim 12 , wherein the computer-executable instructions cause the one or more processor to:
determine whether the query request fails on the column-oriented database;
query the row-oriented database based on the query request in response to the query request failing on the column-oriented database; and
update the predefined list of query requests to include the query request in response to the query request failing on the column-oriented database.

17. The non-transitory computer-readable medium of claim 12 , wherein th

Assignees

• Servicenow Inc

Inventors

• Shah Siddharth Rajendra
• Norris Jeremy
• Baxa Olivier
• Mart Josef

Classifications

• G06F16/24542

  Plan optimisation · CPC title

• G06F16/24553

  of query operations · CPC title

• G06F16/214

  Database migration support · CPC title

• G06F16/221 Primary

  Column-oriented storage; Management thereof · CPC title

• G06F16/2471 Primary

  Distributed queries · CPC title