Optimizing GBase 8s JDBC with the OPTOFC Parameter

OPTOFC (optimize-OPEN-FETCH-CLOSE) is a JDBC option in GBASE's GBase 8s that reduces network round trips when executing SELECT statements with PreparedStatement. This post explains how it works and demonstrates the real performance gain with benchmarks.

When OPTOFC Takes Effect

The optimization kicks in only when all of the following are true:

1. The statement is a PreparedStatement instance.


2. The query is a SELECT.


3. The ResultSet type is TYPE_FORWARD_ONLY.


4. The concurrency mode is CONCUR_READ_ONLY.

What It Does

1. Merges OPEN and FETCH messages — Normally these are two separate network requests. With OPTOFC, they are sent together, saving one round trip. (Without variable‑length columns the driver may already merge them; with variable‑length columns you must enable OPTOFC=1 to force the merge.)


2. 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 ran the same SELECT * query 1,000 times inside a loop. Two tables were used: t1(name varchar(10)) (variable‑length) and t2(name char(10)) (fixed‑length).

Example 1 — Fixed‑length column (char)

• Without OPTOFC: 2,822 ms
  


• With OPTOFC=1: 1,697 ms — ~40% faster
  

Example 2 — Variable‑length column (varchar)

• Without OPTOFC: 2,276 ms (base is lower because OPEN/FETCH are merged by default for varchar)


• With OPTOFC=1: 1,679 ms — still about 26% faster
  

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 stayed at 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's a simple URL change with no code modifications required, making it a low‑effort win for many gbase database applications.

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