Stored procedures are schema elements containing procedures, which
can be invoked from an application program and executed on the
database server. These procedures can access the database using
embedded SQL more efficiently than programs on the client since
client/server communication is avoided. Furthermore, the entire
procedure can be prepared in advance. The purpose of this experiment
is to evaluate the performance gained by executing a procedure on the
server as compared to executing the procedure on the client.
The task is the same as in described in Assignment 2. You are to
implement it in three ways, all of which use savepoints to roll back
when the budget exceeds the maximum.
- Use a client side Java/JDBC program that opens an updatable
result set over the Invest table, fetches the rows, and updates each
row individually as specified for the subtask. After the updates, the
program evaluates the percentage and either goes through another
iteration or rolls back to the last savepoint and terminates. This
does not use stored procedures and has the maximum communication
overhead, since each row is handled by the client separately.
- Same as in the first way except that the program does the subtask
using a few SELECT and UPDATE statements, not processing each row
individually. This also does not use stored procedures, but
communication is reduced over the first way, because rows are not
transferred to the client for updating, although successive SELECT and
UPDATE statements must still be executed from the client.
- Use a stored procedure for executing the entire task. All the
transaction, which was part of the Java/JDBC code till now, is to be
embedded in the stored procedure. Communication is now minimized
since the task is reduced to a single invocation of the procedure.
What to do
- Write, test, and debug your Java/JDBC programs.
- Create the table Invest as in Assignment 2, but initialize it to
contain 1000 rows, with distinct ids and names, with half of them
being stocks and having an amount of 3000 each, and the other half of
them being bonds and having an amount of 1000 each.
- Prepare to run TPPT, which can be found in Start menu -> All
Programs -> TPPT. First read its manual.
Then get an example,
so that you only need to put your stored procedure in schema.txt, and
put your Java/JDBC code for the task in ClientJavaClass.java. Note,
you need a different ClientJavaClass.java for each of the three ways,
so you can put them in three different source directory, with one copy
of ClearClass.java in each directory. Try to understand the example,
as you may need to write them yourself next time.
- Do experiments for each of the three ways and get the report.
Execution must be done in the transaction lab to connect to the
server. Run an experiment with a single terminal for 20 seconds.
First, run your programs in TPPT on UnderMultiUser server using your
assigned Sybase account name and password; this is a debugging phase
to make sure your program runs correctly in TPPT. Then, run your
programs in TPPT on SingleUser server; the user name and password are
contained within the tool; and the reports generated are your final
Hand in your description and code electronically, using Blackboard,
by midnight on the due date. Your handin should include
source code of the stored procedure and Java/JDBC code for each of
the three ways,
timing results produced by the tool and the final state of the Invest table, and
an analysis of the results; do the numbers make sense in terms of
the amount of communications you anticipate?
This homework will be graded based on 100 points, allocated in
proportion to the required amount of work for each part. You may do
this assignment in a team of two people; the two people will receive
the same points.