Improve PHP performance by caching Database results _php Tutorial

As we all know, the results of cached database queries can significantly shorten script execution time and minimize the load on the database server. If the data to be processed is essentially static, the technique will be very effective. This is because many data requests to the remote database can eventually be satisfied from the local cache, eliminating the need to connect to the database, execute queries, and get results.

However, when you use a database that is located on a different computer than the WEB server, it is often a good idea to cache the database result set. However, determining the best caching strategy based on your situation is a challenge. For example, for applications that use the most recent database result set, a time-triggered caching method (a common method of caching systems that assumes that the cache is regenerated every time the failure timestamp is reached) may not be a satisfactory solution. In this case, you need to adopt a mechanism that notifies the application whenever the database data that the application needs to cache is changed so that the application will keep the cached stale data consistent with the database. In this case, it is very convenient to use database change notification.

Getting Started with database change notification

The use of the database change notification attribute is simple: Create a notification handler for notification execution – a PL/SQL stored procedure or a client OCI callback function. Then, register a query against the database object for which you want to receive change notifications so that the notification handler is invoked whenever the transaction changes any of its objects and commits. Typically, the notification handler sends the name of the modified table, the type of change that was made, and optionally the row ID of the changed line to the client listener so that the client application can perform the appropriate processing in the response.

To understand how the database change notification feature works, consider the following example. Assume that your PHP (as the current mainstream development language) application accesses OE. Orders, as well as OE, stored in the Orders table. The order items stored in the Order_items. Because you rarely change the information that has been placed on an order, you may want the application to cache query result sets for both orders and Order_items tables at the same time. To avoid accessing outdated data, you can use database change notification, which allows your application to easily learn about changes to the data stored in the two tables above.

You must first grant the change NOTIFICATION system permission and the EXECUTE on Dbms_changenotification permission to the OE user to register queries against ORDERS and Order_items tables in order to receive notifications and responses to this A DML or DDL change made by two tables. To do this, you can execute the following commands from the SQL command-line tool (such as Sql*plus).

Connect/as SYSDBA;
GRANT change NOTIFICATION to OE;
GRANT EXECUTE on dbms_change_notification to OE;
Make sure that the Init.ora parameter job_queue_processes is set to a value other than 0 to receive PL/SQL notifications. Alternatively, you can also use the ALTER SYSTEM command below:

ALTER SYSTEM SET "job_queue_processes" = 2; Then, after you connect to Oe/oe, you can create a notification handler. But first, you must create a database object that will be used by the notification handler. For example, you might want to create one or more database tables so that the notification handler logs changes to the registry. In the following example, you will create a nfresults table to record the date and time the change occurred, the name of the table that was modified, and a message that indicates whether the notification handler successfully sent the notification message to the client.

CONNECT Oe/oe;

CREATE TABLE Nfresults (
Operdate DATE,
Tblname VARCHAR2 (60),
Rslt_msg VARCHAR2 (100)
);
In practice, you may need to create more tables to record information such as notification events and row IDs for changed rows, but for the purposes of this article, the Nfresults table is perfectly enough.

Using Utl_http to send notifications to clients

You may also want to create one or more PL/SQL stored procedures and invoke them from the notification handlers to achieve a more maintainable and flexible solution. For example, you might want to create a stored procedure that implements sending notification messages to clients. Listing 1 is the PL/SQL process sendnotification. This procedure uses the UTL_HTTPPL package to send change notifications to the client application.

Listing 1. Using Utl_http to send notifications to clients

http://www.bkjia.com/PHPjc/508614.html www.bkjia.com true http://www.bkjia.com/PHPjc/508614.html techarticle as we all know, the results of cached database queries can significantly shorten script execution time and minimize the load on the database server. If the data to be processed is basically static ...

