In this post, originally written by Glenn Paulley and posted to sybase.com in October of 2009, Glenn talks more about the differences between the JConnect and SQL Anywhere JDBC drivers.
In a previous post I briefly described some of the differences between the jConnect JDBC driver and the iAnywhere JDBC driver when used with SQL Anywhere. A whitepaper on sybase.com summarizes the architectural differences between the two drivers.
Both the jConnect and iAnywhere drivers support JDBC 3.0. jConnect is a "pure Java" solution (termed a Type 4 JDBC driver), while the iAnywhere driver is a Type 1 driver because of its reliance on the SQL Anywhere ODBC driver which must be properly installed. It is sometimes argued that a "pure Java" solution is better/faster/more robust; hence, on paper, jConnect should be "better" than the iAnywhere Type 1 driver. However, if one looks more closely, the significant differences between the two solutions are (1) memory management, (2) the use of the TDS wire protocol, and (3) differences in semantics. We look at each of these in turn.
Memory management
With a pure Java solution:
- All objects are managed by the Java virtual machine.
- Java garbage collection cleans things up automatically. The application programmer does not have to worry about objects sticking around indefinitely, memory leaks or objects disappearing while still in use.
Unfortunately, the weakness of pure Java solutions is the same: memory management. The application programmer has little or no real control over the lifespan of an object. Moreover, the programmer has no effective control over garbage collection; garbage collection can kick in at critical times, resulting in random and unreproducible performance problems.
With a hybrid solution such as the iAnywhere JDBC driver, the most important advantage is memory management:
- The programmer retains full control over non-Java objects.
- Garbage collection can be prevented or postponed by non-Java references to Java objects.
However, as with pure Java, the greatest disadvantage of a hybrid solution is - you guessed it - also memory management. In the hybrid case, non-Java objects need to be managed explicitly; program errors lead to memory leaks at best, and memory corruption or GPFs at worst. Moreover, if Java object references are held too long, Java garbage collection won't kick in.
CMDSEQ versus TDS
jConnect uses Sybase ASE's native wire protocol, the Tabular Data Stream (TDS) protocol, whereas the iAnywhere JDBC driver uses SQL Anywhere's native wire protocol which is called Command Sequence (CMDSEQ). There are both semantic and performance differences between the use of the two protocols; each has advantages and disadvantages.
An advantage of TDS is that it supports "fire hose" cursors. That is, with a single TDS language command token one can instruct the server to execute a set of statements, describe all the result sets, and return all the results in one go to the client. In situations where the application desires all of the rows of a result set(s), a fire hose cursor does offer a performance advantage by reducing the amount of round-trip traffic over the wire. However, this comes at a cost: it is the client that is responsible for caching the result set, and the client that must implement cursor scrolling. The TDS client supports a "window" of rows in the result set that the Java application can scroll through - both forwards and backwards. However, should scrolling occur to a range of rows outside this window the entire request is re-issued to the server - necessary since prior rows outside the "window" have been lost. Hence, in this model with scrollable cursors, cursor sensitivity semantics are impossible to guarantee. Moreover, with very large result sets the communication stream can become blocked if the client cannot process the returned rows quickly enough, which can, in turn, block the server.
While fire-hose cursors give an advantage to jConnect connections under the right circumstances, recently-added support for adaptive prefetching in CMDSEQ (see below) mitigates this advantage. Moreover, there are several additional features supported by the iAnywhere JDBC driver that provide advantages over jConnect. These include:
- TDS is limited to TCP/IP, even for local connections, while the iAnywhere JDBC driver can use either TCP/IP or shared memory. This means that when using jConnect applications cannot automatically start and stop local database servers, since this is supported only with shared memory connections.
- Strong encryption support, including RSA, RSA-FIPS, and ECC encryption technologies.
- Complete server-side cursor support. jConnect does not support server-side cursors; it implements a cursor on the client-side by retrieving the entire result set across the network, even if the client will only use a small number of rows from that result set. When using jConnect, application programmers must be careful to write their SQL queries to return the smallest result set necessary, rather than rely on FETCHing only the first few rows, since the entire result set is sent to the client with each SQL request.
- Complete AppInfo support. jConnect truncates AppInfo details.
- Integrated logins on Windows platforms.
- Richer batch SQL statement support - for example, wide (batch) inserts and wide fetches. With SQL Anywhere, jConnect only fully supports wide fetches. jConnect does support wide inserts from the application, which reduces the amount of network traffic required, but on the server TDS wide inserts are simulated, with each row initiating a separate
INSERT statement. In contrast, the iAnywhere JDBC driver efficiently supports both wide inserts and wide fetches.
Adaptive prefetching with CMDSEQ
SQL Anywhere version 11 introduced adaptive prefetch as a variant of prefetch behaviour with CMDSEQ connections. Prefetch is designed to reduce communication in a client-server environment by transferring sets of rows to the client in advance of a FETCHrequest, and is enabled by default. Prefetching can be disabled outright by specifying the DisableMultiRowFetch connection parameter, or by setting the Prefetch connection option to OFF. Prefetch is turned off for cursors declared with sensitive value semantics.
With adaptive prefetching, a SQL Anywhere CMDSEQ client will automatically adjust the number of rows that are prefetched - increasing or decreasing - depending on application behaviour. A hard limit on the maximum number of rows that will be prefetched is 1000. Adaptive prefetching is also controlled by number of rows the application can FETCHin one elapsed second. Adaptive prefetching is enabled for cursors for which all of the following are true:
- ODBC and OLE DB:
FORWARD ONLY, READ ONLY (default) cursor types; ESQL: DYNAMIC SCROLL (default), NO SCROLL and INSENSITIVE cursor types; all ADO.Net cursors - only
FETCH NEXT operations are done (no absolute, relative or backwards fetching) - the application does not change the host variable type between fetches and does not use
GET DATA to get column data in chunks (but using one GET DATA to retrieve the value is fine).
jConnect semantics
In addition to the automatic setting of connection options to ASE-equivalent settings upon connecting with jConnect - described in my previous post- there are other semantic differences with jConnect. They include:
- The TDS protocol does not support dates or timestamps prior to January 1, 1753.
- Fixed-length
CHAR and BINARY values are automatically padded upon retrieval from blank-padded databases. - With older versions of jConnect, empty string values - strings of length zero - are returned to the application as a string with a single blank in it. This is because earlier versions of TDS did not distinguish between an empty string and the NULL value.
If a JDBC application wanted to use jConnect but not want Sybase ASE-like behaviour, then the application would have to:
- Revert the connection option settings issued by the sp_tsql_environment() system procedure by setting these options temporarily immediately after connecting.
- Set the connection option
RETURN_DATE_TIME_AS_STRING to ON in order to get SQL Anywhere to always return DATE/TIME/TIMESTAMP values as strings. This is to overcome the inability of TDS to handle dates prior to January 1, 1753. - Set the jConnect option "dynamic prepare" to TRUE to make sure prepared statements are not re-PREPAREd every time they are used.
- Set a cursor name for each statement in order to force jConnect to use TDS cursors instead of fire-hose cursors. Note that with SQL Anywhere, jConnect will still cache result sets on the client regardless of which cursor type is used.
- Set the fetch size explicitly on every statement in order to get jConnect to mimic CMDSEQ prefetch behaviour.
- For older versions of jConnect:
- Handle 'single-blank strings' as empty strings.
- Refrain from using unsigned data types, since unsigned values are not supported with older jConnect releases.
In a subsequent post I'll outline performance differences between the jConnect and iAnywhere drivers. In our experience with customer applications, most applications benefit from a significant performance boost by switching to the iAnywhere JDBC driver, occasionally up to a factor of two, depending on the nature of the application and the precise sequence of JDBC API calls issued by the application.
My thanks to colleague Karim Khamis for providing me with the background for this article.
