Improving database performance

Before any SQL statement is executed, the runtime prepares (compiles) it to determine the steps that are performed internally to carry out the statement. When you call SQLStatement.execute() on a SQLStatement instance that hasn’t executed previously, the statement is automatically prepared before it is executed. On subsequent calls to the execute() method, as long as the SQLStatement.text property hasn’t changed the statement is still prepared. Consequently, it executes faster.

In order to gain the maximum benefit from reusing statements, if values need to change between statement executions, use statement parameters to customize your statement. (Statement parameters are specified using the SQLStatement.parameters associative array property.) Unlike changing the SQLStatement instance’s text property, if you change the values of statement parameters the runtime isn’t required to prepare the statement again. For more information about using parameters in statements, see Using parameters in statements.

Because preparing and executing a statement is an operation that is potentially demanding, a good strategy is to preload initial data and then execute other statements in the background. Load the data that the application needs first. When the initial start-up operations of your application have completed, or at another “idle” time in the application, execute other statements. For instance, if your application doesn’t access the database at all in order to display its initial screen, wait until that screen displays, then open the database connection, and finally create the SQLStatement instances and execute any that you can. Alternatively, suppose when your application starts up it immediately displays some data, such as the result of a particular query. In that case, go ahead and execute the SQLStatement instance for that query. After the initial data is loaded and displayed, create SQLStatement instances for other database operations and if possible execute other statements that are needed later.

When you’re reusing a SQLStatement instance, your application needs to keep a reference to the SQLStatement instance once it has been prepared. To keep a reference to the instance, declare the variable as a class-scope variable rather than a function-scope variable. One good way to do this is to structure your application so that a SQL statement is wrapped in a single class. A group of statements that are executed in combination can also be wrapped in a single class. By defining the SQLStatement instance or instances as member variables of the class, they persist as long as the instance of the wrapper class exists in the application. At a minimum, you can simply define a variable containing the SQLStatement instance outside of a function so that the instance persists in memory. For example, declare the SQLStatement instance as a member variable in an ActionScript class or as a non-function variable in a JavaScript file. You can then set the statement’s parameter values and call its execute() method when you want to actually run the query.

Suppose you’re executing a large number of SQL statements that involve adding or changing data (INSERT or UPDATE statements). You can get a significant increase in performance by executing all the statements within an explicit transaction. If you don’t explicitly begin a transaction, each of the statements runs in its own automatically created transaction. After each transaction (each statement) finishes executing, the runtime writes the resulting data to the database file on the disk. On the other hand, consider what happens if you explicitly create a transaction and execute the statements in the context of that transaction. The runtime makes all the changes in memory, then writes all the changes to the database file at one time when the transaction is committed. Writing the data to disk is usually the most time-intensive part of the operation. Consequently, writing to the disk one time rather than once per SQL statement can improve performance significantly.

Using the following techniques can prevent unneeded work on the part of the database engine and make applications perform better:

• Always explicitly specify database names along with table names in a statement. (Use “main” if it’s the main database). For example, use SELECT employeeId FROM main.employees rather than SELECT employeeId FROM employees. Explicitly specifying the database name prevents the runtime from having to check each database to find the matching table. It also prevents the possibility of having the runtime choose the wrong database. Follow this rule even if a SQLConnection is only connected to a single database, because behind the scenes the SQLConnection is also connected to a temporary database that is accessible through SQL statements.

• Always explicitly specify column names in a SELECT or INSERT statement.

• Break up the rows returned by a SELECT statement that retrieves a large number of rows: see Retrieving SELECT results in parts.

If possible, avoid changing the schema (table structure) of a database once you’ve added data into the database’s tables. Normally a database file is structured with the table definitions at the start of the file. When you open a connection to a database, the runtime loads those definitions. When you add data to database tables, that data is added to the file after the table definition data. However, if you make schema changes such as adding a column to a table or adding a new table, the new table definition data is mixed in with the table data in the database file. If the table definition data is not all at the start of the database file, it takes longer to open a connection to the database as the runtime reads the table definition data from different parts of the file.

If you do need to make schema changes, you can call the SQLConnection.compact() method after completing the changes. This operation restructures the database file so that the table definition data is located together at the start of the file. However, the compact() operation can be time-intensive, especially as a database file grows larger.