Affecting database file performance by allowing delayed query language trigger firing

Embodiments of the invention provide techniques for processing database triggers having delay attributes. In general, delay attributes may selectively introduce a delay between the firing of a trigger and the execution of the triggered action. The delay may be based on waiting for a specified time interval, waiting until a specified time, or reaching a predetermined threshold of a measure of system performance. The use of delay attributes may enable greater control over the timing of the execution of the triggered action, resulting in reduced impact on the performance of an underlying system.

BACKGROUND OF THE INVENTION

1. Field of the Invention

The invention generally relates to computer database systems. More particularly, the invention relates to techniques for delayed firing of database triggers.

2. Description of the Related Art

Databases are well known systems for storing, searching, and retrieving information stored in a computer. The most prevalent type of database used today is the relational database, which stores data using a set of tables that may be reorganized and accessed in a number of different ways. Users access information in relational databases using a relational database management system (DBMS).

In some cases, a table is manipulated by the use of triggers. Triggers are procedures that are defined by users of the DBMS. The DBMS invokes triggers when particular events (associated with the triggers) occur. Specifically, an SQL trigger program is typically written to take some action due to an event, such as an insert, update, or delete operation against an SQL table in a database. For example, in the case of an insert operation, a trigger can be defined such that it is invoked each time a row is inserted into a particular SQL table. A trigger can instruct the system to take any number of actions when a specified change is attempted.

Conventionally, one problem with the use of triggers is that they can result in large demands for database processing, which can impact the overall performance of a system which includes the database. For example, a trigger may be configured to send an email to each person included in a new database record. In the event that a large number of records are being added to the database, the delay caused by sending each person an email can result in a long delay in completing the creation of the new records.

Therefore, there is a need for improved techniques for processing database triggers.

SUMMARY OF THE INVENTION

Embodiments of the invention generally provide techniques for processing database triggers.

One embodiment of the invention provides a computer-implemented method for controlling the firing of database triggers, comprising: receiving an I/O event affecting a database trigger; identifying one or more delay conditions defined for the database trigger; and delaying the firing of the database trigger until the one or more identified delay conditions are satisfied.

Another embodiment of the invention provides a computer-readable storage medium containing a program which, when executed, performs an operation. The operation comprises: receiving an I/O event affecting a database trigger; identifying one or more delay conditions defined for the database trigger; and delaying the firing of the database trigger until the one or more identified delay conditions are satisfied.

Yet another embodiment of the invention provides a system, comprising: a processor; and a memory containing a program configured to compose a query of hierarchical data by performing an operation. The operation comprises: receiving an I/O event affecting a database trigger; identifying one or more delay conditions defined for the database trigger; and delaying the firing of the database trigger until the one or more identified delay conditions are satisfied.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Embodiments of the invention provide techniques for processing database triggers having delay attributes. In general, delay attributes may selectively introduce a delay between the firing of a trigger and the execution of the triggered action. The delay may be based on waiting for a specified time interval, waiting until a specified time, or reaching a predetermined threshold of a measure of system performance. The use of delay attributes may enable greater control over the timing of the execution of the triggered action, resulting in reduced impact on the performance of an underlying system. That is, by delaying the processing of triggers until a point when there is less demand on a system, there may be increased system efficiency.

FIG. 1is a block diagram that illustrates a client server view of computing environment100, according to one embodiment of the invention. As shown, computing environment100includes two client computer systems110and112, network115and server system120. In one embodiment, the computer systems illustrated in environment100may include existing computer systems, e.g., desktop computers, server computers laptop computers, tablet computers, and the like. The computing environment100illustrated inFIG. 1, however, is merely an example of one computing environment. Embodiments of the present invention may be implemented using other environments, regardless of whether the computer systems are complex multi-user computing systems, such as a cluster of individual computers connected by a high-speed network, single-user workstations, or network appliances lacking non-volatile storage. Further, the software applications illustrated inFIG. 1and described herein may be implemented using computer software applications executing on existing computer systems, e.g., desktop computers, server computers, laptop computers, tablet computers, and the like. However, the software applications described herein are not limited to any currently existing computing environment or programming language, and may be adapted to take advantage of new computing systems as they become available.

As shown, client computer systems110and112each include a CPU102, storage114and memory106, typically connected by a bus (not shown). CPU102is a programmable logic device that performs all the instruction, logic, and mathematical processing in a computer. Storage104stores application programs and data for use by client computer systems110and112. Storage104includes hard-disk drives, flash memory devices, optical media and the like. The network115generally represents any kind of data communications network. Accordingly, the network115may represent both local and wide area networks, including the Internet.

The client computer systems110and112are also shown to include a query tool108. In one embodiment, the query tool108is software application that allows end users to access information stored in a database (e.g., database140). Accordingly, the query tool108may allow users to compose and submit a query to a database system, which, in response, may be configured to process the query and return a set of query results. The query tool108may be configured to compose queries in a database query language, such as Structured Query Language (SQL). However, it should be noted that the query tool108is only shown by way of example; any suitable requesting entity may submit a query (e.g., another application, an operating system, etc.).

In one embodiment, the server120includes a CPU122, storage124, memory126, a database140, and a database management system (DBMS)130. As shown, the database140may include data142and triggers144. The data142represents the substantive data stored by the database140. At various times, elements of the database140may be present in storage124and memory126.

In one embodiment, the triggers144may be defined by users of the database140. In one embodiment, triggers144may be created and maintained by users interacting with a graphical user interface (GUI) configured for this purpose. It is contemplated that the triggers144may also be created and maintained by any other suitable technique, for example the “CREATE TRIGGER” command available in the SQL database command language.

Each trigger144may include a firing condition and a triggered action. Typically, a firing condition specifies an input/output (I/O) event occurring in the database140which will cause the trigger144to be “fired” (i.e., activated). Such I/O events include an INSERT event (a new record is inserted into the database), an UPDATE event (an existing record is changed), or a DELETE event (an existing record is deleted). Generally, the firing of a trigger144causes the triggered action to be executed. For example, a trigger144may be configured to fire on an INSERT event of records of a customer database table, and to then perform the triggered action of sending individual emails to customers included in the inserted records.

In one embodiment, the triggers144include delay attributes146, which serve to delay the execution of the triggered action after the firing of a trigger144. One potential use of delay attributes146may be to postpone the processing of a trigger144so as to minimize any impact on the performance of an underlying system (e.g., server120). That is, by delaying the processing of triggers until a point when there is less demand on a system, there may be increased system efficiency.

The delay attributes146may be specified at the time of creating a trigger144, or may be added and/or modified at a later time. In one embodiment, the delay attributes146specify a time interval that must elapse between the firing of the trigger144and the resulting triggered action. For example, an exemplary trigger may include a delay attribute specifying a time interval of two hours. Thus, in the event the exemplary trigger is fired, the triggered action will not occur until two hours later.

In another embodiment, the delay attributes146may specify a start time and/or date for performing the triggered action. For example, a second exemplary trigger may include a delay attribute specifying a start time of 1 A.M. Thus, in the event the second exemplary trigger is fired, the triggered action will not occur until the next instance of a time of 1 A.M.

In yet another embodiment, the delay attributes146may specify that a triggered action will not be executed in a system that is performing at reduced performance due to, for example, a large system workload. More specifically, an exemplary trigger may be configured so that, in the event it is fired, the resulting triggered action may not be executed as long as a given measure of the performance of the database140and/or the server system120is below a predetermined threshold. The performance of the database140may be measured, for example, by database throughput, response time, cost per transaction, and the like. The performance of the server system120may be measured, for example, by CPU utilization, memory utilization, page faults, and the like. Of course, these measures of database and system performance are provided for illustrative purposes only. It is contemplated that any suitable performance measures may be used instead of, or in combination with, the above examples. It is also contemplated that the above embodiments may be used in combination, such as a combination of delay attributes related to time and performance measure. For example, a trigger may have delay attributes that cause a time delay of two hours, and then delay execution until a performance measure falls below a predefined threshold.

The DBMS130provides a software application used to organize, analyze, and modify information stored in the database140. As shown, the DBMS130includes a query engine132, a trigger engine134and a delay queue136. The query engine132may be configured to process database queries submitted by a requesting application (e.g., a query generated using query tool108) and to return a set of query results to the requesting application. In one embodiment, the trigger engine134may be configured to monitor the triggers144to determine if a trigger has been activated and, if so, to manage the execution of the triggered action. The execution of a triggered action may be delayed by the trigger engine134according to the delay attributes146included in the activated trigger144. More specifically, the trigger engine134may be configured to hold the activated trigger144in the delay queue136until the requirements specified in the delay attributes146are satisfied, and thereafter execute the triggered action.

FIG. 2illustrates a display screen of a graphical user interface (GUI)200for specifying delay attributes of a database trigger, according to one embodiment of the invention. The GUI200may interface with, for example, the DBMS130illustrated inFIG. 1. In one embodiment, the GUI200may be viewed by a user of a server (e.g., server120shown inFIG. 1).

As shown, the GUI200includes a trigger selection210, delay type selections220, a time interval selection230, a start time selection235, a database performance measure selection240, a system performance measure selections245, and control buttons250. The control buttons250may enable the user to perform typical functions in the GUI200, such as executing commands, cancelling commands, and the like. The trigger selection210enables a user to select a particular trigger for which to specify delay attributes. In this example, the trigger selection210indicates that the delay attributes belong to a trigger named “TRIGGER_1”.

In one embodiment, a user of GUI200may select a delay type, and may then specify a corresponding delay condition that must be met to end the delay. As shown, the delay type selections220enable a user to specify delays based on a time interval, a start time, a database performance measure, or a system performance measure. In this illustration, the user has selected checkbox222, thus specifying a delay based on a time interval. Accordingly, the user has also entered a time value in the time interval selection230. The remaining selections235,240,245are left blank, since they are not used for a time interval delay. In this example, the user has specified a time interval selection230of “10:00:00,” meaning that 10 hours must elapse between the activation of the trigger “TRIGGER_1” and the execution of the triggered action. Of course, GUI200is provided for illustrative purposes only. It is contemplated that the delay attributes of a database trigger may be specified by other suitable techniques, such as a modification of the SQL command CREATE TRIGGER to receive delay attributes in command parameters.

FIG. 3illustrates a flow diagram illustrating a method300for creating database triggers having delay attributes, according to one embodiment of the invention. The method300begins at step310, when a new trigger definition is received. This step may result from, for example, a user defining a new trigger. The trigger definition may include a firing condition and a triggered action.

At step320, it is determined whether the trigger will have delay attributes. That is, whether the trigger will be configured such that, in the event the firing condition takes place, the triggered action will be delayed. This determination may be based on, for example, a user interacting with GUI200to specify delay attributes. If it is determined that the trigger will not have delay attributes, the method300continues at step330. Otherwise, the method300continues at step322, where the delay attributes of the trigger are received. The delay attributes may be specified, for example, by a user making selections in the GUI200. Such selections may include a trigger name, a delay type, and the like. At step324, the received delay attributes may be stored, for example, in the database144. At step330, the defined trigger is created, and the method300ends.

FIG. 4illustrates a flow diagram illustrating a method400for processing database triggers having delay attributes, according to one embodiment of the invention. The method400begins at step410, when a trigger is activated. That is, an event specified in the firing condition of a trigger occurs. Such events typically include I/O events of the database, such as INSERT, UPDATE, or DELETE events of a SQL database.

At step420, it is determined whether the activated trigger has any delay attributes. This step may be performed, by the trigger engine134illustrated inFIG. 1. If it is determined that the trigger does not have any delay attributes, the method400continues at step430. Otherwise, the method400continues at step422, where the delay attributes of the trigger are retrieved. The delay attributes may be retrieved from, for example, the database144.

At step424, the trigger may be added to a delay queue (e.g., delay queue136), along with any other triggers having delay attributes that may have been previously activated. At step426, the delayed trigger may be evaluated to determine whether the delay condition (e.g., time interval selection230) has been satisfied. If not, the delayed trigger remains in the delay queue. If the delay condition is satisfied, the method400continues at step430, where the triggered action of the trigger is executed. After step430, the method400ends.