Exploiting conditions to optimize expensive database queries
A fallback query mechanism that serves as a way to recover from query conditions (or criteria) that break due to excessively large result sets relative to a preset threshold. The fallback query mechanism detects when a query has failed to execute because the query requested more items from the database (or list) than allowed by the threshold, rewrites the query to examine no more than the threshold number of most recently created items that match one of the indexed filter criteria specified in a view, and then applies one or more of the other filter criteria. The mechanism then reruns the query and displays the results to the user.
1. A computer-implemented query system, comprising:
a processor for executing computer-executable components; and memory storing computer-executable components including:
a detection component for detecting a failure condition when a number of items to be returned from a data source in response to an original query exceeds a maximum number of items that can be returned from the data source and for terminating query execution processing of the original query against the data source; and
a query component for automatically returning, in response to the detected failure condition, a partial result set having a number of items that does not exceed the maximum number of items by:
automatically selecting a particular query term from a plurality of query terms specified in the original query,
automatically generating a reduced result set limited to a threshold number of most recently created items of the data source that match the particular query term selected from the original query, wherein the threshold number does not exceed the maximum number of items,
automatically generating the partial result set from the reduced result set by applying one or more remaining query terms specified in the original query to filter the reduced result set, and
automatically returning the partial result set for viewing.
2. The system of claim 1 , wherein:
the detection component and the query component are part of an enterprise web-based collaboration and data management server, and
the data source is an enterprise database.
3. The system of claim 1 , wherein the query component further applies one or more remaining query terms specified in the original query to order the partial result set.
4. The system of claim 3 , wherein the one or more remaining query terms specified in the original query to order the partial result set comprise one or more of groups and sorts.
5. The system of claim 1 , wherein the threshold number is a set percentage of the maximum number of items that can be returned from the data source.
6. The system of claim 1 , further comprising an optimization component for selecting the particular query term from the plurality of query terms specified in the original query to optimize quality of the partial result set.
7. The system of claim 1 , further comprising an optimization component for automatically optimizing quality of the partial result set based on one or more terms which are not specified in the original query.
8. A computer-implemented method, comprising:
detecting, by a server computer, a failure condition when a number of items to be returned from a data source in response to an original query exceeds a maximum number of items that can be returned from the data source;
terminating, by the server computer, query execution processing of the original query against the data source; and
automatically returning, by the server computer in response to the detected failure condition, a partial result set having a number of items that does not exceed the maximum number of items by:
automatically selecting a particular query term from a plurality of query terms specified in the original query,
automatically generating a reduced result set limited to a threshold number of most recently created items of the data source that match the particular query term selected from the original query, wherein the threshold number does not exceed the maximum number of items,
automatically generating the partial result set from the reduced result set by applying one or more remaining query terms specified in the original query to filter the reduced result set, and
automatically returning the partial result set for viewing.
9. The method of claim 8 , wherein the threshold number is a set percentage of the maximum number of items that can be returned from the data source.
10. The method of claim 8 , further comprising:
selecting, by the server computer, the particular query term from the plurality of query terms specified in the original query to optimize quality of the partial result set.
11. The method of claim 8 , further comprising:
automatically optimizing, by the server computer, quality of the partial result set based on one or more terms which are not specified in the original query.
12. The method of claim 8 , further comprising:
applying, by the server computer, one or more remaining query terms specified in the original query to order the partial result set.
13. The method of claim 12 , wherein the one or more remaining query terms specified in the original query to order the partial result set comprise one or more of groups and sorts.
14. The method of claim 8 , wherein the particular query term selected from the original query is an indexed equality clause.
15. A computer storage medium storing computer-executable instructions that, when executed, cause a computing device to perform steps comprising:
detecting a failure condition when a number of items to be returned from a data source in response to an original query exceeds a maximum number of items that can be returned from the data source;
terminating query execution processing of the original query against the data source; and
automatically returning, in response to the detected failure condition, a partial result set having a number of items that does not exceed the maximum number of items by:
automatically selecting a particular query term from a plurality of query terms specified in the original query,
automatically generating a reduced result set limited to a threshold number of most recently created items of the data source that match the particular query term selected from the original query, wherein the threshold number does not exceed the maximum number of items,
automatically generating the partial result set from the reduced result set by applying one or more remaining query terms specified in the original query to filter the reduced result set, and
automatically returning the partial result set for viewing.
16. The computer storage medium of claim 15 , wherein the threshold number is a set percentage of the maximum number of items that can be returned from the data source.
17. The computer storage medium of claim 15 , further storing computer-executable instructions for selecting the particular query term from the plurality of query terms specified in the original query to optimize quality of the partial result set.
18. The computer storage medium of claim 15 , further storing computer-executable instructions for automatically optimizing quality of the partial result set based on one or more terms which are not specified in the original query.
19. The computer storage medium of claim 15 , further storing computer-executable instructions for applying one or more remaining query terms specified in the original query to order the partial result set.
20. The computer storage medium of claim 19 , wherein the one or more remaining query terms specified in the original query to order the partial result set comprise one or more of groups and sorts.