Data base optimization method for estimating query and trigger procedure costs

• US 6,212,514 B1
• Filed: 07/31/1998
• Issued: 04/03/2001
• Est. Priority Date: 07/31/1998
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Eberhard, Rainer, Hall, Harold H. Jr., Youngberg, Jon A.
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/127,292
• Time in Patent Office: 977 Days
• Field of Search: 707/2, 707/3, 707/4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination
• CPC Class Codes: G06F 16/24565 Triggers; Constraints
• 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

Abstract

The method of the invention estimates a total processing cost incident to execution of a query statement in respect of a relational database, wherein execution of the query statement results in a further execution of a trigger procedure. The method first estimates the query statement'"'"'s costs by analyzing components of the query statement to determine a processing time there for in relation to a database table that is the target of the query statement. The method then determines the number of rows that are affected in the database table as a result of execution of the query statement. Next, components of a trigger procedure that is invoked in response to execution of the query statement are analyzed to determine a “base cost” for a single execution of the trigger procedure visa-vis data that is contained in a single row of the database table. The components of the trigger procedure are next analyzed to determine a per row cost for execution of the trigger procedure. The per row cost is determined from an estimate of time required for execution of the trigger procedure for a single row and from an estimate of time required for execution of the trigger procedure for a plurality of rows. Thereafter, the total processing cost of a query statement is calculated by adding the query statement cost to a sum of the either (i) the base cost if only one row is subjected to the query statement or (ii) the per row cost, multiplied by the number of rows affected by the query statement.

Claims

1. A method for estimating a total processing cost for executing a query statement with respect to a relational database, wherein execution of said query statement results in a further execution of a trigger procedure, said method comprising the steps of:

estimating a query cost of said query statement in relation to a database table that is a target of said query statement;

determining a number of rows that are affected in said database table as a result of execution of said query statement;

analyzing components of a trigger procedure that is invoked in response to execution of said query statement to determine a base cost for a single execution of said trigger procedure in relation to data contained in a single row of said database table;

further analyzing components of said trigger procedure to determine a per row cost for execution of said trigger procedure; and

calculating said total processing cost by adding said query cost to a sum of said base cost, plus said per row cost multiplied by the number of rows affected by said query statement, as determined by said determining step.

2. The method as recited in claim 1, wherein said estimating of a query cost of said query statement is performed by analyzing components of said query statement to determine, at least, a processing time there for.

3. The method as recited in claim 2, wherein said per row cost is determined from an estimate of time required for execution of said trigger procedure for a single row of said database table and from an estimate of time required for execution of said trigger procedure for a plurality of rows of said database table.

4. The method as recited in claim 3, wherein said base cost includes both central processing time and input/output processing time.

5. The method as recited in claim 1, wherein said per row cost is determined from a function relating said estimate of time required for execution of said trigger procedure for a single row of said database table and said estimate of time required for execution of said trigger procedure for a plurality of rows of said database table.

6. The method as recited in claim 5, wherein said function is a straight line connecting said estimate for a single row and said estimate for said plurality of rows.

7. A memory media for controlling a computer to estimate a total processing cost to execute a query statement with respect to a relational database, wherein execution of said query statement results in a further execution of a trigger procedure, said memory media comprising:

a) means for controlling said computer to estimate a query cost of a query statement in relation to a database table that is a target of said query statement;

b) means for controlling said computer to determine a number of rows that are affected in said database table, as a result of execution of said query statement;

c) means for controlling said computer to analyze components of a trigger procedure that are invoked in response to execution of said query statement to determine a base cost for a single execution of said trigger procedure in relation to data contained in a single row of said database table;

d) means for controlling said computer to further analyze components of said trigger procedure to determine a per row cost for execution of said trigger procedure; and

e) means for controlling said computer to calculate said total processing cost by adding said query cost to a sum of said base cost plus said per row cost multiplied by the number of rows affected by execution of said query statement, as determined by said determining step.

8. The memory media as recited in claim 7, wherein said means a) estimates a query cost of said query statement by analyzing components of said query statement to determine, at least, a processing time there for.

9. The memory media as recited in claim 8, wherein said means d) determines said per row cost from an estimate of time required for execution of said trigger procedure for a single row of said database table and from an estimate of time required for execution of said trigger procedure for a plurality of rows of said database table.

10. The memory media as recited in claim 9, wherein said base cost includes both central processing time and input/output processing time.

11. The memory media as recited in claim 7, wherein said per row cost is determined from a function relating said estimate of time required for execution of said trigger procedure for a single row of said database table and said estimate of time required for execution of said trigger procedure for a plurality of rows of said database table.

12. The memory media as recited in claim 11, wherein said function is a straight line connecting said estimate for a single row and said estimate for said plurality of rows.

13. A system for estimating a total processing cost for executing a query statement with respect to a relational database, wherein execution of said query statement results in a further execution of a trigger procedure, said system comprising:

a relational database including data organized into logical tables;

memory means for storing an estimator procedure for controlling a processor to estimate said total processing cost of a query statement and said trigger procedure; and

a processor, which, in combination with said estimator procedure;

estimates a query cost of said query statement in relation to a database table that is a target of said query statement;

determines a number of rows that are affected in said database table as a result of execution of said query statement;

analyzes components of a trigger procedure that is invoked in response to execution of said query statement to determine a base cost for a single execution of said trigger procedure in relation to data contained in a single row of said database table;

further analyzes components of said trigger procedure to determine a per row cost for execution of said trigger procedure; and

calculates said total processing cost by adding said query cost to a sum of said base cost, plus said per row cost multiplied by the number of rows affected by said query statement.

14. The system as recited in claim 13, wherein said estimate of a query cost of said query statement is performed by analyzing components of said query statement to determine, at least, a processing time there for.

15. The system as recited in claim 14, wherein said per row cost is determined from an estimate of time required for execution of said trigger procedure for a single row of said database table and from an estimate of time required for execution of said trigger procedure for a plurality of rows of said database table.

16. The system as recited in claim 15, wherein said base cost includes both central processing time and input/output processing time.

17. The system as recited in claim 13, wherein said per row cost is determined from a function relating said estimate of time required for execution of said trigger procedure for a single row of said database table and said estimate of time required for execution of said trigger procedure for a plurality of rows of said database table.

18. The system as recited in claim 17, wherein said function is a straight line connecting said estimate for a single row and said estimate for said plurality of rows.