Systems and methods for dynamic query optimization
Systems, apparatuses, methods, and computer program products are disclosed for dynamic query optimization. An example method includes receiving, by communications hardware, a first query and determining, by a query analysis engine, whether the first query corresponds to a stored query of a historical query repository. The method also includes determining, in response to determining that the first query does not correspond to the stored query, determining, by the query analysis engine, a parameter set indicating a current state associated with a system involved in an execution of the first query, processing, by a reinforcement learning engine, the first query and the parameter set in accordance with a plurality of policies, selecting, by the reinforcement learning engine and based on the processing, a first policy from the plurality of policies, and generating, by the reinforcement learning engine and based on the first policy, a query execution plan for the first query.
1 . A method comprising:
receiving, by communications hardware, a query;
determining, by a query analysis engine, whether the query corresponds to a stored query of a historical query repository; and
in response to determining that the query does not correspond to the stored query:
determining, by the query analysis engine, a parameter set indicating a current state associated with a system involved in an execution of the query;
processing, by a reinforcement learning engine, the query and the parameter set in accordance with a plurality of policies, wherein each policy of the plurality of policies comprises a join algorithm, a join order, and a join type, and wherein processing the query and the parameter set in accordance with the plurality of policies comprises:
executing, by the reinforcement learning engine, the query in a first environment in accordance with at least one policy of the plurality of policies and the parameter set, and
determining, by the reinforcement learning engine and based on the execution, an execution metric set comprising one or more metrics captured from the execution of the query in the first environment;
selecting, by the reinforcement learning engine and based on the processing, a policy from the plurality of policies, wherein the policy is selected based on a resource utilization metric of the execution metric set; and
generating, by the reinforcement learning engine and based on the selected policy, a query execution plan for executing the query in a second environment, wherein the query execution plan is generated using the join algorithm, the join order, and the join type of the selected policy.
2 . The method of claim 1 , further comprising:
receiving, by the communications hardware, an execution results set, wherein the execution results set indicates results of an execution of the query in accordance with the query execution plan; and
training, by intelligent agent circuitry, the reinforcement learning engine using the execution results set.
3 . The method of claim 1 , wherein the execution metric set further comprises an execution time metric.
4 . The method of claim 1 , wherein the parameter set comprises one or more of:
hardware configuration data;
query load data;
table characteristic data; and
query characteristic data.
5 . The method of claim 4 , wherein the table characteristic data indicates one or more of a table size, complexity, cardinality, distribution, density, variability, and velocity of one or more tables indicated by the first query.
6 . The method of claim 1 , wherein the first query comprises one or more join clauses.
7 . The method of claim 1 , further comprising, in response to determining that the first query fully corresponds to a stored query of the historical query repository:
generating, by a transfer learning engine, a query execution plan for the first query based at least on a previous query execution plan associated with the stored query.
8 . The method of claim 1 , further comprising, in response to determining that the first query partially corresponds to a stored query of the historical query repository:
determining, by the query analysis engine, the parameter set indicating the current state associated with the system involved in the execution of the query;
receiving, by the communications hardware, a modified parameter set that is based on the parameter set;
processing, by the reinforcement learning engine, the query and the modified parameter set in accordance with at least a portion of the plurality of policies;
selecting, by the reinforcement learning engine and based on the processing, a policy from the portion of the plurality of policies; and
generating, by the reinforcement learning engine and based on the selected policy, a query execution plan for the query.
9 . An apparatus comprising:
communications hardware configured to receive a first-query;
a query analysis engine configured to:
determine whether the query corresponds to a stored query of a historical query repository, and
in response to determining that the query does not correspond to the stored query, determine a parameter set indicating a current state associated with a system involved in an execution of the query; and
a reinforcement learning engine configured to:
process the query and the parameter set in accordance with a plurality of policies, wherein each policy of the plurality of policies comprises a join algorithm, a join order, and a join type, and wherein processing the query and the parameter set in accordance with the plurality of policies comprises:
executing the query in a first environment in accordance with at least one policy of the plurality of policies and the parameter set, and
determining, based on the execution, an execution metric set comprising one or more metrics captured from the execution of the query in the first environment;
select, based on the processing, a policy from the plurality of policies, wherein the policy is selected based on a resource utilization metric of the execution metric set, and
generate, based on the selected policy, a query execution plan for executing the first query in a second environment, wherein the query execution plan is generated using the join algorithm, the join order, and the join type of the selected policy.
10 . The apparatus of claim 9 ,
wherein the communications hardware is further configured to receive an execution results set, wherein the execution results set indicates results of an execution of the first query in accordance with the query execution plan; and
wherein the apparatus further comprises intelligent agent circuitry configured to train the reinforcement learning engine using the execution results set.
11 . The apparatus of claim 9 , wherein the execution metric set further comprises an execution time metric.
12 . The apparatus of claim 9 , wherein the parameter set comprises one or more of
hardware configuration data;
query load data;
table characteristic data; and
query characteristic data.
13 . The apparatus of claim 12 , wherein the table characteristic data indicates one or more of a table size, complexity, cardinality, distribution, density, variability, and velocity of one or more tables indicated by the query.
14 . The apparatus of claim 9 , wherein the first-query comprises one or more join clauses.
15 . The apparatus of claim 9 , further comprising:
a transfer learning engine configured to generate, in response to determining that the query fully corresponds to a stored query of the historical query repository, a query execution plan for the query based at least on a previous query execution plan associated with the stored query.
16 . The apparatus of claim 9 , wherein the query analysis engine is further configured to, in response to determining that the query partially corresponds to a stored query of the historical query repository, determine the parameter set indicating the current state associated with the system involved in the execution of the query,
wherein the communications hardware is further configured to receive a modified parameter set that is based on the parameter set,
wherein the reinforcement learning engine is further configured to process the first query and the modified parameter set in accordance with at least a portion of the plurality of policies, wherein the reinforcement learning engine is further configured to select, based on the processing, a selected policy from the portion of the plurality of policies, and
wherein the reinforcement learning engine is further configured to generate, based on the selected policy, a query execution plan for the query.
17 . The method of claim 4 , wherein the hardware configuration data indicates one or more of a computer processing unit speed, a computer processing unit clock speed, a number of computer processing unit cores, a computer processing unit type, a computer processing unit size, a computer processing unit read speed, a computer processing unit write speed, an amount of available random access memory, an available network bandwidth, a cache size, a graphics processing unit availability, and a graphics processing unit specification.
18 . The method of claim 4 , wherein the query load data indicates at least one of a number of queries in a queue, an amount of demand placed on the system, an estimated execution time for one or more queries of the queries in the queue, historical query performance data, a historical resource usage, a historical execution time, and a volume of queries being received or processed at a given time.
19 . The apparatus of claim 12 , wherein the hardware configuration data indicates one or more of a computer processing unit speed, a computer processing unit clock speed, a number of computer processing unit cores, a computer processing unit type, a computer processing unit size, a computer processing unit read speed, a computer processing unit write speed, an amount of available random access memory, an available network bandwidth, a cache size, a graphics processing unit availability, and a graphics processing unit specification.
20 . The apparatus of claim 12 , wherein the query load data indicates at least one of a number of queries in a queue, an amount of demand placed on the system, an estimated execution time for one or more queries of the queries in the queue, historical query performance data, a historical resource usage, a historical execution time, and a volume of queries being received or processed at a given time.