Java Programming with Oracle JDBC

SQL92 Token Parsing

Like auto-commit, SQL92 escape syntax token parsing is on by default. In case you don’t recall, SQL92 token parsing allows you to embed SQL92 escape syntax in your SQL statements (see Section 9.3.3 in Chapter 9). These standards-based snippets of syntax are parsed by a JDBC driver transforming the SQL statement into its native syntax for the target database. SQL92 escape syntax allows you to make your code more portable -- but does this portability come with a cost in terms of performance?

Table 19-2 shows the number of milliseconds needed to insert 1,000 rows into the TESTXXXPERF table. Timings are shown with the SQL92 escape syntax parser on and off for both the OCI and Thin drivers. As before, these timings represent the result of three program runs averaged together.

Table 19-2. SQL92 token parser timings (in milliseconds)

SQL92 parser	OCI	Thin
On	2,567	2,514
Off	2,744	2,550

Notice from Table 19-2 that with the OCI driver we lose 177 milliseconds when escape syntax parsing is turned off, and we lose only 37 milliseconds when the parser is turned off with the Thin driver. These results are the opposite of what you might intuitively expect. It appears that both drivers have been optimized for SQL92 parsing, so you should leave it on for best performance.

Now that you know you never have to worry about turning the SQL92 parser off, let’s move on to something that has some potential for providing a substantial performance improvement.