Programming for Performance
Page 2 of 4

Prepared Statements

A prepared statement is an SQL-J statement that is compiled once for a particular Connection and is then available to that Connection to be executed many times until explicitly or implicitly closed.

Using JDBC, you create a PreparedStatement, which you explicitly close with the close method, or implicitly close by disconnecting. It is also implicitly closed when garbage-collected.

Compilation is a time-intensive process composed of four stages:

  • Parsing

    The stage in which the SQL-J language parser reads the text of the statement, determines which words are commands, which are identifiers, and which are valid expressions, checks for valid syntax, and generates an internal tree representation of the statement.

  • Binding

    The stage in which Cloudscape verifies the names of columns, tables, and other database objects found in the statement, determines the data types of all expressions in the statement, and notes dependencies.

  • Optimization

    The stage in which Cloudscape chooses the access path for statement execution (whether to use an index, which tables to scan, etc.), join order, locking granularity, and notes dependencies on indexes.

  • Code generation

    The final stage in compilation, in which Cloudscape generates the actual code to execute.

Execution is the actual evaluation of the statement.

For example, consider the following SQL-J statement:

SELECT rooms_taken
FROM HotelAvailability
WHERE hotel_id = 10
AND booking_date = DATE'1998-06-09'

In the compilation stage, Cloudscape takes the following actions:

  • parses the text of the string, finding the keywords SELECT, FROM, WHERE, and AND, along with identifiers and literals
  • verifies the names of the columns and the tables, checks data types and verifies that the data type of hotel_id is the same as that of the literal 10 and that the data type of booking_date is the same as the literal Date'1998-06-09', and notes that the statement depends on the HotelAvailability table (you won't be able to delete that table until you're finished with the statement)
  • determines that it can make use of the primary key backing index on the hotel_id and booking_date columns
  • generates code that it will use to evaluate the statement

In the execution stage, Cloudscape actually evaluates the statement; it retrieves records from the HotelAvailability table that match the search condition (using the index to shorten retrieval time), filters out all columns except rooms_taken, and then returns results.

If an application executes the same statement more than once, it will achieve substantial performance improvements by using a prepared statement instead of a statement, because it avoids repeat compilation time.

Often an application executes statements that are similar (but not exactly alike) more than once. For example, the JBMSTours application executes a statement similar to the one shown above every time it checks on the availability of a particular hotel on a particular date. However, the application does not know in advance that the actual hotel_id to look for will be 10 or that the date will be Date'1998-06-09'. Instead, it prepares a single PreparedStatement once that uses the dynamic or IN parameter construct of the PreparedStatement interface. Instead of using the literals 10 or Date'1998-06-09', the application uses ?s (placeholders) for these two parameters.

Each time it executes the statement, it provides the actual values of these variables.

Using parameters, the application could prepare the following statement:

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

To look up the number of rooms taken for hotel number 5 on January 9, 1998, the application fills in the particular parameters using JDBC methods, not SQL-J, and then executes the precompiled statement.

PreparedStatement ps = conn.prepareStatement(
    'SELECT rooms_taken FROM HotelAvailability' +
    'WHERE hotel_id = ? AND booking_date = ?');
ps.setInt(1, 5); ps.setDate(2, new java.sql.Date(98,1,9); ResultSet rs = ps.executeQuery();

Dynamic parameters (?) cannot be used indiscriminately within PreparedStatements; Cloudscape has to be able to infer data type information about each parameter. In the above statement, Cloudscape infers that the first parameter is an INTEGER data type (the type of hotel_id) and that the second parameter is a DATE data type (the type of booking_date). Cloudscape expects the application to provide the JDBC data types that correspond to those types and to use the appropriate setXXX methods for those types. (You will learn about those methods in a later task.) At execution time, supplying parameters of other data types may cause an error. (Cloudscape handles some conversions automatically, such as to and from string data types and between numerical data types.)

Quiz: Can Cloudscape infer the data type of the dynamic parameter in the following SQL-J statements?

SELECT ? FROM HotelAvailability

VALUES (CLASS myClass).overloadedMethod(?)

Answer: No. The HotelAvailability table has many columns of differing data types. The ? could stand for any of those columns, so this statement is not allowed. In the second example, the method is overloaded, and Cloudscape cannot guess the data type of the parameter. You can get around this second limitation by using the CAST command to cast its data type:

VALUES (CLASS myClass).overloadedMethod(CAST (? AS DECIMAL))

Prepare a Statement in ij

In this task, you will use ij to prepare and execute prepared statements.

  1. Start ij and connect to toursDB following the instructions in Start ij and Connect to toursDB.
  2. Turn off auto-commit.

    autocommit off;

    Ij allows you to create a prepared statement using the following syntax:

    PREPARE Identifier AS String;

    where Identifier is your name for the prepared statement and String is the text of the statement. The prepared statement is known by this name only within ij.

  3. Prepare the statement in the above example, calling it findRoomsTaken:

    PREPARE findRoomsTaken AS
        'SELECT rooms_taken
         FROM HotelAvailability
         WHERE hotel_id = ?
         AND booking_date = ?';

Execute a Prepared Statement in ij

After you prepare a statement in ij, you execute it by using ij's Execute command and the name you gave it. To supply the values of dynamic parameters, you provide a result set within a string with the expected number of columns--the results of a query--using an SQL-J statement string. The statement will be executed once for each row in the result set.

In the case of findRoomsTaken, you would need to supply a result set with two columns: the first of the INTEGER data type, the second of the DATE data type. The easiest way to do this is to use a VALUES clause.

For example, to provide the parameters 10 and today's date, you could use:

VALUES (10, current_date)

  1. Execute the command using the parameters 10 and today's date:

    execute findRoomsTaken using 'VALUES (10, current_date)';

    Note: If no rooms have been booked for that hotel on that particular date, the query won't return anything.

  2. Execute the command using the parameters 50 and a date of your choice:

    execute findRoomsTaken using 'VALUES (50, DATE''1998-08-15'')';

    (The date literal inside a string requires doubled single quotes.)

    Alter the values you supply until you actually get some results.

    You can also supply the values from a query. We could supply values to findRoomsTaken using a simple query from the HotelBookings table.

    SELECT hotel_id, arrival FROM HotelBookings

    Depending on how many times you ran JBMSTours.BuildATour, this table may contain more than one row.

  3. Execute findRoomsTaken using a SELECT statement:

    execute findRoomsTaken using
        'SELECT hotel_id, arrival FROM HotelBookings';

    Quiz: Does the above ij statement avoid all compilation?

    Answer: No. Cloudscape has to compile the new SELECT statement used to provide the parameter values.

  4. Disconnect:

    disconnect;

  5. Shut down the system and exit ij with the exit command:

    exit;

Such a construct does not actually save you much compilation time, because Cloudscape has to compile the SELECT statement you used to provide the result set.

A Java programmer does not have the same limitation. In a Java program, you use the methods of java.sql.PreparedStatement to provide the parameter values, which do not take any Cloudscape compilation time.

In the next section, you will work with a Java program.

Compare the Insert Performance of PreparedStatements to that of Statements

Prepared statements are a good choice for INSERT statements, which are typically executed several times.

In this task, you will use a Java program to insert data into the HotelBookings table using a Statement and then a PreparedStatement.

  1. Open the Java file InsertComparer.java, which you should have copied into your working directory from the scripts directory.

    This simple program connects to toursDB, inserts 16 rows into the HotelBookings table using 16 different statements, and then prints the time it took to accomplish the inserts.

    Then it prepares a single prepared statement, executes it 16 different times with different parameters, and then prints the time it took to accomplish the inserts.

  2. Look at the first INSERT statement.

    s.executeUpdate("INSERT INTO HotelBookings VALUES (250, 10, DATE '1997-10-15', DATE '1997-10-20', 3, 1000.00)");

    When Cloudscape compiles the statement, it must determine the data type of all six values and compare them to the data types of the six columns in the table. That is a lot of work.

  3. Look at the PreparedStatement and the first execution of it:

    PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO HotelBookings VALUES (?, ?, ?, ?, ?, ?)");
    ps.setInt(1, 250);
    ps.setInt(2, 10);
    ps.setDate(3, new java.sql.Date(98, 11, 15));
    ps.setDate(4, new java.sql.Date(98, 11, 20));
    ps.setInt(5, 3);
    ps.setObject(6, new java.math.BigDecimal(1000.00));
    ps.executeUpdate();

    When Cloudscape compiles the PreparedStatement, it determines what data type each of the dynamic parameters must be.

    Each time the application executes the statement, it provides values using the setXXX methods of the java.sql.PreparedStatement interface.

    There is one set method for each standard data type in java.sql, plus one for Java objects. These methods take two parameters: the first is the number of the parameter, and the second is the actual value to supply. The parameters are ordered from left to right, starting with 1. The application takes responsibility for providing a value of the expected type with the expected setXXX method.

  4. Compile the program:

    javac InsertComparer.java

    (You may get a note about deprecated APIs, depending on the version of the JDK that you are using. You can ignore these messages.)

  5. Run the program:

    java -Dcloudscape.system.home= your_tutorial_system InsertComparer

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

    Compiling and executing 16 statements took 4917 milliseconds.
    Compiling the statement once and executing it 16 times took 601 milliseconds.

    The actual time it took will vary, depending on your system. In our environment, using a PreparedStatement provided a substantial performance gain.