Predictive query parsing time and optimization

Patent metadata

Field	Value
Publication number	US-2020183936-A1
Application number	US-201816214280-A
Country	US
Kind code	A1
Filing date	Dec 10, 2018
Priority date	Dec 10, 2018
Publication date	Jun 11, 2020
Grant date	—

Abstract

A query is preprocessed for features identified by a Data Manipulation Language (DML) in the text of the query. A machine-learning algorithm uses the features as input and provides as output a predicted query parsing execution time needed by a query parser to parse the query. The predicted query parsing time is provided as input to a query optimizer. The query optimizer uses the predicted query parsing time as a factor in optimizing a query execution plan for the query. Subsequently, the query execution plan is executed against a database as the query.

First claim

1 . A method, comprising:
parsing a query for features present in the query; inputting the features as an input to a trained machine-learning algorithm; receiving as an output from the trained machine-learning algorithm an estimated amount of time necessary for a query parser to fully parse the query into database constructs; and processing the estimated amount of time and the database constructs and producing a query execution plan for the query that accounts for the estimated amount of time in combination with the database constructs.

2 . The method of claim 1 , wherein parsing further includes processing the parsing as a preprocessor of the query parser before the query parser fully parses the query into the database constructs.

3 . The method of claim 1 , wherein parsing further includes parsing the features as reserved syntax that identifies database operations for a given Data Manipulation Language (DML) format of the query.

4 . The method of claim 3 , wherein parsing further includes identifying the given DML format as a Structured Query Language (SQL) format.

5 . The method of claim 4 , wherein identifying further includes identifying semantics within the query representing database views, nested views, and join indexes.

6 . The method of claim 5 , wherein identifying further includes recording with the features a frequency with which each semantic appears within the query.

7 . The method of claim 1 , wherein receiving further includes receiving the output as a classification identifier that identifies a classification time range that the estimated amount of time falls within.

8 . The method of claim 1 , wherein receiving further includes receiving the estimated amount of time as an actual predicted time for parsing the query by the query parser.

9 . The method of claim 1 , wherein processing further includes passing the query to the query parser and the estimated amount of time to a query optimizer for the producing of the query execution plan.

10 . The method of claim 1 further comprising:
scheduling the query for execution at a scheduled time; and
processing the query execution plan for the query against a database at the scheduled time.

11 . The method of claim 1 further comprising:
obtaining an actual parsing execution time for parsing the query by the query parser; and
providing the actual parsing execution time with a query identifier for the query back to the trained machine-learning algorithm for dynamic learning and adjustment by the trained machine-learning algorithm based on the actual parsing execution time required by the query parser.

12 . A method, comprising:
iteratively training a machine-learning algorithm with features extracted from queries and with actual parsing execution times required by a query parser when parsing the queries into database constructs for further processing by a query optimizer and producing a trained machine-learning algorithm that when provided a candidate query with candidate features produces as output a predicted parsing execution time predicted for the query parser based on the candidate query; extracting specific features from a submitted query for execution against a database; inputting the specific features into the trained machine-learning algorithm; receiving a specific predicted parsing execution time back from the trained machine-learning algorithm; and producing a query execution plan for executing the submitted query based at least in part on the specific predicted parsing execution time provided by the trained machine-learning algorithm.

13 . The method of claim 12 , wherein iteratively training further includes training the machine-learning algorithm as a regression-based neural network.

14 . The method of claim 12 , wherein iteratively training further includes training the machine-learning algorithm as a classification-based neural network.

15 . The method of claim 12 , wherein producing further includes providing the submitted query to the query parser that produces database constructs from the submitted query and providing the specific predicted parsing execution time with the database constructs to a query optimizer for producing the query execution plan.

16 . The method of claim 12 , wherein providing further includes adjusting, by the query optimizer, the query execution plan based on a Service Level Agreement (SLA) associated with the submitted query in view of the specific predicted parsing execution time.

17 . The method of claim 12 further comprising, identifying an actual query execution time for executing the query execution plan and when the specific predicted parsing execution time and/or the actual query execution time exceeds a threshold amount of time, caching the query execution plan in memory.

18 . The method of claim 12 further comprising, identifying resources required by the query execution plan and based on the specific predicted parsing execution time and a total number of the resources performing one or more of:
scheduling the submitted query for execution to ensure a Service Level Agreement (SLA) for the submitted query is satisfied by the resources at a scheduled time;
dynamically increasing priorities for the resources to ensure the SLA is satisfied at the scheduled time; and
dynamically increase the total number of resources to ensure the SLA is satisfied.

19 . A database, comprising:
at least one hardware processor; a non-transitory computer-readable storage medium having executable instructions representing a query parsing time predictor, a query parser, and a query optimizer; the query parsing time predictor configured to execute on the at least one hardware processor from the non-transitory computer-readable storage medium and to perform processing to: extract features from a query, process the features to provide a predicted parsing execution time for the query parser to parse the query into database constructs, provide the query to the query parser, and provide the predicted parsing execution time to the query optimizer; the query parser configured to execute on the at least one hardware process from the non-transitory computer-readable storage medium and to perform processing to parse the query into the database constructs; and the query optimizer configured to execute on the at least one hardware processor from the non-transitory computer-readable storage medium and to perform processing to use the predicted parsing execution time along with the database constructions to produce a query execution plan for the query.

20 . The database of claim 19 , wherein query parsing time predictor is further configured to pass the features as input to a trained regress ion-based neural network or a trained classification-based neural network and receive the predicted parsing execution time as output from the trained regression-based neural network of the trained classification-based neural network.

Assignees

• Teradata Us Inc

Inventors

• Ramesh Bhashyam
• Chimanchode Jaiprakash Ganpatrao
• Sankaran Naveen Thaliyil
• Katta Jitendra Yasaswi Bharadwaj

Classifications

• G06N5/01

  Dynamic search techniques; Heuristics; Dynamic trees; Branch-and-bound · CPC title

• G06N3/09

  Supervised learning · CPC title

• G06N3/0499

  Feedforward networks · CPC title

• H04L41/5003

  Managing SLA; Interaction between SLA and QoS · CPC title

• H04L41/16

  using machine learning or artificial intelligence · CPC title