Summary client / server application logic design is reasonable, directly affecting the processing performance of the client / server mode application system. This article proposes some methods to the platform to determine how the application logic is divided, allowing the client / server application system to have a high processing efficiency. Key words PL / SQL triggered client / server 1. Problem For the 80s to the end of the 1990s, many application systems transition from host terminals, file sharing methods to client / server. The client / server system provides higher performance than file server systems because clients and servers separate the processing requirements of the application, and together implement their processing requirements (ie "distributed application processing"). The server is a multiple client management database, while the client sends a request and analyzes the data received from the server. In a client / server application, the database server is intelligent, it only blocks and returns those rows requested by a client, ensuring concurrency, making the information transfer on the network minus, so that the system can be improved . In the client / server system, the application is distributed on the network, so when designing a client-only database application, if too much burden is added to the network, there is no to take advantage of the stored procedure of the database, no Network access minimizes, the result is that the application is to perform excessive network I / O, so that the network is saturated, thereby reducing the performance of the entire system. To develop a good client / server application system, you must figure out how to distribute application functions between components of the constituent system. This article proposes some methods to determine which functions in the client / server database system should be put. Client applications focus mainly represent and / or analyze data in a manner that makes it easy for users. When developing client applications, network transmission is one of the questions that should be considered. Note how the application sends information to the database server or receives information from the database server and how much data is sent and received. Usually the network I / O on the client / server system is the bottleneck of the application performance, the less I / O, the application and the operation of the entire system, and the operation of the entire system caused by an application. To eliminate unnecessary network transmission from a client application, you need to understand and use SQL commands and other features on the database. Second, one example below, let's consider an example: a database application completes the total amount of all rows that display each sales order. General algorithm is divided into two steps: (1) Multiplying the number of items in each line of items by unit price = amount; (2) Putting per line amount. The first method: each time, then, use the application to be tired,
Select ORDERID, QUANTITITY, StockWhere stock.id = item.idorder by OrderId results: ORDERID Quantity UnitPrice1 1 6.031 1 21.41 4 87.122 2 8.972 3 21.4 Adoption, if the traffic is increased, its network transmission The amount is correspondingly increased. Second method: Let the database server calculate, and then only take the result from the Internet, ie:
Select ORDERID, SUM (Quantity * Unitprice) from item, stockwhere item.id = stock.idgroup by OrderIdORDER by ORDERID results: ORDERID SUM (Quantity * Unitprice) 1 265.872 82.14 As shown above, since the second query uses a SQL A combination, a SQL function (SUM), and a Group By clause that allows the server to perform calculations. Therefore, only less data is transmitted from the Internet. This simple example shows: 1. How to reduce the transmission amount on the network in the client / server environment; 2. Developers must fully familiarize with SQL to make a good client database application. Third, improve performance methods Let us introduce several ways to improve performance. 1. Using integrity constraints All client applications must follow a range of predefined data integrity planning and business rules to ensure that all database data is legal. Two methods can be used to implement a simple and complete rule: allowing the application to perform integrity checks; use Oracle7's integrity constraints. (1) Let the application execute an integrity check For example, the customer number of any order (Orders) must be the customer's client number in the Customer. This is the simplest reference integrity that can be performed using the application itself.
DeClareFlag Integer; BegInselect ID = 3for Update of id; if SQL% Found Tensert Into OrdersValues (5, 3, sysdate, null, null, 'f'); - Other Application Logic Commital; This process is just a method for implementing the reference integrity rule inside the application, but it can be seen that in order to implement a simple integrity rule, the application takes a lot of time to request and transmit data through the network. (2) Integrity constraints using Oracle's CREATE TABLE orders 7 (id INTEGER PRIMARY KEY, customer id INTEGER NOT NULL REFERENCES customer, orderdate DATE NOT NULL, shipdate DATE DEFAULT SYSDATE, paidate DATE DEFAULT SYSDATE, status CHAR (1) DEFAULT 'F 'CHECK (Status In (' f ',' b ')))); a better way to implement simple integrity rules (such as reference integrity) is to use Oracle 7 integrity constraints. The advantage of this method is obvious: 1 Define a table, conveniently establish integrity constraints, developers do not need to create tests for implementation of a simple integrity rule, and raise the bad complex data integrity logic, improve work Efficiency; 2 to achieve integrity rules with centralized methods; 3 No network I / O, client / server system will not reduce performance due to network access. 2. Using database trigger applications often require complex business rules, these rules cannot be indicated by integrity rules, so it is best not to implement integrity rules in applications in applications in the application; but use data triggers (Triggers) To implement business rules. Its advantage is that it is easy to create, and it can be concentrated in rules to avoid unnecessary network I / O. Using database triggers can centralize and automate other applications. For example, the value of calculating the Total column in the ITEM table is the number of parts that multiply the part of the part, and the part unit price is stored in the STOCK table. When a new row project is inserted, the application calculates the value of Total columns. Method. Method 1: Let the application execute this operation through the SQL command
DeClare Total Real; Beginselect Unitprice * Quantity Into TotalFrom Stock, Itemwhere ID = 4; Insert Item VALUES; END application issues a request via the network, obtain some part unit price, then insert this row calculated value ( Tota L). Modify a number of lines in the ITEM table, the application needs to include similar logic. In addition, multiple users may also insert and modify orders at the same time. In summary, use this method to calculate the TOTAL sequence generate a large number of network transfers in the client / server system. Method 2: Trigger with database, automatically export Total's value from a row project, and no network access is required when the user inserts a new row or modify Quantity in the ITEM table.
CREATE TRIGGER LinetotalBEFORE INSERT OR UPDATE OF quantity, stockidON itemFOR EACH ROWDELAREitemprice REAL; BEGINSELECT unitpriceINTO itempriceFROM stockWHERE id =: new.stockid;: new.tolal: = new.quantity * itemprice; END linetotal; When you create a trigger linetotal, application development Personnel do not need to consider keeping Total as the latest value when writing applications, and all applications on the network database will benefit. 3. Utilize process and package optimization performance This mainly discusses how to use integrity constraints and database triggers to move application logic to the database server to reduce network I / O, improve performance. Other types of application processing logic distribution to the database server can also reduce network I / O in client / server applications, and applications do not have to perform database server operations with SQL statements containing multiple network operations, but simply and effectively call Stored procedure. The package is a method used to encapsulate multiple related processes in the database. Below is the difference between SQL and the stored procedure example: For example, to insert some row item orders, implement process with SQL: Insert ITO Orders Values (...) Insert Item VALUES (1, ..) Update stock set onHand = ... INSERT INTO ORDERS VALUES (...) INSERT ITO ITEM VALUES (2, ..) Update stock set onhand = ... INSERT INTO Orders Values (...) Insert Item VALUES (3, ..) Update stock set onhand = ... commit; To create a new sales order and insert it into the three rows items, the application must be implemented with 7 different SQL statements, each statement is transmitted over the network, Reduce the amount of network traffic generated by these SQL statements in the client / server system, you can create two simple processes to insert an order and row project.
CREATE PROCEDURE placeorder (custid IN INTEGER) ASBEGININSERT INTO ordersVALUES (orderseq.NEXTVAL, custid, SYSDATE, null, null, 'F'); END placeorder; CREATE PROCEDURE placeitem (itemid IN INTEGER, partid IN INTEGER, quan IN INTEGER) ASBEGININSERT INTO Item (ID, OrderID, stockid, quantity) Values (itemid, orderseq.currval, partid, quan); Update stockset onhand = onhand-quanwhere id = partid; End PlaceItem; application simply calls these processes.
PlaceRder (3); PlaceItem (1, 3, 2); PlaceItem (2, 8, 1); PlaceItem (3, 9, 3); When an application is called stored procedure, only process calls sent through the network and parameter. The method of improving the performance of the client / server application system is mainly based on reasonable distribution processing logic on the client side and the server, and makes full use of the database server to improve the execution speed. The effectiveness of this method has been confirmed in the many client / server applications we have developed. In addition, in improving performance measures and methods, Query Optimization of SQL statements cannot be ignored. In combination, the above two methods can fundamentally improve the performance of the client / server application system.
