![]() |
Tuning Databases and Applications
|
Reference Manual |
Storing Prepared Statements to Improve PerformanceYou can name and store prepared statements in a database. Storing prepared statements, which are already compiled, helps you avoid costly compilation time and speeds up query execution for the end user.
Creating a Stored Prepared StatementWhen you create a stored prepared statement with the CREATE STATEMENT command, Cloudscape prepares the statement, tags it with the name you give it, and stores the compiled code in a system table. For example:
CREATE STATEMENT getFullFlightInfo NOTE: Create stored prepared statements after data are loaded into the referenced table, or recompile them after data is loaded. Creating a stored prepared statement when the referenced tables are empty leads to a bad plan. (See Stale Stored Prepared Statements.) You may optionally provide sample values for parameters in the CREATE STATEMENT statement. This allows you to provide the optimizer with representative data so that the optimizer can choose a plan that will be suitable for the real values that will be used by the statement. Without these values, the optimizer makes guesses as to the selectivity of the parameters. Providing sample values (with the USING clause) is particularly useful if you have a good idea of how the statement will be used in the future. For example:
CREATE STATEMENT getFullFlightInfo Providing sample values with the USING clause is not useful for INSERTs (unless the INSERT also includes a SELECT). It is useful only for SELECT, UPDATE, or DELETE statements with WHERE clauses. This is because an INSERT statement will not get a different plan given differing amounts of data in the target table of the insert. Executing a Stored Prepared StatementAfter you create the stored prepared statement, you can invoke it using its name. Cloudscape can execute the statement without having to recompile it, unless you explicitly ask it to or some dependencies have been broken (see Invalid Stored Prepared Statements and Recompilation). In your application, if the statement takes parameters, "prepare" an EXECUTE STATEMENT statement that references the stored prepared statement, then provide runtime values with the JDBC methods. Or, for development purposes, you can execute the statement with an EXECUTE STATEMENT statement with a USING clause, which allows you to provide the parameters directly. If the statement does not take parameters, you can simply execute it. The "compilation cost" of an EXECUTE STATEMENT statement is the time it takes to retrieve the plan from the data dictionary and is unmeasurable after the first access. // Execute a prepared statement via JDBC PreparedStatement getFullFlightInfo = conn.prepareStatement( Cloudscape allows you to provide parameters in the EXECUTE STATEMENT statement itself with another SQL statement such as a VALUES clause. This is useful when you are developing, but takes away many of the performance advantages of using stored prepared statements because of the cost of compiling the VALUES clause. Provide parameters with JDBC instead. For more information about stored prepared statements and examples showing how to create and use them, see CREATE STATEMENT statement and EXECUTE STATEMENT statement of the Cloudscape Reference Manual. Additional Benefits for Multiple Connections: The Stored Prepared Statement CacheStored prepared statements run faster than other kinds of statements because they allow you to avoid compilation. They also provide additional performance benefits in a multi-user situation because they reduce memory use. In a database with more than one Connection, each Connection shares the same object in memory for a stored prepared statement, caching only individual parameter information, not the entire object. This can improve performance significantly in multi-user databases because of the reduction in memory use. There is one stored prepared statement cache per Cloudscape database. You can configure the size of the stored prepared statement cache; see cloudscape.language.spsCacheSize. For databases that have a lot of frequently used stored prepared statements, increase the size of this cache; the default size is 32. The size of this cache never needs to be larger than the number of different stored prepared statements in a database. You can find out the number of stored prepared statements in a database with this query: SELECT COUNT(*) FROM SYS.SYSSTATEMENTS Figure 3-3 Multiple connections to a database can share the same statement execution plan and the same memory space when using stored prepared statements. Figure 3-4 For PreparedStatements, each connection must compile the statement, and each connection must have a copy of the statement execution plan. For examples of using stored prepared statements, see the JBMSTours sample application and the chapter on programming for performance in Learning Cloudscape: The Tutorial. Invalid Stored Prepared Statements and RecompilationStored prepared statements, like other prepared statements, can become invalid. When a stored prepared statement is invalid, it is compiled the next time it is prepared or executed. Compilation happens automatically in the background and may be the cause of a slow execution. Once a stored prepared statement is re-compiled, however, it shouldn't need any more automatic recompilation unless it becomes invalid again. A statement becomes invalid when one of its dependencies is broken through some kind of change in the database schema. For example, dropping an index, table, or view on which the statement depends breaks the statement's dependencies. As an example, let's look at the following stored prepared statement:
CREATE STATEMENT getFullFlightInfo This statement depends on the following dictionary objects:
The Cloudscape Reference Manual details the dependencies for all dictionary objects and commands. Look for the heading "Dependency System" under the man page for a command. For an example, see the man page for CREATE INDEX statement in the Cloudscape Reference Manual. To avoid end-user inconvenience, make sure that no stored prepared statements are invalid before deploying your application and database. You must make sure that no stored prepared statements are invalid before deploying a read-only database. Invalid stored prepared statements require writing to disk, which will fail in a read-only environment. You can automatically recompile all stored prepared statements with this command: You can automatically recompile all invalid stored prepared statements with this command: ALTER STATEMENT RECOMPILE INVALID You can recompile a single statement by specifying its name: ALTER STATEMENT GetFullFlightInfo RECOMPILE Stale Stored Prepared StatementsThe preceding section, Invalid Stored Prepared Statements and Recompilation, discussed situations in which Cloudscape marked statements as invalid and automatically recompiled them. There are certain situations in which the statement is technically valid but its plan is stale, because it is no longer the best plan for executing the statement. These situations are caused not by changes in the database schema (which Cloudscape does detect), but by changes in the data layout. For example, if you have an empty table or a table with only a few rows in it, Cloudscape's optimizer may decide that it is easier to do a table scan than to access the data through an index. After you have loaded several thousand rows, however, a different statement execution plan is in order. Follow the directions in the preceding section, Invalid Stored Prepared Statements and Recompilation, to recompile all or individual stored prepared statements. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |