A Three-Tiered Application Success Story
by Christopher Waterson & Menachem Sendowski
Using Gupta SQLWindows and Oracle7, Bechtel Corporation and Terrace Systems Corporation have jointly implemented a corporate time recording application that is currently in use by over 3,500 Bechtel employees, and is targeted to be in use by all 16,000 non-manual employees by the end of 1995.
The Bechtel Electronic Time Record (BETR) system uses a unique three-tiered client server architecture we call "light-client" client-server: a front-end presentation layer is used for managing user interaction, an application layer processes requests from the front-end and implements business logic, and a database layer serves as a repository for business information. Our original motivations for selecting this design were:
Once the high-level design had been completed, we drafted Oracle7 stored procedure interface specifications and formed two application development teams. The "front end" team concentrated on building a solid SQLWindows application that was easy and intuitive to use. The "back end" team concentrated on implementing the business logic of the application. The two teams worked independently and in parallel, and only saw the fruits of the other's labor in the final stages of development. From inception to production, the project took close to 8 months, and involved four full-time programmers for a period of about one month.
In this article, we review the three-tiered client-server architecture in detail. We expand on the development methodology which we used to build the application. We discuss specific design considerations and implementation details, including application partitioning and encapsulation. Finally, we review the success of our implementation, highlighting both positive and negative aspects of the architecture as implemented in a real-world system.
BETR was born as a business initiative to reduce Bechtel's payroll and labor overhead processing costs by replacing the paper-based manual time recording system with an automated equivalent. As system designers, our goal was to produce an easy-to-use application that could be accessed by each employee within the organization from his or her desktop computer.
As such, BETR is a functionally simple application that provided several interesting technical challenges.
It was our conclusion that these technical problems could be most effectively and elegantly solved by a "light client" client-server application architecture for the OLTP components of the application.
Below we describe some of the different approaches to client-server, and high-light the advantages and disadvantages of each as they applied to our business scenario.
The "traditional" client-server architecture depicted in Figure 1 was the first architecture we considered for BETR. We classify a "traditional" client-server architecture as a two-tier architecture with the following characteristics:
This approach is well suited for applications where flexibility is paramount. The database serves as a repository of information that can be accessed and maintained freely by a variety of application tools in a vendor-neutral manner.
Figure 1. Traditional client-server architecture
However, for transaction-intensive applications with a large installation base, the traditional client-server architecture led to several problems:
A "tiered" client-server architecture solves some of the above problems by introducing a layer between the client application and the database server called the application server (see Figure 2):
Figure 2. Tiered client-server architecture
Tiered client-server is more appropriate for OLTP-style applications because it offers:
An upgrade to local software (and therefore re-distribution) need only be applied when a change is made to the application's overall functionality that requires a change to the user interface.
Post production risks are reduced because each business message can be tested independently of the user interface. The ensures that a business operation works under normal and abnormal conditions because it can be thoroughly tested with a wide variety of parameter values.
The client software is simply a presentation layer that allows the user to view and navigate the data, allows the user to send business messages, and informs the user of business errors.
Application users manipulate the database tables only indirectly, via business messages sent to the application server. Each business message is implemented in such a way that it raises a business error if a user attempts to perform an invalid operation.
At the time when we were performing technology selection and evaluation, getting into the "pure" three-tiered marked was prohibitively expensive and fairly complex. We were also unwilling to implement a "pure" three-tiered client-server architecture because it requires two distinct processing units, an application server and a database server.
The "light client" client-server architecture (shown in Figure ) solves the two-processor problem of the tiered client-server architecture by using Oracle stored procedures to combine the application server and the database server in a single-processor environment.
Figure 3. "Light client" client-server architecture
In the "light client" client-server architecture, as in the tiered client-server architecture, the front-end component of the application is a presentation layer, and need only provide an effective application interface to the user. Unlike the tiered architecture, the bulk of the application's business functionality is implemented by the RDBMS engine itself.
As in tiered client-server, the two portions of the application communicate using business messages. Unlike the tiered architecture, the messages are issued directly from the client application to the database server.
The "light client" client-server architecture has all of the advantages of the tiered architecture, including simpler software distribution, reduction of duplicate software, and improved security. In addition:
By moving the bulk of the business logic to the server platform, operations the require complex data validation, lookup, summarization, or bulk-manipulation need never incur a network "hit." These operations are all encapsulated within the server-level logic. The client simply requests a business operation of the server, and the server handles the nuts and bolts of getting the job done.
Bundling several operations together into a stored procedure (among other things) reduces the number of external-to-internal and internal-to-external datatype conversions that are required for individual operations -- these conversion operations involve network traffic and are relatively expensive.
Pursuing the "light client" client-server architecture in favor of the traditional or tiered client-server architectures is not without risk:
Once the high-level design and analysis of BETR was complete, we performed detailed designed and implemented BETR over a period of three to four months. We had two development teams with distinctly different skill sets:
We used the following general process for development of BETR:
True to our expectations, the development of the front-end and back-end portions of the application can be performed in parallel once we had agreed upon the interface specifications.
The key to a successful design for any client-server system is balance, or application partitioning. For our application, "traditional" client-server, where most of the logic resides in the client workstation, was too client-intensive. On the other hand, using a tiered architecture with all the application's logic on the server would have made our application too server-intensive.
It became apparent that we needed to develop some guidelines for partitioning our application. We decided on the following rules of thumb for identifying where we would place application code:
Table 1. Our "Rules of Thumb" for application partitioning
Client Server Reasoning
An operation that A "business message," Greater security &
changes control or i.e., an operation that control at
set-up structures in changes or creates server-level.
the database, such as transaction data in the Performance gain for
modifying a database, such as saving pre-compiled & cached
manager-employee modifications to a time PL/SQL
relationship. record.
A simple "generic" An operation that Client can best handle
validation, such as performs non-trivial type checking.
ensuring proper information validation Allows consolidation of
datatype or size. on a transaction, such business logic at
as ensuring that time server.
record meets payroll Packaged PL/SQL has
integrity checks. "fast" access to
database; no network
traffic.
"Ad hoc" operations An operation that PL/SQL has primitive
that involve involves fairly static dynamic SQL
dynamically generated SQL. capabilities.
SQL. Performance benefit due
to pre-compilation of
PL/SQL is lost.
An operation that An operation that Shields database
requires relatively requires complicated complexity from
simple SQL. SQL. application programmer.
"API"-style approach
Operations that Operations that Centralized security
manipulate "unsecured" manipulate "secure"
objects. objects.
Obviously, it was crucial to determine:
For example, our application had transactions such as SignTimesheet and ApproveTimesheet (representing electronic employee signature and approval signature) that were clear candidates for server implementation.
A query that became a server-based component was ListValidChargeCodes, which produced a list of valid time charges based on the employee's current project assignment.
For example, we knew that our payroll validations could be fairly pliable, so the business action VerifyTimesheet was implemented on the server.
Because of government audit requirements, each change to a time record had to be recorded, so the SaveTimesheet procedure that stored time record information in database tables was a clear candidate for centralized implementation.
There were several issues that we dealt with once we began the real work of implementing the system.
Each transaction or "business message" was implemented as an Oracle stored procedure. All of our stored procedures were packaged to maintain code modularity.
Most "read" and "populate" operations are performed using client-issued SQL that executes directly against database tables. In some cases, we have implemented stored procedures to generate a list-of-values. We did so when we felt that:
The underlying database tables, views, and packages were created in a "secure" database account. Public synonyms were created for those objects that needed to be "readable" to a user of the system. Each user has an individual Oracle account, to which object and system privileges are granted to a user via an Oracle role.
We implement our business errors as real Oracle exceptions using the RAISE_APPLICATION_ERROR PL/SQL pragma. This halts application flow and uses a standard exception mechanism to propagate the error out to the front-end.
The system relies on Oracle's statement-level rollback to undo any work that had been performed up until the point that an error was detected, which greatly simplified much of our application programming. In addition, it makes the system "safe" in that each statement either completely succeeds or completely fails, leaving the database in a consistent state.
This has proven quite effective; however, this approach does have its limitations. Specifically, only fatal errors can be reported using RAISE_APPLICATION_ERROR; we had to build a separate mechanism to report warning messages.
We used Gupta SQLWindows to implement the graphical front-end applications. We created functional classes that mirrored the packages created in the database, mapping each procedure in the package to a function for the class. It allowed us to:
For those procedures that returned result sets to the application (e.g., procedures that produce list-of-values), we used PL/SQL tables to populate SQLWindows arrays. Where more than one set of values was required, we used parallel arrays to retrieve values. Some words of caution here:
For example if you declare a PL/SQL table of VARCHAR2(1024), you'll be able to pass at most 32 items back and forth. (We actually discovered that it is somewhat less -- probably due to book-keeping information hidden somewhere.)
To get around this one, we had to create a clean-up function within Oracle to properly convert the strings.
Be aware that SAL's SqlPLSQLCommand function always invokes the Oracle Call Interface routine odessp before running a stored procedure. Odessp itself invokes another Oracle stored procedure that queries the data dictionary to describe the target stored procedure's interface. The Router does this to ensure that the SQLWindows data types can be converted properly to Oracle datatypes; however, this effectively creates two stored procedure calls for each invocation of SqlPLSQLCommand.
We used Oracle SQL*Forms 3.0 to implement the character based front-end applications. The character based front-end application runs as a host-mode application. Although we had originally planned to allow for a separate, dedicated machine to run the SQL*Forms application, both the RDBMS and SQL*Forms currently run on the same machine.
BETR has been a production system since September, 1994. During the past eight months we have learned quite a deal about deploying a custom application throughout the enterprise.
Before production rollout, we did an extensive load test of the application system. This fairly easy to do because all of our transactions were encapsulated as stored procedures.
We wrote a simple SQLWindows application that opened multiple sessions with the server and issued a fixed set of transactions in a round-robin style. We ran the application on several PCs and were able simulate 200 concurrent users issuing a fairly standard set of transactions. From this, we gathered performance statistics (and discovered a number of Oracle and operating parameters that had been set too low!).
Since the initial benchmark of the system, we perform routine monitoring of our application server's resource usage. To date, we have supported 150 concurrent users (of about 2,500 named) on a DEC2100 single-processor machine without significant performance degradation. We plan to continue our rollout to support 8,000 named users with this machine, and estimate that this will result in about 500 concurrent sessions.
We have chosen to deploy the application in a networked fashion. In other words, the application's executables and SQLWindows run-time DLLs reside on a network file server from which client workstations run the application. Bechtel's network topology is such that roughly 50 to 75 PCs are supported by a single file server. Although this does incur a significant performance penalty for individual users, it has allowed us to quickly and efficiently re-deploy the application throughout the company when performing upgrades and maintenance. As the application stabilizes and software replication technology matures, we will begin to experiment with locally-installed configurations.
Through that period we've seen several upgrades and enhancements that are classified below:
Table 2. Release Categories
Category Description Frequency Impact
Major Application re-write. None yet! Synchronized
Release re-distribution of
client application and
update of centralized
database logic.
Minor Significant enhancement Once per 4 Synchronized
Release of overall functionality months re-distribution of
(e.g., a new screen or client application and
other major new update of centralized
features). database logic.
Significant user
interface change.
Maintenance Minor enhancement of Once per 2 Re-distribution of
Release functionality (e.g., a months client application.
new report).
Patch Bug fixes, policy Once per Update of centralized
changes, legal month database logic.
requirements.
Upgrades that require coordinated rollout of client application software and database-level changes are both the most difficult and highest-risk. It involves operations personnel to distribute the client software throughout the campus, as well as DBA personnel to run upgrade scripts.
The interface between the client and server components require that both be synchronized properly or the application will not function. (This has actually worked to our benefit in that it is an easy way to ensure that users are always running the most current version of the application.)
Roll-back of a "failed" upgrade is difficult because not only must old client software be re-distributed, but database software must be "downgraded" as well.
These are easier to perform than minor releases, but still require propagation of client software throughout the campus.
Patches are by far the easiest to implement and occur fairly frequently. These require update to server logic for changes to company policy, etc. The beauty of the centralized business logic truly shows here when we are able to "invisibly" update the application logic by simply re-compiling a few Oracle packages.
The ability to perform server-only patches to the stored procedure code has proven to be a real "win" with our system.
One of our initial goals with BETR was to create a code base that could be easily and quickly re-used with other similar "corporate transaction gathering" applications. We are currently in the process of designing and developing an expense reporting application in which we hope to re-use some of the BETR PL/SQL code.
To date, we have had mixed success in re-using the packaged PL/SQL code in other applications. Unlike SAL or C++, PL/SQL's ability to dynamically generate and execute SQL is clumsy, at best. Forsaking dynamic SQL implies that any modification to a SQL statement requires duplication of the entire statement, or addition of "clever" WHERE conditions that both cloud application logic and reduce performance.
Although we will be able to re-use some of the PL/SQL procedures, we will not be able to recycle as much code as we had originally hoped.
We have been pleased with the success of our "light client" client-server architecture, and feel that it has lived up to most of our design expectations:
Because of the complexity of coordinating and managing several separate development teams, a "light client" client-server application requires careful and thorough interface design before implementation begins. Interface changes after production rollout result in costly re-distribution of client software.
In comparison to a traditional client-server architecture, application performance is improved, hardware and network load are reduced, distribution and maintenance costs are lowered, and security is increased by using a "light client" client-server architecture.
The "light client" client-server architecture provides an elegant and effective method for implementing applications with a large installation base, transaction-intensive nature, and focused functional architecture.