Poorly written server-side code and a badly designed database will make any application run slower, but improving the performance of a slow running web application requires examination of the entire system, not just the database. A typical three-tier web application structure is shown in Figure 1.
FIGURE 1. Web application process flow
As shown in Figure 1, there are numerous possible places for web applications to experience bottlenecks or performance killers, as described in the following nine-step process:
- Step 1. Code and operations are executed on the client machine. When a user clicks a Submit button, data is collected and bundled into a request that is sent to the application server.
- Step 2. The client request is transmitted to the application server.
- Step 3. Code in the application server is executed as a formulation of the client request to retrieve information from the database.
- Step 4. The client request is transmitted from the application server to the database.
- Step 5. The database receives and processes the information and prepares it for return to the application server.
- Step 6. Information is transmitted over an internal network from the database to the application server.
- Step 7. The application server processes the database response and prepares the response transmission to the client machine.
- Step 8. Data is transmitted from the application server to the client machine.
- Step 9. The client machine processes the returned request and renders the application page in the browser.
Web Application Performance Problem Areas
There is a temptation to focus tuning efforts on the database only, by looking at parameters, SQL queries, and PL/SQL code. However, tuning solely in the database only helps with Step 5 and ignores all of the other places where performance can degrade. This section describes how problems can occur at each step in the process.
Step 1: Client Machine Performance Problems
The formulation of a request in the client machine is usually the least likely source of system performance problems. However, it should not be dismissed entirely. In many commonly used modern system architectures, it is possible to place so much code in the client machine that a significant amount of time is required before the request is transmitted to the application server. This is particularly true for underpowered client machines with inadequate memory and slow processors.
Step 2: Client Machine to Application Server Transmission Problems
As is true for the client machine itself, the transmission between the client machine and the application server is a less common cause of slowly performing web applications. However, if the client machine is attempting to transmit a large amount of information, the time required to do so over the Internet may increase. For example, uploading large files (such as images) or transmitting a large block of data may slow down performance.
Step 3: Application Server Performance Problems
The application server itself rarely causes significant performance degradation. For computationally intensive applications such as large matrix inversions for linear programming problems, some performance slowdowns can occur, but this is less likely to be a significant factor in poorly performing applications.
Step 4: Application Server to Database Transmission Problems
Transmission of data from the application server to the database with 1 Gbps or better transmission speeds might lead you to ignore this step in the process. It is not the time needed to move data from the application server to the database that is the primary issue; rather, it is the time required to switch contexts from the application server to the database that is significant. As a result, a large number of requests between the application server and the database can easily add up to a significant source of performance degradation.
The trend in current web development is to make applications database-agnostic. This sometimes results in a single request from a client machine requiring many requests from the application server to the database in order to be fulfilled. What needs to be examined and measured is the number of round-trips made from the application server to the database.
Inexpert developers may create routines that execute so many round-trips that there is little tuning that a DBA can do to yield reasonable performance results. It is not unusual for a single request from the client machine to generate hundreds (if not thousands) of round-trips from the application server to the database before the transmission is complete. A particularly bad example of this problem required 60,000 round-trips. Why would this large number be needed? Java developers who think of the database as nothing more than a place to store persistent copies of their classes use Getters and Setters to retrieve and/or update individual attributes of objects. This type of development can generate a round-trip for every attribute of every object in the database. This means that inserting a row into a table with 100 columns results in a single INSERT followed by 99 UPDATE statements. Retrieving this record from the database then requires 100 independent queries.
In the application server, identifying performance problems involves counting the number of transmissions made. The accumulation of time spent making round-trips is one of the most common places where web application performance can suffer.
Another major cause of performance problems can occur in the network firewalls where the application server and the client are in different zones with packet inspection in between. For normal applications, these activities may not be significant, but for large, data-transfer-oriented applications, this activity could cause a serious lag. One such example could be a document management system where entire documents are loaded from client machines to the application server.
Step 5: Database Performance Problems
In the database itself, it is important to look for the same things that cause client/server applications to run slowly. However, additional web application features can cause other performance problems in the database.
Most web applications are stateless, meaning that each client request is independent. This leads to the loss of already gathered session-level information accumulated in global temporary tables and package variables. Consequently, when a user logs in to an application, the user will be making multiple requests within the context of the sign-on operation (logical session) to restore information that was already gathered by previous requests.
The information pertaining to the logical session must be retrieved at the beginning of every request and persistently stored at the end of every request. Depending on how this persistence is handled in the database, a single table may generate massive I/O demands, resulting in redo logs full of information, which may cause contention on tables where session information is stored.
Step 6: Database to Application Server Transmission Problems
Transferring information from the database back to the application server (similar to Step 4) is usually not problematic from a performance standpoint. However, performance can suffer when a Java program requests the entire contents of the table instead of a single row. If the entire contents of a database table with a large number of rows are brought into the middle tier and then filtered to find the appropriate record, performance will be inadequate. During development (with a small test database), the application may even work well as long as data volumes are small. In production (with larger data volumes), the amount of information transferred to the application server becomes too large and everything slows down.
Processing the data from the database can be resource-intensive. Many database-agnostic Java programmers minimize work done in the database and execute much of the application logic in the middle tier. In general, complex data manipulation can be handled much more efficiently with database code. Java programmers should minimize information returned to the application server and, where convenient, use the database to handle computations.
Step 8: Application Server to Client Machine Transmission Problems
This area is one of the most important for addressing performance problems but often receives the least attention. Industry standards often assume that everyone has access to high-speed networks so that the amount of data transmitted from the application server to the client is irrelevant. Applications with a very rich user interface (UI) create more and more bloated screens of 1MB or more. Some available partial-page refresh capabilities mitigate this problem somewhat by reducing the amount of information that needs to be transmitted when only part of the screen is being refreshed.
Transmission between the application server and the client machine is one of the most frequent causes of poor web application performance. If a web page takes 30 seconds to load, even if it is prepared in 5 seconds rather than 10 seconds, users will not experience much of a benefit. The amount of information being sent must be decreased.
Step 9: Client Machine Performance Problems
How much work does the client machine need to do to render a web application page? This area is usually not a performance killer, but it can contribute to poor performance. Very processing-intensive page rendering can result in poor application performance, especially on under equipped client machines.