IP Library › Granted Patent US 10,909,116
Granted Patent B2
US 10,909,116 · App. 15/899,694 · Granted Feb 2, 2021

Optimizing query processing and routing in a hybrid workload optimized database system

Inventors: Al Chakra (Apex, NC); Ruiping Li (San Jose, CA); Terence P. Purcell (Springfield, IL); Lily Zhou (Sunnyvale, CA)
Assignee: International Business Machines Corporation
G06F16/24545G06F16/2379G06F16/24537
View Patent ↗
Loading inventors, assignments & file history…
Monitor This Case
Get email alerts when status or documents change.
Order Certified Copies
Most orders are placed with the USPTO same day — all within 24 business hours.
Order via The Patent Place →
Pre-filled with this patent's details
Quick Facts
Patent No.
US 10,909,116
App. No.
15/899,694
Granted
Feb 2, 2021
Kind
B2
Abstract

A method, system and computer program product for processing queries in a hybrid database system. A database management system of the hybrid database system obtains an estimated execution time of a query by an online transaction processing engine. The database management system further obtains an estimated execution time of the query by a database accelerator. Furthermore, the database management system determines a wait time (which could be zero) for the database accelerator to process the query. The database management system then determines whether the online transaction processing engine or the database accelerator will process the query using the estimated execution time of the query by the online transaction processing engine, the estimated execution time of the query by the database accelerator and the wait time (which could be zero) for the database accelerator to process the query. In this manner, the processing of queries in hybrid database systems is optimized.

Claims (57)

1. A method for processing queries in a hybrid database system, the method comprising:

receiving a query to be processed;

determining an approximate filtering for execution of said query by an online transaction processing engine and a database accelerator using information from an index probe and validated real-time statistics in response to literals of a query statement being known;

estimating an execution time of said query by said online transaction processing engine using said determined approximate filtering;

estimating an execution time of said query by said database accelerator using said determined approximate filtering;

determining a wait time for said database accelerator to process said query; and

determining, by a processor, whether said online transaction processing engine or said database accelerator will process said query using said estimated execution time of said query by said online transaction processing engine, said estimated execution time of said query by said database accelerator and said wait time for said database accelerator to process said query.

2. The method as recited in claim 1 further comprising:

processing said query by said online transaction processing engine in response to said estimated execution time of said query by said online transaction processing engine not exceeding said estimated execution time of said query by said database accelerator plus said wait time for said database accelerator to process said query.

3. The method as recited in claim 1 further comprising:

processing said query by said database accelerator in response to said estimated execution time of said query by said online transaction processing engine exceeding said estimated execution time of said query by said database accelerator plus said wait time for said database accelerator to process said query.

4. The method as recited in claim 1 , wherein said wait time for said database accelerator to process said query is zero, wherein said query is processed by said online transaction processing engine in response to said estimated execution time of said query by said online transaction processing engine not exceeding said estimated execution time of said query by said database accelerator, wherein said query is processed by said database accelerator in response to said estimated execution time of said query by said online transaction processing engine exceeding said estimated execution time of said query by said database accelerator.

5. The method as recited in claim 1 further comprising:

identifying indexes that provide match filtering from local predicates as well as identifying tables that provide filtering for join operations; and

performing said index probe for next execution's literal values to determine approximate filtering for each subsequent execution.

6. The method as recited in claim 5 further comprising:

validating said real-time statistics for tables identified as filtering for join operations to determine a current size of object.

7. The method as recited in claim 1 further comprising:

estimating one or more of the following using heuristic rules and cost modeling in response to said literals of said query statement not being known: said execution time of said query by said online transaction processing engine and said execution time of said query by said database accelerator.

8. A computer program product for processing queries in a hybrid database system, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code comprising the programming instructions for:

receiving a query to be processed;

determining an approximate filtering for execution of said query by an online transaction processing engine and a database accelerator using information from an index probe and validated real-time statistics in response to literals of a query statement being known;

estimating an execution time of said query by said online transaction processing engine using said determined approximate filtering;

estimating an execution time of said query by said database accelerator using said determined approximate filtering;

determining a wait time for said database accelerator to process said query; and

determining whether said online transaction processing engine or said database accelerator will process said query using said estimated execution time of said query by said online transaction processing engine, said estimated execution time of said query by said database accelerator and said wait time for said database accelerator to process said query.

9. The computer program product as recited in claim 8 , wherein the program code further comprises the programming instructions for:

processing said query by said online transaction processing engine in response to said estimated execution time of said query by said online transaction processing engine not exceeding said estimated execution time of said query by said database accelerator plus said wait time for said database accelerator to process said query.

10. The computer program product as recited in claim 8 , wherein the program code further comprises the programming instructions for:

processing said query by said database accelerator in response to said estimated execution time of said query by said online transaction processing engine exceeding said estimated execution time of said query by said database accelerator plus said wait time for said database accelerator to process said query.

11. The computer program product as recited in claim 8 , wherein said wait time for said database accelerator to process said query is zero, wherein said query is processed by said online transaction processing engine in response to said estimated execution time of said query by said online transaction processing engine not exceeding said estimated execution time of said query by said database accelerator, wherein said query is processed by said database accelerator in response to said estimated execution time of said query by said online transaction processing engine exceeding said estimated execution time of said query by said database accelerator.

12. The computer program product as recited in claim 8 , wherein the program code further comprises the programming instructions for:

identifying indexes that provide match filtering from local predicates as well as identifying tables that provide filtering for join operations; and

performing said index probe for next execution's literal values to determine approximate filtering for each subsequent execution.

13. The computer program product as recited in claim 12 , wherein the program code further comprises the programming instructions for:

validating said real-time statistics for tables identified as filtering for join operations to determine a current size of object.

14. The computer program product as recited in claim 8 , wherein the program code further comprises the programming instructions for:

estimating one or more of the following using heuristic rules and cost modeling in response to said literals of said query statement not being known: said execution time of said query by said online transaction processing engine and said execution time of said query by said database accelerator.

15. A system, comprising:

a memory unit for storing a computer program for processing queries in a hybrid database system; and

a processor coupled to the memory unit, wherein the processor is configured to execute the program instructions of the computer program comprising:

receiving a query to be processed;

determining an approximate filtering for execution of said query by an online transaction processing engine and a database accelerator using information from an index probe and validated real-time statistics in response to literals of a query statement being known;

estimating an execution time of said query by said online transaction processing engine using said determined approximate filtering;

estimating an execution time of said query by said database accelerator using said determined approximate filtering;

determining a wait time for said database accelerator to process said query; and

determining whether said online transaction processing engine or said database accelerator will process said query using said estimated execution time of said query by said online transaction processing engine, said estimated execution time of said query by said database accelerator and said wait time for said database accelerator to process said query.

16. The system as recited in claim 15 , wherein the program instructions of the computer program further comprise:

processing said query by said online transaction processing engine in response to said estimated execution time of said query by said online transaction processing engine not exceeding said estimated execution time of said query by said database accelerator plus said wait time for said database accelerator to process said query.

17. The system as recited in claim 15 , wherein the program instructions of the computer program further comprise:

processing said query by said database accelerator in response to said estimated execution time of said query by said online transaction processing engine exceeding said estimated execution time of said query by said database accelerator plus said wait time for said database accelerator to process said query.

18. The system as recited in claim 15 , wherein said wait time for said database accelerator to process said query is zero, wherein said query is processed by said online transaction processing engine in response to said estimated execution time of said query by said online transaction processing engine not exceeding said estimated execution time of said query by said database accelerator, wherein said query is processed by said database accelerator in response to said estimated execution time of said query by said online transaction processing engine exceeding said estimated execution time of said query by said database accelerator.

19. The system as recited in claim 15 , wherein the program instructions of the computer program further comprise:

identifying indexes that provide match filtering from local predicates as well as identifying tables that provide filtering for join operations; and

performing said index probe for next execution's literal values to determine approximate filtering for each subsequent execution.

20. The system as recited in claim 19 , wherein the program instructions of the computer program further comprise:

validating said real-time statistics for tables identified as filtering for join operations to determine a current size of object.

Assignments (1)
ASSIGNMENT OF ASSIGNOR'S INTEREST Recorded Feb 20, 2018
From: CHAKRA, AL; LI, RUIPING; PURCELL, TERENCE P.; ZHOU, LILY
To: INTERNATIONAL BUSINESS MACHINES CORPORATION
Reel/Frame 044974/0392 →
Continuity (1)
Related Publication 20190258732A1 · Aug 22, 2019
Cited By (1)
US 12,380,099