Experiment 2 - Stored Procedures
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. This experiment uses TPPT.
The task to be executed is the same as that of Experiment 1: from a given starting state, raise all faculty salaries by the same percentage - an integral number of (compounded) 1% raises -- so as to keep the total budget less than or equal to $40000. You are to implement this in three ways, all of which use savepoints to roll back when the budget exceeds the maximum.
1. Use a client side, Java/JDBC program that opens an updatable result set over the faculty table and fetches and updates each row individually with a 1% raise. After giving all faculties the raise, the program evaluates the budget and either goes through another iteration or rolls back to the last savepoint and terminates. This does not involve stored procedures and requires the maximum communication overhead, since each row is handled by the client separately.
2. Same as (1) except that the program gives all faculties a 1% raise with a single UPDATE statement. This also does not involve stored procedures, but communication is now reduced over (1) since rows are not transferred to the client for updating, although successive UPDATE statements must still be executed from the client.
3. Use a stored procedure for executing the entire task. Create the procedure outside your java code. All the transaction logic, which was part of the JAVA code till now, is now embedded in the stored procedure. Communication is now minimized since it is reduced to a single invocation of the procedure.
FacId char(9) NOT NULL,
Name char(50) NOT NULL,
Password char(20) NOT NULL,
Validity char(1) NOT NULL,
salary float NULL,
PRIMARY KEY (FacId)
To initialize the Faculty table, use
INSERT INTO Faculty SELECT * FROM tppt.tppt.initFaculty
Note that you will need to re-initialize the salaries in the Faculty table each time you run an experiment.
1. When you run your program in Tppt, it will manage the connection to the database, so you don't need the connection code. However, when you debug your program in JDK or JBuilder, your code must establish the connection to the database.
2. Before calling a stored procedure or executing an SQL statement in your program, make sure it's correct using Jisql.
3. Because only one connection is allowed on SingleUser server at a time, if you cannot connect to the server, please wait for some time and try again.
4. Useful materials for TPPT can be found on the Hints page.
1. The Java source code and/or stored procedures for the three cases
2. Timing results produced by the tool and the final state of the Faculty table.
3. Your analysis of the results. Do the numbers make sense in terms of the amount of I/O that you anticipate?
Last updated on October 15, 2008