Query runtime estimation using statistical query records

• US 7,356,524 B2
• Filed: 05/13/2005
• Issued: 04/08/2008
• Est. Priority Date: 05/13/2005
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Dipper, Stefan, Nagel, Klaus, Zurek, Thomas
• Primary Examiner(s): Vital; Pierre
• Assistant Examiner(s): Nofal; Christopher P
• Application Number:
  US11/129,139
• Publication Number:
  US 20060259460A1
• Time in Patent Office: 1,061 Days
• Field of Search: 706/2, 707/2, 707/3, 707/4, 707/5, 707/6, 707/7, 707/8, 707/10, 707/100, 707/102, 707/103.R, 707/104.1, 709/224
• US Class Current: 707/720
• CPC Class Codes: G06F 16/903 Querying for retrieval from the web G06F16/953
• CPC Class Codes: Y10S 707/944 Business related
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A method for estimating a runtime of a query of a data repository may abstract, extract, or otherwise generate one or more parameters of the query. Thereafter, the abstracted parameters may be associated with one or more statistical query records. From these statistical query records, a query runtime may be estimated. Related apparatuses, computer program products, and computer systems are also described.

Claims

1. A computer-implemented method comprising:

abstracting one or more parameters of a data repository query by extracting at least a grouping column, a table, and a query filter to form a query abstraction, wherein the one or more parameters include the query filter, wherein abstracting determines a most selective condition within the query filter and determines a selectivity of the most selective condition;

associating the abstracted parameters with one or more statistical query records, wherein associating further comprises identifying a statistical query record that includes the query filter condition matching the determined most selective condition and the query filter selectivity that falls within a predetermined range of the determined selectivity; and

providing, to a user interface for presentation, an estimate of query runtime for the query abstraction, the estimate determined based on the associated one or more statistical query records.

2. A method as in claim 1, wherein the providing further comprises sending to a user interface a message including the estimate.

3. A method as in claim 1, further comprising displaying, at the user interface, the estimate to a user.

4. A method as in claim 1, wherein the one or more abstracted parameters comprise one or more of the following:

set of grouping columns, a set of tables, infocubes, or query filters, the infocube comprising a multi-dimensional data structure.

5. A method as in claim 1, further comprising:

processing the query;

calculating the runtime of the query; and

generating a statistical query record for the query based on the calculated runtime.

6. A method as in claim 5, further comprising populating a library with the generated statistical query records.

7. A method as in claim 1, wherein the estimating further comprises:

providing one or more statistics based on the associated one or more statistical query records.

8. A method as in claim 7, wherein the one or more statistics are chosen from the group comprising:

average runtime, median runtime, minimum runtime, or maximum runtime.

9. A method as in claim 1, wherein the estimating further comprises:

discarding statistical query records having at least one of the longest and shortest runtimes;

wherein the query runtime estimation is based on the remaining associated one or more statistical query records.

10. A method as in claim 1, further comprising:

estimating a number of query result records associated with the query based on the associated one or more statistical query records.

11. A method as in claim 10, wherein the number of query result records is based on a number of facts relevant to the query.

12. A method as in claim 10, wherein the number of query result records is based on an expected result of the query.

13. A system comprising:

a processor; and

a memory, wherein the processor and memory are configured to provide an apparatus comprising;

an abstraction unit to abstract one or more parameters of a data repository query by extracting at least a grouping column, a table, and a query filter to form a query abstraction, wherein the one or more parameters include the query filter, wherein abstracting determines a most selective condition within the query filter and determines a selectivity of the most selective condition;

an association, to a user interface for presentation, unit to associate the abstracted parameters with one or more statistical query records, wherein associating further comprises identifying a statistical query record that includes the query filter condition matching the determined most selective condition and the query filter selectivity that falls within a predetermined range of the determined selectivity; and

an estimator to provide an estimate of query runtime for the query abstraction, the estimate determined based on the associated one or more statistical query records.

14. An apparatus as in claim 13, further comprising accessing the data repository.

15. An apparatus as in claim 13, further comprising:

a processor to process the query;

a calculation unit to calculate the runtime of the query; and

a record generator to generate a statistical query record for the query based on the calculated runtime.

16. An apparatus as in claim 15, further comprising a database storing a plurality of statistical query records including the statistical query record generated by the record generator.

17. An apparatus as in claim 13, wherein the one or more parameters include a query filter and further comprising:

a determination unit to determine a most selective condition within the query filter and to determine a selectivity of the most selective condition; and

an identification unit to identify a statistical query record that includes a filter condition matching the determined most selective condition and a filter selectivity that falls within a predetermined range of the determined selectivity.

18. A computer program product, embodied on computer readable-material, the computer program product including executable instructions for estimating a runtime of a query of a data repository that cause a computer system to provide a method comprising:

abstracting one or more parameters of the query by extracting at least a grouping column, a table, and a query filter to form a query abstraction, wherein the one or more parameters include the query filter, wherein abstracting determines a most selective condition within the query filter and determines a selectivity of the most selective condition;

associating the abstracted parameters with one or more statistical query records, wherein associating further comprises identifying a statistical query record that includes the query filter condition matching the determined most selective condition and the query filter selectivity that falls within a predetermined range of the determined selectivity; and

estimating the query runtime for the query abstraction, the estimate determined based on the associated one or more statistical query records.