Optimizing GBase 8s JDBC with IFX_AUTOFREE

The JDBC driver for GBase 8s provides IFX_AUTOFREE, a parameter that cuts an extra network round trip when closing statements. If your application explicitly calls ResultSet.close(), the cursor is already freed on the server. With IFX_AUTOFREE enabled, the subsequent Statement.close() skips sending a redundant cursor-release message — saving one round trip per execution.

How to Enable It

URL parameter:

  jdbc:gbasedbt-sqli://<host>:<port>/<db>:IFX_AUTOFREE=1

Properties object:

  Properties pr = new Properties();
  pr.put("IFX_AUTOFREE", "1");
  conn = DriverManager.getConnection(url, pr);

Benchmarks

The tests ran 1,000 iterations of SELECT * FROM t1 (a single‑row table) and measured total wall time in a gbase database.

Example 1: IFX_AUTOFREE Disabled

@Test
public void test1() throws SQLException {
   String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=0;DB_LOCALE=en_US.819";
   try (Connection conn = DriverManager.getConnection(url, "gbasedbt", "111111")) {
       long startTime = System.currentTimeMillis();
       for (int i = 0; i < 1000; i++) {
           try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM t1");
                ResultSet rs = ps.executeQuery()) {
               rs.next();
           }
       }
       System.out.println("time:" + (System.currentTimeMillis() - startTime));
   }
}

Output: time:2898

Example 2: IFX_AUTOFREE Enabled

@Test
public void test1() throws SQLException {
   String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;DB_LOCALE=en_US.819";
   try (Connection conn = DriverManager.getConnection(url, "gbasedbt", "111111")) {
       long startTime = System.currentTimeMillis();
       for (int i = 0; i < 1000; i++) {
           try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM t1");
                ResultSet rs = ps.executeQuery()) {
               rs.next();
           }
       }
       System.out.println("time:" + (System.currentTimeMillis() - startTime));
   }
}

Output: time:2343

Example 3: Using Statement Instead of PreparedStatement

@Test
public void test1() throws SQLException {
   String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;DB_LOCALE=en_US.819";
   try (Connection conn = DriverManager.getConnection(url, "gbasedbt", "111111")) {
       long startTime = System.currentTimeMillis();
       for (int i = 0; i < 1000; i++) {
           try (Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT * FROM t1")) {
               rs.next();
           }
       }
       System.out.println("time:" + (System.currentTimeMillis() - startTime));
   }
}

Output: time:2376

Example 4: Combining IFX_AUTOFREE with OPTOFC

@Test
public void test1() throws SQLException {
   String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;OPTOFC=1;DB_LOCALE=en_US.819";
   try (Connection conn = DriverManager.getConnection(url, "gbasedbt", "111111")) {
       long startTime = System.currentTimeMillis();
       for (int i = 0; i < 1000; i++) {
           try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM t1");
                ResultSet rs = ps.executeQuery()) {
               rs.next();
           }
       }
       System.out.println("time:" + (System.currentTimeMillis() - startTime));
   }
}

Output: time:1228

IFX_AUTOFREE is a low‑effort switch that can deliver a noticeable speed‑up in query‑heavy workloads. Pair it with OPTOFC for maximum effect in your GBASE environment.

推荐订阅源

DEV Community