Systems and methods for optimizing database queries

• US 9,607,042 B2
• Filed: 09/15/2014
• Issued: 03/28/2017
• Est. Priority Date: 09/16/2013
• Status: Active Grant

• Current Assignee: Mastercard International Incorporated (MasterCard Incorporated)
• Original Assignee: Mastercard International Incorporated (MasterCard Incorporated)
• Inventors: Long, Kenneth L.
• Primary Examiner(s): Woo, Isaac M
• Application Number:
  US14/486,706
• Publication Number:
  US 20150081666A1
• Time in Patent Office: 925 Days
• Field of Search: 707600-899
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning

Abstract

A computer-based method for optimizing database queries is provided. The method uses a server computer device including a processor communicatively coupled to a database. The method includes receiving a main query requesting data from a partitioned database table within the database. The partitioned database table includes a plurality of partitions. The method also includes determining a partition list. The partition list includes two or more partitions of the plurality of partitions and excludes at least one partition of the plurality of partitions. The method further includes generating a plurality of sub-queries where each sub-query is associated with one partition included within the partition list, and processing each sub-query of the plurality of sub-queries on the associated partition included within the partition list.

Claims

1. A computer-based method for optimizing database queries, the method implemented using a server computer device including a processor communicatively coupled to a database, said method comprising:

receiving a main query requesting data from a partitioned database table within the database, the partitioned database table includes a plurality of partitions;

determining, by the processor, a partition list, wherein the partition list includes two or more partitions of the plurality of partitions and excludes at least one partition of the plurality of partitions;

generating, by the processor, a plurality of sub-queries, wherein each sub-query is associated with one partition included within the partition list; and

processing each sub-query of the plurality of sub-queries on the associated partition included within the partition list.

2. The method in accordance with claim 1, wherein the main query expressly identifies two or more partitions of the plurality of partitions to include in the partition list, and wherein determining a partition list is based on the main query.

3. The method in accordance with claim 1, further comprising identifying partition information associated with the partitioned database table, the partition information including a partition key, and wherein determining a partition list is based, at least in part, on the partition key.

4. The method in accordance with claim 3, wherein the main query includes a filter criterion and wherein determining a partition list is based, at least in part, on the filter criterion and the partition key.

5. The method in accordance with claim 4, further comprising generating a sub-query for each partition included in the partition list, the sub-query including the main query with the filter criterion.

6. The method in accordance with claim 5, further comprising:

identifying at least one partition for filter-exemption based, at least in part, on the filter criterion and the partition key;

generating a sub-query for the at least one filter-exempted partition, the sub-query including the main query without the filter criterion; and

processing the sub-query on the at least one filter-exempted partition.

7. The method in accordance with claim 6, wherein identifying at least one partition for filter-exemption further comprises:

comparing the filter criterion to the partition information for each partition in the partition list;

for each partition where there is no match between the filter criterion and the partition, determining that the partition is filter-exempted; and

for each partition where there is a match between the filter criterion and the partition, determining that the partition is not filter-exempted.

8. A server computing device for optimizing database queries, said server computer device comprising a processor communicatively coupled to a database, said server computing device programmed to:

receive a main query requesting data from a partitioned database table within the database, the partitioned database table includes a plurality of partitions;

determine a partition list, wherein the partition list includes two or more partitions of the plurality of partitions and excludes at least one partition of the plurality of partitions;

generate a plurality of sub-queries, wherein each sub-query is associated with one partition included within the partition list; and

process each sub-query of the plurality of sub-queries on the associated partition included within the partition list.

9. The server computer device in accordance with claim 8, wherein the main query expressly identifies two or more partitions of the plurality of partitions to include in the partition list, and wherein the server computer device is further programmed to determine the partition list based on the main query.

10. The server computer device in accordance with claim 8, wherein the server computer device is further programmed to:

identify partition information associated with the partitioned database table, the partition information including a partition key; and

determine a partition list based, at least in part, on the partition key.

11. The server computer device in accordance with claim 10, wherein the main query includes a filter criterion and wherein the server computer device is further programmed to determine a partition list based, at least in part, on the filter criterion and the partition key.

12. The server computer device in accordance with claim 11, wherein the server computer device is further programmed to generate a sub-query for each partition included in the partition list, the sub-query including the main query with the filter criterion.

13. The server computer device in accordance with claim 12, wherein the server computer device is further programmed to:

identify at least one partition for filter-exemption based, at least in part, on the filter criterion and the partition key;

generate a sub-query for the at least one filter-exempted partition, the sub-query including the main query without the filter criterion; and

process the sub-query on the at least one filter-exempted partition.

14. The server computer device in accordance with claim 13, wherein the server computer device is further programmed to:

compare the filter criterion to the partition information for each partition in the partition list;

for each partition where there is no match between the filter criterion and the partition, determine that the partition is filter-exempted; and

for each partition where there is a match between the filter criterion and the partition, determine that the partition is not filter-exempted.

15. At least one non-transitory computer-readable storage media having computer-executable instructions embodied thereon, wherein when executed by a server computer device having at least one processor coupled to at least one memory device, the computer-executable instructions cause the processor to:

receive a main query requesting data from a partitioned database table within a database, the partitioned database table includes a plurality of partitions;

determine a partition list, wherein the partition list includes two or more partitions of the plurality of partitions and excludes at least one partition of the plurality of partitions

generate a plurality of sub-queries, wherein each sub-query is associated with one partition included within the partition list; and

process each sub-query of the plurality of sub-queries on the associated partition included within the partition list.

16. The computer-readable storage medium of claim 15, wherein the main query expressly identifies two or more partitions of the plurality of partitions to include in the partition list, and wherein the computer-executable instructions further cause the processor to determine the partition list based on the main query.

17. The computer-readable storage medium of claim 15, wherein the computer-executable instructions further cause the processor to:

identify partition information associated with the partitioned database table, the partition information including a partition key; and

determine a partition list based, at least in part, on the partition key.

18. The computer-readable storage medium of claim 17, wherein the main query includes a filter criterion and wherein the computer-executable instructions further cause the processor to determine a partition list based, at least in part, on the filter criterion and the partition key.

19. The computer-readable storage medium of claim 18, wherein the computer-executable instructions further cause the processor to generate a sub-query for each partition included in the partition list, the sub-query including the main query with the filter criterion.

20. The computer-readable storage medium of claim 19, wherein the computer-executable instructions further cause the processor to:

identify at least one partition for filter-exemption based, at least in part, on the filter criterion and the partition key;

generate a sub-query for the filter-exempted partition, the sub-query including the main query without the filter criterion; and

process the sub-query on the filter-exempted partition.