The recently released SQL Anywhere 17, contains a wide variety of new features that improve performance for a variety of activities, improve the security and robustness of the database server and clients, and provide some new tools to improve developer productivity. You can find a nice overview of version 17 here.
In order to fully appreciate the application and implications of some of the changes in version 17, I have decided to post some detailed analysis of select features, comparing version 17 to previous versions, highlighting differences and improvements, and attempting to provide best practices in relation to the subject matter where possible. The first topic I have chosen to cover is autocommit.
Auto-commit is a database client connection option that governs when commits are issued for transactions. When it comes to best practices in SQL Anywhere, the recommendation is that auto-commit be turned off wherever possible. This is because a commit operation causes an IO, and IO is one of the most expensive things you can do in a traditional RDBMS. Not using auto-commit generally results in fewer commits, and therefore better performance of the database server. In addition, creating and managing transactions that line up with your business processes allows you to better control when commits occur, can help to ensure a more consistent state of your database in relation to your business rules.
Even though not using auto-commit is preferred, most applications are actually built with auto-commit enabled. There are a couple of reasons for this. It is often easier to commit after every operation, and it is appealing to developers to get immediate feedback as to whether their database change was successful or not. In addition, many database interfaces turn auto-commit on by default, meaning application developers also build auto-commit style applications by default. Once built, it is sometimes very difficult to re-architect an application to do explicit commits.
Prior to version 17, if an application turned on auto-commit, the client api (eg. ODBC, JDBC, etc…) would issue an explicit “COMMIT” after every database request. To avoid this extra communication with the server and improve overall performance, a new option was added to version 17 called (surprise!) auto_commit. The option is OFF by default and can only be set locally for the duration of a connection (ie. you cannot set a PUBLIC auto_commit option). However, if an application sets the auto_commit option to ON, then the SQL Anywhere Server will automatically commit after every request.
All of the SQLA client interfaces have been updated to take advantage of this new option automatically. The version 17 SQL Anywhere ODBC, JDBC and OLEDB drivers automatically set the new auto_commit option if connected to a version 17 server when the application issues the corresponding AutoCommit API call for each of the drivers. These drivers will revert back to handling auto commit on the client side if the target server is version 16 or below. For APIs (for example ESQL) where autocommit was not previously supported, these application can now use the new auto_commit option to have the server automatically commit after each execution if desired.
Performance Implications
The following example tells the server to auto commit after every request:
SET TEMPORARY OPTION auto_commit = ‘ON’
To determine the performance impact of this option, I ran a simple test using the PerformanceInsert sample that ships with SQL Anywhere. I used a table with a single integer primary key column and inserted 100000 rows on my laptop.
CREATE TABLE ins( c1 integer NOT NULL PRIMARY KEY );
First I ran the test with 1 commit (at the end of the test):
instest -cdba,sql -o ins.out -r 100000 -x -v 1
My second test run was committing after every row:
instest -cdba,sql -o ins.out -r 100000 -x -v 1 -m 1
For my third test, I altered the source code for instest and added a command to turn on auto_commit after connecting to the database:
EXEC SQL EXECUTE IMMEDIATE 'SET TEMPORARY OPTION auto_commit=ON';
…and then I ran the test with no explicit commits:
instest -cdba,sql -o ins.out -r 100000 -x -v 1
Here are the results:
Local machine Connection | Insert Time (s) | Commit Time (s) | Total Time (s) |
|---|
1 Commit | 9.669 | 0.18 | 9.849 |
Commit every row from the client | 11.185 | 22.911 | 34.096 |
Commit every row with auto_commit=’ON’ | * | * | 30.029 |
As we can see, not committing after every operation can have a huge impact on performance. However, if your application does use auto_commit, over a same machine connection, the test demonstrates a ~13% improvement in performance between having the client issue a commit after every operation, or having the server automatically do the commit after every operation. If we use a network server and tcpip, the difference is even more pronounced, at a ~17% difference in performance (chart below)
TCP/IP Connection | Insert Time (s) | Commit Time (s) | Total Time (s) |
|---|
1 Commit | 13.486 | 0.246 | 13.732 |
Commit every row from the client | 14.615 | 26.467 | 41.082 |
Commit every row with auto_commit=’ON’ | * | * | 35.222 |
*Because the server is executing the commit operation, the instest program cannot separate the commit and insert execution times
Lab testing of other scenarios (which include more mixed workload activities) has shown as much as a 25-30% improvement in performance of the commit operation, when the server-side auto_commit option is used as opposed to the client based auto_commit operations.
A Final Note - Chained vs. Autocommit
The auto_commit option is very different from the database chained option. Setting chained=OFF will force the server to commit after each statement executed in the server, including each individual statement within a procedure, whereas setting auto_commit ON will force the server to commit only after each statement from the client. In the case of a procedure the commit would happen after the entire procedure has completed execution when auto_commit is on.
