Method and system for query optimization
The present teaching relates to methods and systems for query optimization by receiving a query stream comprising a plurality of queries from one or more applications via the network; determining relationships among the plurality of queries, wherein each of the relationships is associated with a prior probability; modeling the plurality of queries based on the relationships with the corresponding prior probabilities; re-writing at least one of the plurality of queries based on the model for the plurality of queries by, for each of the at least one of the plurality of queries generating a new query based on more than one query in the plurality of queries that are related via one of the relationships with a corresponding prior probability, and updating the corresponding prior probability based on the model of the plurality of queries.
1. A method implemented on a machine having at least one processor, storage, and communication platform connected to a network for optimizing query streams, comprising:
receiving, by a query optimizer, a query stream comprising a first plurality of queries from one or more applications via the network; and
in response to identifying in a storage that the first plurality of queries are different from a second plurality of queries of a previously optimized query stream,
determining, by the query optimizer, relationships among the first plurality of queries, wherein each of the relationships is associated with a prior probability;
generating, by the query optimizer, a predicted representation of the first plurality of queries based on the relationships with the corresponding prior probabilities;
selecting, by the query optimizer, based on the predicted representation, at least two queries from the received query stream, wherein each of the at least two queries includes multiple query statements;
re-writing, by the query optimizer, the at least two queries by combining the at least two queries into a single query to reduce a number of the first plurality of queries, wherein the single query includes at least part of content included in each of the least two queries and new content not in any of the at least two queries, and the single query has a fewer number of query statements than the total number of query statements included in the at least two queries;
updating, by the query optimizer, prior probabilities based on queries in the optimized query stream; and
communicating, by the query optimizer, with a remote database to send the optimized query stream to the remote database to reduce communication between the one or more applications and the remote database.
2. The method of claim 1 , wherein the received queries are associated with features provided by the applications.
3. The method of claim 1 , wherein the re-writing is processed by using a bank of transformation rules.
4. The method of claim 1 , wherein the re-writing is processed by a library between the application programming interface and the database, and the library being Java database connectivity (JDBC) fusion.
5. The method of claim 1 , further comprising:
in response to identifying in the storage that if the first plurality of queries are same as the second plurality of queries,
retrieving, without communicating with the remote database, from a local database of the query optimizer, data that is returned by the remote database according to the previously optimized query stream and is stored by the local database; and
outputting the retrieved data.
6. A system, having at least one processor, storage, and a communication platform connected to a network for optimizing query streams, comprising:
a query receiver implemented by a processor and configured for receiving a query stream comprising a first plurality of queries from one or more applications via the network;
a query relationship determiner implemented by a processor and configured for in response to identifying in a storage that the first plurality of queries are different from a second plurality of queries of a previously optimized query stream, determining relationships among the first plurality of queries, wherein each of the relationships is associated with a prior probability;
a query simulator implemented by a processor and configured for in response to identifying in the storage that the first plurality of queries are different from the second plurality of queries, generating a predicted representation of the first plurality of queries based on the relationships with the corresponding prior probabilities;
a query writing module implemented by a processor and configured for in response to identifying in the storage that the first plurality of queries are different from the second plurality of queries,
selecting, based on the predicted representation, at least two queries from the received query stream, wherein each of the at least two queries includes multiple query statements, and
re-writing the at least two queries by combining the at least two queries into a single query to reduce a number of the first plurality of queries, wherein the single query includes at least part of content included in each of the least two queries and new content not in any of the at least two queries, and the single query has a fewer number of query statements than the total number of query statements included in the at least two queries;
a query updater implemented by a processor and configured for in response to identifying in the storage that the first plurality of queries are different from the second plurality of queries, updating prior probabilities based on queries in the optimized query stream; and
an optimized query stream generator implemented by a processor and configured for in response to identifying in the storage that the first plurality of queries are different from the second plurality of queries, communicating with a remote database to send the optimized query stream to the remote database to reduce communication between the one or more applications and the remote database.
7. The system of claim 6 , wherein the received queries are associated with features provided by the applications.
8. The system of claim 6 , wherein the re-writing is processed by using a bank of transformation rules.
9. The system of claim 6 , wherein the re-writing is processed by a library of Java database connectivity (JDBC) fusion between the applications and the database.
10. The system of claim 6 , wherein the query receiver is implemented by a processor and further configured for in response to identifying in the storage that the first plurality of queries are same as the second plurality of queries,
retrieving, without communicating with the remote database, from a local database of the query optimizer, data that is returned by the remote database according to the previously optimized query stream and is stored by the local database; and
outputting the retrieved data.
11. A machine-readable tangible and non-transitory medium having information for database operation, when read by the machine, causes the machine to perform the following:
receiving a query stream comprising a first plurality of queries from one or more applications via the network; and
in response to identifying in a storage that the first plurality of queries are different from a second plurality of queries of a previously optimized query stream,
determining relationships among the first plurality of queries, wherein each of the relationships is associated with a prior probability;
generating a predicted representation of the first plurality of queries based on the relationships with the corresponding prior probabilities;
selecting, based on the predicted representation, at least two queries from the received query stream, wherein each of the at least two queries includes multiple query statements;
re-writing the at least two queries by combining the at least two queries into a single query to reduce a number of the first plurality of queries, wherein the single query includes at least part of content included in each of the least two queries and new content not in any of the at least two queries, and the single query has a fewer number of query statements than the total number of query statements included in the at least two queries;
updating prior probabilities based on queries in the optimized query stream; and
communicating with a remote database to send the optimized query stream to the remote database to reduce communication between the one or more applications and the remote database.
12. The medium of claim 11 , wherein the received queries are associated with features provided by the applications.
13. The medium of claim 11 , wherein the re-writing is processed by using a bank of transformation rules.
14. The medium of claim 11 , wherein the re-writing is processed by a library between the application programming interface and the database, and the library being Java database connectivity (JDBC) fusion.
15. The medium of claim 11 , wherein the information, when read by the machine, causes the machine to further perform the following:
if the first plurality of queries are same as the second plurality of queries,
retrieving, without communicating with the remote database, from a local database of the machine, data that is returned by the remote database according to the previously optimized query stream and is stored by the local database; and
outputting the retrieved data.
16. The method of claim 1 , wherein a number of queries included in the optimized query stream is less than a number of queries included in the selected subset of queries.
17. The method of claim 1 , wherein the new content comprises a new statement that is not included in any of the at least two queries.