DEV Community

Michael
Michael

Posted on

Boost JDBC Performance with GBase 8s: Understanding IFX_AUTOFREE

When working with JDBC, repeatedly creating and closing ResultSet and Statement objects can generate unnecessary network round trips. The GBase 8s JDBC driver offers a handy parameter — IFX_AUTOFREE — that helps reduce this overhead and improves performance in high‑query scenarios.

What Does IFX_AUTOFREE Do?

IFX_AUTOFREE is a boolean parameter (0 = disabled, 1 = enabled). When enabled, the server automatically frees the cursor as soon as the client calls ResultSet.close(). Consequently, when Statement.close() is later invoked, the JDBC driver does not send an extra cursor‑release message to the server, saving one network round trip per statement.

How to Enable It

You can enable IFX_AUTOFREE in two ways:

1. Via the JDBC URL

jdbc:gbasedbt-sqli://:/:IFX_AUTOFREE=1;DB_LOCALE=en_US.819

text

2. Via a Properties Object 

java
Properties props = new Properties();
props.put("IFX_AUTOFREE", "1");
Connection conn = DriverManager.getConnection(url, props);
Enter fullscreen mode Exit fullscreen mode

When to Use It
This parameter shines in scenarios where you run many short queries, such as in loops that repeatedly execute SELECT statements and immediately close the result set. By eliminating one network round trip per query, it can significantly boost throughput.

Performance Test
The following examples compare execution times for 1000 queries under different configurations.

  1. Without IFX_AUTOFREE (Baseline) 
java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=0;DB_LOCALE=en_US.819";
// Inside loop: try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM t1");
//                ResultSet rs = ps.executeQuery()) { rs.next(); }
// Output: time:2898 ms
Enter fullscreen mode Exit fullscreen mode
  1. With IFX_AUTOFREE=1 
java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;DB_LOCALE=en_US.819";
// Same loop using PreparedStatement + ResultSet
// Output: time:2343 ms
Result: ~19% faster than baseline.
Enter fullscreen mode Exit fullscreen mode
  1. Using Statement Instead of PreparedStatement (with AUTOFREE=1) 
java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;DB_LOCALE=en_US.819";
// Inside loop: try (Statement stmt = conn.createStatement();
//                ResultSet rs = stmt.executeQuery("SELECT * FROM t1")) { rs.next(); }
// Output: time:2376 ms
Performance is similar to the PreparedStatement case.
Enter fullscreen mode Exit fullscreen mode
  1. Combining with OPTOFC OPTOFC (Open‑Fetch‑Close) is another optimization parameter. When used together with IFX_AUTOFREE, it can further reduce network round trips: 
java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;OPTOFC=1;DB_LOCALE=en_US.819";
// Loop as in example 2
// Output: time:1228 ms
Enter fullscreen mode Exit fullscreen mode

The combination yields a dramatic improvement (1228 ms, about 58% faster than baseline).

Summary
IFX_AUTOFREE is a simple yet powerful JDBC parameter in the gbase database ecosystem. By enabling it, you can eliminate one network round trip per statement closure, resulting in noticeable performance gains for high‑volume query workloads. For even better results, consider pairing it with OPTOFC.

If you have any questions or want to share your own experiences, feel free to leave a comment! 🚀

Top comments (0)