Optimizing GBase 8s JDBC with the OPTOFC Parameter

Source: Dev.to

When OPTOFC Takes Effect

The optimization is applied only when all of the following conditions are met:

• The statement is a PreparedStatement instance.
• The query is a SELECT.
• The ResultSet type is TYPE_FORWARD_ONLY.
• The concurrency mode is CONCUR_READ_ONLY.

What It Does

• Merges OPEN and FETCH messages – Normally these are two separate network requests. With OPTOFC, they are sent together, saving one round trip. (For variable‑length columns the driver may already merge them; setting OPTOFC=1 forces the merge.)
• Auto‑closes the cursor – If all rows are fetched from the server, the server closes the cursor automatically. The subsequent ResultSet.close() call then avoids an extra network request.

How to Enable It

Add OPTOFC=1 to the JDBC URL:

jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:OPTOFC=1

Performance Benchmarks

The tests executed the same SELECT * query 1,000 times inside a loop. Two tables were used:

• t1(name varchar(10)) – variable‑length column
• t2(name char(10)) – fixed‑length column

Example 1 — Fixed‑length column (char)

Example 2 — Variable‑length column (varchar)

Example 3 — When it does NOT work (TYPE_SCROLL_INSENSITIVE)

The URL included OPTOFC=1, but the ResultSet type was set to TYPE_SCROLL_INSENSITIVE, breaking condition 3. Execution time remained 2,848 ms, identical to the unoptimized path.

Key Takeaway

OPTOFC=1 delivers a measurable speedup for forward‑only, read‑only SELECT queries, especially when variable‑length columns are involved. It requires only a URL change, with no code modifications, making it a low‑effort win for many GBase 8s applications.

If you’re using GBase 8s in a JDBC workload, verify your ResultSet types and connection strings—you might be leaving free performance on the table.