Data base optimization method for estimating query and trigger procedure costs
First Claim
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.
1 Assignment
0 Petitions
Accused Products
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.
-
Citations
18 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. - View Dependent Claims (2, 3, 4, 5, 6)
-
-
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. - View Dependent Claims (8, 9, 10, 11, 12)
-
-
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. - View Dependent Claims (14, 15, 16, 17, 18)
-
Specification