Database and application server-related issues

Avoid unnecessary database connections

Connecting to a database is a relatively slow operation that should be done only once. Do not connect and disconnect over and over. Specify the databases to be connected on the command line rather than using the CONNECT statement in ABL. If necessary, you can specify database connections when you enter significant modules that use that database, but be aware of the cost.

Minimize creation of subprocesses

Creating a new process is an expensive operation that can be very time consuming if done often and especially when done by multiple users on the same system. Use statements like INPUT THROUGH, OUTPUT THROUGH, and UNIX with care.

Use database sequences

In Update Your Database and Write Triggers you saw how you can use database sequences to generate a sequence of unique integer values, for example, as key values for a database field such as a Customer Number or Order Number. Sequences are much faster than using an integer field in a control record stored in the database, and they do not cause lock conflicts as a control record does. To obtain unique values from a field in a database record, each user needs to get an EXCLUSIVE-LOCK on the record within a transaction, retrieve its value, increment the value, and then end the transaction and release the record. This is very time consuming, especially when you consider that it is likely that many users will be trying to do this at the same time. This is exactly the purpose that database sequences were designed for. When you execute the NEXT-VALUE function on a sequence, the OpenEdge RDBMS increments the sequence and returns the value to you in a single operation, so that there is no chance that another user can see the same value, but without any need for a transaction.

Minimize the size and number of network messages

The two slowest operations in most systems are transmitting data over a network and accessing data on a disk. When using application servers, sockets, or client/server database access, take care to minimize the number of network calls you make. Whenever possible, send large amounts of data at once rather than many small transmissions.

Always remember to test on slow networks. A dial-up connection is many times slower than a fast LAN connection. Network messages take considerably longer to transmit on slow connections. Sometimes an application works well during development because a fast network is used and fails miserably when deployed in the real world.

Network latency is limited and is generally beyond your control. The speed of light limits network transmission time. For example, it is 3266 miles from Boston to London. Via the Internet, it might be 5000 miles or more. A sample routing chart shows 18 hops from a PC in a Boston office to a server in a London office. It takes at least 40 milliseconds to send a message, assuming best possible conditions. Typical conditions might require 80 or 100 milliseconds. So, sending a message and getting a response takes around 160 milliseconds if the server responds quickly. At that rate, you can send and receive 6 (typical) to 12 (best case) messages per second. Nothing will ever make this turnaround time faster. Technical improvements in the Internet will only allow you to send more data in the same time.