Some methods to improve the performance of C/s system

Performance Summary client/server application logic design is reasonable, directly affect the client/server mode application system processing performance. Based on Oracle 7 server, this paper proposes some methods to determine how to divide the application logic and make the client/server application system have higher processing efficiency.
Keyword PL/SQL triggers a child client/server

First, the question of the proposed

In the late 80 and early 90, many application systems were transitioning from host terminal mode and file sharing mode to client/server mode. Client/server systems provide higher performance than file server systems because the client and server separate the processing requirements of the application, while simultaneously implementing their processing requirements (i.e., "Distributed Application Processing"). The server manages the database for multiple clients, while the client sends the request and analyzes the data received from the server. In a client/server application, the database server is intelligent, which only blocks and returns the rows requested by a client, guarantees concurrency, minimizes the transmission of information on the network, and thus improves the performance of the system.

In the client/server system, application processing is distributed on the network, so when designing client database application, if put too much burden on the network, did not take full advantage of the database stored procedures, did not minimize network access, the result is that the application to perform too much network I/O, so that the network saturation, Thus reducing the performance of the entire system. To develop a good client/server application system, you must figure out how to distribute the application functions among the parts of the system that comprise it.

This article presents a number of ways to determine which features in a client/server database system should be implemented. Client applications are primarily focused on expressing and/or analyzing data in a user-friendly manner. When developing client applications, network traffic is one of the key issues to consider. Notice how the application sends information to the database server or receives information from the database server and how much data is sent and received. Typically, network I/O on a client/server system is a bottleneck for application performance, and the less I/O is caused by an application, the better the application and the overall system will run. To eliminate unnecessary network traffic from one client application, you need to understand and take advantage of SQL commands and other features on the database.

Second, an example

Next, let's consider an example of a database application that completes the total amount of all the rows that display each sales order.

The general algorithm is divided into two steps:

(1) Number of items per line multiplied by unit Price = amount;
(2) Add up the amount of each line.

The first method:

One line at a time, then add it to the application, i.e.

SELECT OrderID, Quantity, UnitPrice
From Item, Stock
WHERE stock.id=item.id
ORDER BY OrderID
The results are:
OrderID Quantity UnitPrice
1 1 6.03
1 1 21.4
1 4 87.12
2 2 8.97
2 3 21.4
With this method, if the volume of traffic increases, the transmission of the network increases correspondingly.

The second method:

Let the database server compute, and then just fetch the results from the web, namely:

SELECT OrderID, SUM (quantity *unitprice)
From Item, Stock
WHERE item.id=stock.id
GROUP by OrderID
ORDER BY OrderID
The results are:
OrderID SUM (Quantity*unitprice)
1 265.87
2 82.14
As shown above, the second query uses a combination of SQL, a SQL function (SUM), and a GROUP BY clause to let the server perform the calculation. As a result, only a small amount of data is transferred from the Internet.

This simple example illustrates:

1. How to reduce the amount of traffic on the network using SQL functions in the client/server environment;
2. Developers must be fully familiar with SQL in order to make good client database applications.


Third, the method of lifting high performance

Here are a few ways to raise performance.



1. Use integrity constraints

All client applications must follow a series of predefined data integrity planning and business rules to ensure that all database data is legitimate. There are two ways to implement a simple, complete rule: to have an application perform an integrity check, and to use ORACLE7 integrity constraints.

(1) Allow the application to perform an integrity check

For example, the customer number for any one order (orders) must be the GU client number in the client table (customer). This is the simplest referential integrity, and you can perform integrity checks with the application itself.


DECLARE
Flag INTEGER;
BEGIN
SELECT ID into flag
From customer
WHERE id=3
for UPDATE of ID;
IF Sql%found THEN
INSERT into Orders
VALUES (5,3,sysdate,null,null, ' F ');

--Other application logic
COMMIT;
End IF;
End;


This process is just one way to enforce referential integrity rules within an application, but it can be seen that in order to implement a simple integrity rule, the application spends a lot of time requesting and sending data over the network.

(2) Use of Oracle 7 integrity constraints

CREATE TABLE Orders
(
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 referential integrity, is to use the integrity constraints of Oracle 7. The benefits of this approach are obvious:

① define a table and easily establish integrity constraints, developers do not need to implement a simple integrity rules to create tests, debugging complex data integrity logic, improve work efficiency;
② to implement the integrity rules in a centralized way;
③ without any network I/O, the client/server system will not degrade performance because of network access.

2. Using Database triggers

Applications often need to implement complex business rules that cannot be represented by integrity rules, so it is best not to enforce integrity rules in an application in a conventional way, but rather to enforce business rules with data triggers (triggers). The advantage is that it is easy to create, can centralize the implementation of rules, avoid unnecessary network I/O. The use of database triggers enables the centralization and automation of some other applications. For example, the value of the total column in the Item table is the number of the ordered part multiplied by the unit price of the part, while the part unit price is stored in the stock table, and when a new line item is inserted, the application calculates the value for the total column in two ways.

Method One: Let the application perform this operation through the SQL command


DECLARE total Real;
BEGIN
SELECT unitprice*quantity into total
From the stock, item
WHERE id=4;
INSERT into Item VALUES (...);
End

The application makes a request over the network, obtains the unit price of some parts, and then inserts the row that contains the computed value of the row (Tota l). To modify the quantity value of a row in the Item table, the application needs to include similar logic to compute. In addition, multiple users may also insert and modify orders at the same time. In summary, this method of computing the total column produces a large amount of network traffic in the client/server system.

Method Two: Use database triggers, automatically export total value from a line item, when user inserts new row or modifies quantity in Item table, need no network access.


CREATE TRIGGER LineTotal
Before INSERT OR UPDATE of Quantity,stockid
On item
For each ROW
Delare
Itemprice Real;
BEGIN
SELECT UnitPrice
Into Itemprice
From the stock
WHERE Id=:new.stockid;
: New.tolal:=new.quantity*itemprice;
End LineTotal;

When the trigger LineTotal is created, the application developer does not have to consider keeping the total as the latest value when writing the application, and all applications on the network database will benefit.

3. Optimize performance using process and package

The main discussion here is how to use integrity constraints and database triggers to move application logic to the database server in order to reduce network I/O and improve performance. Other types of application processing logic distributed to the database server can also reduce network I/O in client/server applications, and applications do not have to use SQL statements containing multiple network operations to perform database server operations, but simply and efficiently invoke stored procedures. A package is a way to encapsulate a number of related processes in a database. Here is the difference between using SQL and using stored procedure examples:

For example, to insert an order for some line items, use SQL to implement the procedure:


INSERT into Orders VALUES (...)
INSERT into Item VALUES (1,..)
UPDATE stock SET onhand= ...
INSERT into Orders VALUES (...)
INSERT into Item VALUES (2,..)
UPDATE stock SET onhand= ...
INSERT into Orders VALUES (...)
INSERT into Item VALUES (3,..)
UPDATE stock SET onhand= ...
COMMIT;

To create a new sales order and insert its three line items, the application must be implemented with 7 different SQL statements, each of which transmits data over the network, to reduce the amount of network traffic that these SQL statements generate on the client/server system, and two simple procedures can be created to insert order and line items.

CREATE PROCEDURE PlaceOrder (CustID in INTEGER) as
BEGIN
INSERT into Orders
VALUES (Orderseq. Nextval,custid,sysdate,null,null, ' F
');
End PlaceOrder;
CREATE PROCEDURE Placeitem
(Itemid in Integer,partid in integers, quan in integer)
As
BEGIN
INSERT into item (id,orderid,stockid,quantity)
VALUES (Itemid,orderseq. Currval,partid,quan);
UPDATE Stock
SET Onhand=onhand-quan
WHERE Id=partid;
End Placeitem;

The application simply calls these procedures.


PlaceOrder (3);
Placeitem (1,3,2);
Placeitem (2,8,1);
Placeitem (3,9,3);

When an application invokes a stored procedure, the data sent over the network is only a procedure call and a parameter. In this paper, the method to improve the performance of client/server application system is to use reasonable distributed processing logic to the client side and server, and make full use of database server to improve the execution speed. The effectiveness of this method has been proven in a number of client/server applications we have developed. In addition, in improving the performance of the measures and methods, SQL statements query optimization problem can not be ignored. Combining the above two methods can fundamentally improve the performance of client/server application system.