This page contains the top 5 recommendations for your coding practices to achieve optimal performance in using the Oracle TimesTen product.

- Milliseconds count - use direct-linked connections for best response time - Prepare queries and bind columns and parameters in advance - Tune your application connections and use connection pooling - Close cursor promptly - Turn OFF AutoCommit and Commit Regularly

Milliseconds count - Use Direct-linked Connections for Best Response Time

If you have control on how your application accesses the TimesTen database and if you can run your application on the same machine as the TimesTen database, we recommend that you use the direct-linked connection mode. Direct-linked applications can achieve significantly better performance than client/server applications because database operations are executed directly from the application process’ address space without incurring inter-process communication and network roundtrip overhead.

TimesTen supports multi-process and multi-threaded applications, a TimesTen database can be shared across processes that are using direct-linked and client/server connection modes.

For Java applications that utilize the JDBC interface, it is simple to use direct-linked connection mode. Just change your JDBC connection string to the form of “jdbc:TimesTen:direct:…”

For applications accessing data cached from the Oracle Database, the use of a direct-linked connection improves your application’s performance the same way it does for TimesTen data that is not cached from the Oracle Database.

If your application cannot use a direct-linked connection, the use of a client/server connection still provides significantly higher performance than accessing a conventional disk-based relational database. This is primarily due to the TimesTen product being designed and optimized for in-memory data structure and layout.

Prepare All SQL Queries and Bind Columns/Parameters In Advance

For best performance, you should prepare all SQL statements that are executed more than once in advance. This is true for all relational databases, but for TimesTen and its extremely fast transaction rates, the time spent to compile a statement can actually take several times longer than it takes to execute it.

In addition to preparing statements in advance, you should bind all input parameters and output columns for those SQL statements in advance:

Tune Your Connections and Use Connection Pooling

If your application is multithreaded and opens multiple connections to the same TimesTen database, you should pay attention to and manage your connections carefully. As an in-memory database, TimesTen uses available processor resources efficiently and is, in general, CPU-bound (as supposed to disk-bound for many applications running on disk-based RDBMS). In general, it is difficult to achieve best performance if there are more concurrent connections active simultaneously than there are CPUs on your machine to handle the operations, and unnecessary concurrent connections may result in lower application throughput.

If your application requires a lot of connections, you should ensure that connections are held efficiently for the transactions. Efficient usage of connections via a connection pool is better than having lots of idle connections. For examples of connection poolings using TTClasses and JDBC, see the /developer/connection_pools directory under Quick Start. Also, commercial J2EE application servers typically use connection pooling by default.

Close Cursor Promptly

Similar to the Oracle Database, you do not need to commit TimesTen read-only transactions. However, it is important to close read-only cursors promptly in order to release all resources (such as temporary space used for a sort operation) held by a read-only SQL query.

The following methods close a SQL cursor:

Turn Off AutoCommit and Commit Regularly

By default in ODBC and JDBC, all connections to the database have autocommit turned on. This means that each individual SQL statement is committed in its own transaction. By turning autocommit off and explicitly committing multiple SQL statements in a single transaction, an application's performance can be greatly improved. This makes particular sense for large operations, such as bulk inserts or bulk deletes. (TTClasses turns off autocommit as by default.)

However, it is also possible to group too many operations into a single transaction where locks are held for a much longer time for each transaction, resulting in much less concurrency in the system. In general, bulk insert, update and delete operations tend to work most effectively when you commit every few thousand rows

Refer to the Oracle TimesTen In-Memory Database C Developer's Guide and Oracle TimesTen In-Memory Database Java Developer's Guide for additional application tuning information.