Systems and methods for query optimization
A method for query optimization may include capturing a proposed database query input into a user interface. The method may further include providing the proposed database query to a machine-learning model. The machine-learning model may have been trained, using one or more gathered and/or simulated sets of query execution overhead data and one or more gathered and/or simulated sets of database queries, to determine a potential execution overhead of a database query and output a query execution score. The method may further include outputting, by the machine-learning model, the query execution score based on the proposed database query. The method may further include determining that the query execution score exceeds a query execution score threshold. The method may further include triggering a corrective action based on the query execution score exceeding the query execution score threshold.
1 . A computer-implemented method comprising:
receiving, by one or more processors, a proposed database query input into a user interface;
providing, by the one or more processors, the proposed database query to a machine-learning model, wherein the machine-learning model has been trained, using one or more sets of query execution overhead data and one or more sets of database queries, to determine a potential execution overhead of a database query and output a query execution score;
outputting, by the machine-learning model, the query execution score based on the proposed database query;
determining, by the one or more processors, that the query execution score exceeds a query execution score threshold; and
based on determining that the query execution score exceeds the query execution score threshold, generating, by the one or more processors, an optimized query from the proposed database query, wherein the optimized query has a lower execution overhead than the proposed database query.
2 . The computer-implemented method of claim 1 , further comprising:
obtaining an execution overhead resulting from executing the proposed database query; and
retraining the machine-learning model using the proposed database query and the execution overhead.
3 . The computer-implemented method of claim 1 , wherein the potential execution overhead comprises a predicted query workload placed upon a database system.
4 . The computer-implemented method of claim 1 , further comprising:
blocking, by the one or more processors, the proposed database query from being executed on a database.
5 . The computer-implemented method of claim 1 , wherein the generating the optimized query comprises:
identifying, by a natural language processor, one or more of a database update command or a database information request of the proposed database query; and
generating, by the natural language processor, an optimized database query including the one or more of the database update command and the database information request.
6 . The computer-implemented method of claim 1 , wherein the generating the optimized query comprises:
determining, by the one or more processors, that a threshold number of query execution scores exceeding the query execution score threshold has been reached; and
outputting, by the one or more processors, one or more sets of user training outputs to the user interface.
7 . The computer-implemented method of claim 1 , further comprising:
recording, by the one or more processors, data associated with a unique user that input the proposed database query into the user interface.
8 . A computer-implemented method comprising:
providing, by one or more processors, a proposed database query to a machine-learning model, wherein the machine-learning model has been trained, using one or more sets of query execution overhead data and one or more sets of database queries, to determine a potential execution overhead of a database query and output a query execution score;
outputting, by the machine-learning model, the query execution score based on the proposed database query;
determining, by the one or more processors, that the query execution score exceeds a query execution score threshold;
identifying, by a natural language processor, one or more formats of the proposed database query for restructuring; and
generating, by the natural language processor, an optimized database query including the one or more restructured formats.
9 . The computer-implemented method of claim 8 , wherein the generating the optimized database query further comprises:
restructuring query statements according to a set of syntax rules.
10 . The computer-implemented method of claim 8 , wherein the generating the optimized database query further comprises:
reformatting the proposed database query.
11 . The computer-implemented method of claim 8 , wherein the machine-learning model has been trained further on a history of triggered corrective actions.
12 . The computer-implemented method of claim 8 , further comprising:
determining, by the one or more processors, that a threshold number of query execution scores exceeding the query execution score threshold has been reached; and
outputting, by the one or more processors, one or more sets of user training outputs to a user interface.
13 . A system comprising:
a memory storing instructions and a machine-learning model that has been trained to find associations between one or more sets of query execution overhead data and one or more sets of database queries to determine a potential execution overhead of a database query and output a query execution score; and
a processor operatively connected to the memory and configured to execute the instructions to perform operations including:
receiving, by one or more processors, a proposed database query input into a user interface;
providing, by the one or more processors, the proposed database query to the machine-learning model;
outputting, by the machine-learning model, the query execution score based on the proposed database query;
determining, by the one or more processors, that the query execution score exceeds a query execution score threshold; and
based on determining that the query execution score exceeds the query execution score threshold, generating, by the one or more processors, an optimized query from the proposed database query, wherein the optimized query has in a lower execution overhead than the proposed database query.
14 . The system of claim 13 , the operations further comprising:
obtaining an execution overhead resulting from executing the proposed database query; and
retraining the machine-learning model using the proposed database query and the execution overhead.
15 . The system of claim 13 , wherein the potential execution overhead comprises a predicted query workload placed upon a database system.
16 . The system of claim 13 , wherein the query execution score is further based on a user of the proposed database query.
17 . The system of claim 13 , wherein the query execution score threshold is based on current traffic or workload on a database system.
18 . The system of claim 17 , wherein the generating the optimized query comprises:
identifying, by a natural language processor, one or more of a database update command or a database information request of the proposed database query; and
generating, by the natural language processor, an optimized database query including the one or more of the database update command and the database information request.
19 . The system of claim 13 , wherein the generating the optimized query comprises:
determining, by the processor, that a threshold number of query execution scores exceeding the query execution score threshold has been reached; and
outputting, by the processor, one or more sets of user training outputs to the user interface.
20 . The system of claim 13 , wherein the machine-learning model has been further trained on user historical data.