Method and system for characterizing applications for use with databases having structured query language interfaces

• US 6,304,871 B1
• Filed: 12/18/1998
• Issued: 10/16/2001
• Est. Priority Date: 12/18/1998
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Johnson, Michael Joseph, Gajda, Kimberly Lynn, Thorpe, Robert Tod
• Primary Examiner(s): Black, Thomas
• Assistant Examiner(s): RONES, CHARLES
• Application Number:
  US09/216,613
• Time in Patent Office: 1,033 Days
• Field of Search: 707/2, 707/204, 707/103, 707/4, 707/5, 707/102, 345/355
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3419 by assessing time
• CPC Class Codes: G06F 11/3428 Benchmarking
• CPC Class Codes: G06F 11/3447 Performance evaluation by modeling
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• 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/99943 Generating database or data structure, e.g. via user interface

Abstract

A system and method for allowing a user to characterize an application are disclosed. In one aspect, the method and system allow a user to characterize a database engine. The database engine utilizes a particular interface for communicating with an application. The method and system include determining a query spectrum including plurality of queries corresponding to a plurality of query types. The plurality of query types are chosen such that any possible query can be classified as being of one query type. The plurality of queries is compatible with the particular interface. The method and system include running the query spectrum on the database engine and determining a time taken to run each of the plurality of queries on the database engine. In another aspect, the method and system are for characterizing the application. In this aspect, the method and system include characterizing the database engine using a query spectrum including a first plurality of queries compatible with the particular interface. The first plurality of queries has a plurality of query types. The method and system further include providing a second plurality of queries characteristic of the application. The second plurality of queries have a portion of the plurality of query types and are compatible with the particular interface. The method and system further include calculating a run time taken to run the second plurality of queries on a portion of the plurality of database engines based on the portion of the plurality of query types and characterization of the plurality of database engines.

Claims

1. A system for allowing a user to charcterize a database engine, the database engine utilizing a particular interface for communicating with an application, the system comprising:

a query spectrum including a plurality of queries, the plurality of queries corresponding to a plurality of query types chosen such that any possible query can be classified as being one query type, the plurality of queries including a plurality of joins, the plurality of query types including all possible join types for the plurality of joins, the plurality of queries being compatible with the particular interface, the plurality of query types being run on the database engine; and

a plurality of times corresponding to the plurality of query types, the plurality of times corresponding to a time taken to run a query on the database engine.

2. The system of claim 1 wherein the database engine is capable of utilizing a plurality of databases, each of the plurality of databases having a size, and wherein the plurality of times further includes:

a particular time taken to run each of the plurality of queries for each of the plurality of databases.

3. The system of claim 2 wherein the plurality of query types is based on the time taken to run a particular query of the query type.

4. A method for allowing a user to characterize a database engine, the database engine utilizing a particular interface for communication with an application, the method comprising the steps of:

(a) determining a query spectrum including a plurality of queries, the plurality of queries corresponding to a plurality of query types chosen such that any possible query can be classified as being one query type, the plurality of queries including a plurality of joins, the plurality of query types including all possible join types for the plurality of joins, the plurality of queries being compatible with the particular interface;

(b) running the query spectrum on the database engine; and

(c) determining a time taken to run each of the plurality of queries on the database engine.

5. The method of claim 4 wherein the database engine is capable of utilizing a plurality of databases, each of the plurality of databases having a size, and wherein the query spectrum running step (b) further includes the step of:

(b1) running the query spectrum on the database engine for each of the plurality of databases.

6. The method of claim 5 wherein the time determining step (c) fiter includes the step of:

(c1) determining a particular time taken to run each of the plurality of queries for each of the plurality of database.

16. The method of claim 5 wherein the run time adjusting step (c1) further includes the step of:

(c1i) adjusting the run time using a plurality of benchmarks corresponding to the portion of the plurality of machines.

7. A method for characterizing an application capable of being used with a database engine that utilizes a particular interface for communicating with the application, the method comprising the steps of:

(a) characterizing the database engine using a query spectrum including a first plurality of queries, the first plurality of queries being compatible with the particular interface, the first plurality of queries having a plurality of query types, wherein a query type of the plurality of query types has a characteristic time based on a query time to run a query having the query type;

(b) providing a second plurality of queries characteristic of the application, the second plurality of queries having a portion of the plurality of query types and being compatible with the particular interface; and

(c) calculating a run time taken to run the second plurality of queries on a portion of the plurality of database engines based on the portion of the plurality of query types and characterization of the plurality of database engines;

wherein the run time determining step (c) further includes the step of;

(c1) for each of the portion of the plurality of query types, counting a number of the second plurality of queries having a particular query type;

(c2) for each of the portion of the plurality of query types, taking the sum of the number multiplied by the characteristic time.

8. A method for characterizing an application capable of being used with a database engine that utilizes a particular interface for communicating with the application, the method comprising the steps of:

(a) characterizing the database engine using a query spectrum including a first plurality of queries, the first plurality of queries being compatible with the particular interface, the first plurality of queries having a plurality of query types, wherein a query type of the plurality of query types has a characteristic time based on a query time to run a query having the query type;

(b) providing a second plurality of queries characteristic of the application, the second plurality of queries having a portion of the plurality of query types and being compatible with the particular interface; and

(c) calculating a run time taken to run the second plurality of queries on a portion of the plurality of database engines based on the portion of the plurality of query types and characterization of the plurality of database engines;

wherein the database engine includes a tracing facility and wherein the step of providing the second plurality of queries (b) further includes the steps of;

(b1) performing a plurality of characteristic tasks on the database engine; and

(b2) using the tracing facility to determine the second plurality of queries.

9. A method for characterizing an application capable of being used with a database engine that utilizes a particular interface for communicating with the application, the method comprising the steps of:

(a) characterizing the database engine using a query spectrum including a first plurality of queries, the first plurality of queries being compatible with the particular interface, the first plurality of queries having a plurality of query types, wherein a query type of the plurality of query types has a characteristic time based on a query time to run a query having the query type;

(b) providing a second plurality of queries characteristic of the application, the second plurality of queries having a portion of the plurality of query types and being compatible with the particular interface; and

(c) calculating a run time taken to run the second plurality of queries on a portion of the plurality of database engines based on the portion of the plurality of query types and characterization of the plurality of database engines;

wherein the lun time determining step (c) further includes the step of;

(c1) counting a number of the second plurality of queires having a particular query type for each of the portion of the plurality of query types;

(c2) taking the sum of the number multiplied by the characteristic time for each of the portion of the plurality of query types.

10. A method for characterizing an application capable of being used with a database engine that utilizes a particular interface for communicating with the application, the method comprising the steps of:

(a) characterizing the database engine using a query spectrum including a first plurality of queries chosen such that any possible query can be classified as being one query type, the plurality of queries including a plurality of joins, the plurality of query types including all possible join types for the plurality of joins, the first plurality of queries being compatible with the particular interface, the first plurality of queries having a plurality of query types, the plurality of query types being run on the database engine;

(b) providing a second plurality of queries characteristic of the application, the second plurality of queries having a portion of the plurality of query types and being compatible with the particular interface; and

(c) calculating a run time taken to run the second plurality of queries on a portion of the plurality of database engines based on the portion of the plurality of query types and characterization of the plurality of database engines.

11. The method of claim 10 wherein the database engine includes a tracing facility and wherein the step of providing the second plurality of queries (b) further includes the steps of:

(b1) performing a plurality of characteristic tasks on the database engine; and

(b2) using the tracing facility to determine the second plurality of queries.

12. The method of claim 10 wherein a query type of the plurality of query types has a characteristic time based on a query time to run a query having the query type.

13. The method of claim 12 wherein the database engine is capable of utilizing a plurality of databases, each of the plurality of databases having a size, and wherein the database characterization step (a) further includes the step of:

(a1) running the query spectrum on the database engine for each of the plurality of databases.

14. The method of claim 13 wherein the run time determining step (c) further includes the step of:

(c1) calculating a particular time taken to run the second plurality of queries on the database engine at a particular database size based on the portion of the plurality of queay types and the characterization of the database engine.

15. The method of claim 12 wherein the database engine can be run on a plurality of machines and wherein the run time determining step (c) further includes the step of:

(c1) adjusting the run time to account for a portion of the plurality of the machines.