Programming for Performance
Page 3 of 4

Stored PreparedStatements

Within Cloudscape, prepared statements exist only within the context of a single user's session, and they disappear when garbage-collected from the user's application. You must re-create them for every session.

Cloudscape allows you to store and name precompiled statements for use across multiple sessions. Stored prepared statements are usually compiled only once (when you create them), not for every session. You create and name stored prepared statements with a language command. In the previous section, you named a prepared statement, but that name existed only within the context of ij. Stored prepared statements have names that are durable across sessions, like tables, views, and other database objects.

Since stored prepared statements usually need to be compiled only once, they are a great performance improvement for applications that use a lot of prepared statements or very complex statements or for applications that have fixed statements and are run repeatedly.

Create a Stored Prepared Statement

You can create a stored prepared statement with (you guessed it) a CREATE STATEMENT statement. Cloudview provides a GUI environment that makes it even easier to work with them.

In this task you will create a stored prepared statement to match the unstored prepared statement you created earlier in Prepare a Statement in ij

  1. Start Cloudview as described in Start Cloudview in the Home Directory. Then open a connection to toursDB as described in Open a Connection to toursDB. Select the toursDB icon in the left-hand hierarchy window. Click the "+" to the left of the icon.
  2. Click the Stored Statements icon.
  3. Choose New->Stored Statement from the Edit menu.
  4. Type findRoomsTaken in the Name box.
  5. Type the following SQL-J statement in the Query box:

    SELECT rooms_taken FROM HotelAvailability WHERE hotel_id = ? AND booking_date = ?

  6. Click OK to create the statement.

    You can execute the statement in the same window.

    The statement has two parameters (you know this because the statement contains two question marks).

  7. Fill in the parameter window, like this (substitute the current date for the date shown):

    9, 1999-09-01

  8. Click the toursDB icon to get to the SQL window.
  9. Now execute the statement again from the less-friendly SQL-J environment.

    You will have to provide the parameters with a VALUES statement:

    EXECUTE STATEMENT findRoomsTaken USING VALUES (9, current_date);

  10. Keep the connection open for the next task.

Work with One of the Stored Prepared Statements in toursDB

The toursDB database has four stored prepared statements, which are used by the FlightBuilder class to query the Flights table and to insert into the FlightObjects table. It is especially useful to be able to store and precompile the complex statement that looks for transfer flights between two cities. Let's look at the SQL-J for that statement (it's a doozy):

SELECT firstleg.flight_id, firstleg.segment_number, secondleg.flight_id, secondleg.segment_number
FROM (SELECT flight_id, segment_number, arrive_time,     dest_airport
    FROM Flights
    WHERE orig_airport = ?) firstleg,
(SELECT flight_id, segment_number, orig_airport, depart_time     FROM Flights WHERE dest_airport = ?) secondleg
WHERE firstleg.dest_airport = secondleg.orig_airport
AND firstleg.flight_id <> secondleg.flight_id
AND secondleg.depart_time.getHours() -
    firstleg.arrive_time.getHours() BETWEEN -23 AND +23

The statement is a good example of the power of SQL-92 syntax (it uses derived tables, which you will learn about in the next chapter). It is so complex that it can take a few seconds to compile. However, in our database, this is a stored prepared statement, so our application can avoid that compilation time.

The application creates and stores the stored prepared statements in the class JBMSTours.CreateSchema. This one has the name getFullTransferFlightInOneBlow (because it gets both legs of the flight in one statement).

  1. Execute the getFullTransferFlightInOneBlow statement, using the values `SFO' and `GRU' (the airport codes for San Francisco and São Paulo):
  2. Select the Stored Statements icon, and click the plus sign to the left of it to display statements if necessary.
  3. Select GETFULLTRANSFERFLIGHTINONEBLOW.
  4. Fill in the parameters like this:

    SFO, GRU

    Do not enclose the strings in single quotes. Don't forget the comma separate the strings.

    The statement returns rows that consist of the flight ids and segment numbers of two different flights that will be combined to make one transfer flight.

    In Cloudview, you provide parameters with the Parameters box. In ij or an SQL-J Statement, you provide parameters with an SQL-J statement.

    In a Java application, you can set the parameters using the setXXX methods of java.sql.PreparedStatement and avoid all compilation costs. (You will learn about an example class that does this in Compare the Performance of Statements, PreparedStatements, and Stored Prepared Statements.)

Execute a Method That Uses Stored Prepared Statements

You can make any statement a stored prepared statement. The JBMSTours.FlightBuilder class has one static method that uses stored prepared statements, so it should run very quickly. You can execute this method in Cloudview. This class has an alias.

  1. Click the toursDB icon to return to the SQL window. Execute this statement:

    VALUES FlightBuilder.returnAnyFlight(
        getCurrentConnection(), 'SFO','MAD').toString();

    The method returns a String version of a Flight object, not just raw data.

  2. Execute the returnAnyFlight static method to find out if there are any flights (direct or not) between New York and Cairo:

    VALUES FlightBuilder.returnAnyFlight(
        getCurrentConnection(), 'JFK','CAI').toString();

    Look at the source code for the FlightBuilder class to see which stored prepared statements it uses and to see how it sets parameters.

  3. Close the connection and exit Cloudview.

Compare the Performance of Statements, PreparedStatements, and Stored Prepared Statements

Remember InsertComparer? There's a similar class called SelectComparer that compares the performance of using Statements, PreparedStatements, and stored prepared statements.

  1. Open the Java file SelectComparer.java if you wish to examine it. You should have copied into your working directory from the scripts directory.
  2. Compile the program:

    javac SelectComparer.java

  3. Run the program:

    java -Dcloudscape.system.home= your_tutorial_system SelectComparer

    Note the time it took to execute. You should get output looking something like this:

    Compiling and executing 16 statements took 5749 milliseconds.
    Compiling the statement once and executing it 16 times took 751 milliseconds.
    Retrieving a stored prepared statement without compiling it and executing it 16 times took 641 milliseconds.

    The actual time it took will vary, depending on your system. In our environment, using a PreparedStatement provided a substantial performance gain over using a series of Statements, and using a stored prepared statement provided a small performance gain.