DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Optimizing GBase 8s JDBC with IFX_AUTOFREE

#gbase #database #数据库

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
Enter fullscreen mode Exit fullscreen mode
  • Properties object: 
  Properties pr = new Properties();
  pr.put("IFX_AUTOFREE", "1");
  conn = DriverManager.getConnection(url, pr);
Enter fullscreen mode Exit fullscreen mode

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));
   }
}
Enter fullscreen mode Exit fullscreen mode

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));
   }
}
Enter fullscreen mode Exit fullscreen mode

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));
   }
}
Enter fullscreen mode Exit fullscreen mode

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));
   }
}
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)