Even if your application doesn’t execute any statements when it first loads, instantiate a SQLConnection object and call its open() or openAsync() method ahead of time (such as after the initial application startup) to avoid delays when running statements. See Connecting to a database.

If you access a certain database throughout the execution time of your application, keep a reference to the SQLConnection instance, and reuse it throughout the application, rather than closing and reopening the connection. See Connecting to a database.

When writing data-access code, it can be tempting to execute operations synchronously rather than asynchronously, because using synchronous operations frequently requires shorter and less complex code. However, as described in Using synchronous and asynchronous database operations, synchronous operations can have a performance impact that is obvious to users and detrimental to their experience with an application. The amount of time a single operation takes varies according to the operation and particularly the amount of data it involves. For instance, a SQL INSERT statement that only adds a single row to the database takes less time than a SELECT statement that retrieves thousands of rows of data. However, when you’re using synchronous execution to perform multiple operations, the operations are usually strung together. Even if the time each single operation takes is very short, the application is frozen until all the synchronous operations finish. As a result, the cumulative time of multiple operations strung together may be enough to stall your application.

Use asynchronous operations as a standard approach, especially with operations that involve large numbers of rows. There is a technique for dividing up the processing of large sets of SELECT statement results, described in Retrieving SELECT results in parts. However, this technique can only be used in asynchronous execution mode. Only use synchronous operations when you can’t achieve certain functionality using asynchronous programming, when you’ve considered the performance trade-off that your application’s users will face, and when you’ve tested your application so that you know how your application’s performance is affected. Using asynchronous execution can involve more complex coding. However, remember that you only have to write the code once, but the application’s users have to use it repeatedly, fast or slow.

In many cases, by using a separate SQLStatement instance for each SQL statement to be executed, multiple SQL operations can be queued up at one time, which makes asynchronous code like synchronous code in terms of how the code is written. For more information, see Understanding the asynchronous execution model.

For any SQL statement that is executed more than once in an application, create a separate SQLStatement instance for each SQL statement. Use that SQLStatement instance each time that SQL command executes. For example, suppose you are building an application that includes four different SQL operations that are performed multiple times. In that case, create four separate SQLStatement instances and call each statement’s execute() method to run it. Avoid the alternative of using a single SQLStatement instance for all SQL statements, redefining its text property each time before executing the statement. See Use one SQLStatement instance for each SQL statement for more information.

Use SQLStatement parameters—never concatenate user input into statement text. Using parameters makes your application more secure because it prevents the possibility of SQL injection attacks. It makes it possible to use objects in queries (rather than only SQL literal values). It also makes statements run more efficiently because they can be reused without needing to be recompiled each time they’re executed. See Using parameters in statements for more information.

When you don’t specify an itemClass for a SQLStatement, to avoid spelling errors, define String constants containing a table’s column names. Use those constants in the statement text and for the property names when retrieving values from result objects. Also use constants for parameter names.