注册登录
提 问
English中文版
光洋山
作者光洋山·2016-12-01 14:59
数据库架构师·金融科技公司
关注81

GBase 8t 如何实现1秒insert 10万记录

字数 2373阅读 2281评论 0赞 0

GBase 8t 如何实现1秒insert 10万记录

在Java开发程序过程中，如何提供数据库操作的性能，除了数据库本身的性能优化，和Java程序本身执行SQL语句的方式有很大关系。本文针对GBase 8t/Informix的4种写法进行了性能的测试。

1.测试结果
Insert 10万记录。在数据库采用unbuffered logging日志模式下。

SQL执行方式测试用时说明
直接执行executebyrow49秒executeUpdate(sql) 每1条提交一次
直接执行directexecute21.9秒executeUpdate(sql) 每1000条提交一次
绑定变量parserexecute14秒prepareStatement(sql) executeUpdate() 每1000条提交一次
绑定变量+batch执行batchinsert1.2秒prepareStatement(sql) addBatch() executeBatch() 每1000条提交一次
绑定变量+batch执行+USEPUT特性batchinsertp0.8秒prepareStatement(sql) addBatch() executeBatch() IFX_USEPUT=1 每1000条提交一次

测试记录

time java executebyrow
connect successfully

real 0m48.972s
user 0m6.420s
sys 0m2.995s

[root@database java]# time java directexecute
connect successfully

real 0m21.953s
user 0m4.622s
sys 0m1.440s
[root@database java]# time java parserexecute

real 0m14.102s
user 0m3.410s
sys 0m0.854s
[root@database java]# time java batchinsert

real 0m1.234s
user 0m0.976s
sys 0m0.064s
[root@database java]# time java batchinsertp

real 0m0.853s
user 0m0.484s
sys 0m0.060s

2.测试准备：

create table t3(payid integer,
paytype integer,
cardid integer,
incardid integer,
amount decimal(8,2),
paydate datetime year to second) 
;
create index idx_t3 on t3 (payid)  ;

Define the env for Java

export JAVA_HOME=/usr/java/jdk1.7.0_67
export PATH=/usr/java/jdk1.7.0_67/bin:${PATH}
export CLASSPATH=.:/usr/java/jdk1.7.0_67/lib/tools.jar:/usr/java/jdk1.7.0_67/lib/dt.jar
export CLASSPATH=${CLASSPATH}:/root/16/mycat/lib/ifxjdbc.jar

3.程序代码及说明：

executebyrow.java

 
import java.util.*;
import java.sql.*;

public class executebyrow {
     private static String URL_STRING[] = { "jdbc:informix-sqli://192.168.100.15:9088/db:INFORMIXSERVER=gbase15" };
     private static String user = "gbase";
     private static String password = "gbase";


     public static void main(String[] args) throws SQLException,
               ClassNotFoundException {
          Class.forName("com.informix.jdbc.IfxDriver");
          for (int i = 0; i < 1; i++) {
               try {
                    Connection conn = DriverManager.getConnection(URL_STRING[i],user, password);
                    if (!conn.isClosed())
                         System.out.println("connect successfully");
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    for (int j = 1; j <= 100000; j++) {
                                 String sql = "insert into t3(payid,paytype,cardid,incardid,amount,paydate) values ("+j+",1,999,1000,99.99,'2016-01-01 20:20:20')";
                    int result = statement.executeUpdate(sql);
                    conn.commit();
                    }
                    conn.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
          }
          return;
     }
}

directexecute.java 

import java.util.*;
import java.sql.*;

public class directexecute {
     private static String URL_STRING[] = { "jdbc:informix-sqli://192.168.100.15:9088/db:INFORMIXSERVER=gbase15" };
     private static String user = "gbase";
     private static String password = "gbase";

     public static void main(String[] args) throws SQLException,
               ClassNotFoundException {
          Class.forName("com.informix.jdbc.IfxDriver");
          for (int i = 0; i < 1; i++) {
               try {
                    Connection conn = DriverManager.getConnection(URL_STRING[i],user, password);
                    if (!conn.isClosed())
                         System.out.println("connect successfully");
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    for (int j = 1; j <= 100000; j++) {
                                 String sql = "insert into t3(payid,paytype,cardid,incardid,amount,paydate) values ("+j+",1,999,1000,99.99,'2016-01-01 20:20:20')";
                    int result = statement.executeUpdate(sql);
                    if (j%1000==0){
                    System.out.println(j);
                    conn.commit();
                                }
                    }
                    conn.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
          }
          return;
     }
}

parserexecute.java

import java.util.*;
import java.sql.*;

public class parserexecute {
     private static String URL_STRING[] = { "jdbc:informix-sqli://192.168.100.15:9088/db:INFORMIXSERVER=gbase15;IFX_USEPUT=1" };
     private static String user = "gbase";
     private static String password = "gbase";

     public static void main(String[] args) throws SQLException,
               ClassNotFoundException {
          Class.forName("com.informix.jdbc.IfxDriver");
          for (int i = 0; i < 1; i++) {
               try {
                    Connection conn = DriverManager.getConnection(URL_STRING[i],user, password);
                    if (!conn.isClosed())
                         System.out.println("connect successfully");
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    String sql = "insert into t3(payid,paytype,cardid,incardid,amount,paydate) values (?,1,999,1000,99.99,'2016-01-01 20:20:20')";
                    PreparedStatement  ps=(PreparedStatement) conn.prepareStatement(sql);
                    for (int j = 1; j <= 100000; j++) {
                    ps.setInt(1,j);
                    int result = ps.executeUpdate();
                    if (j%1000==0){
                         System.out.println(j);
                         conn.commit();
                                }
                    }
                    conn.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
          }
          return;
     }
}

batchinsert.java

import java.util.*;
import java.sql.*;

public class batchinsert {
     private static String URL_STRING[] = { "jdbc:informix-sqli://192.168.100.15:9088/db:INFORMIXSERVER=gbase15;" };
     private static String user = "gbase";
     private static String password = "gbase";

     public static void main(String[] args) throws SQLException,
               ClassNotFoundException {
          Class.forName("com.informix.jdbc.IfxDriver");
          for (int i = 0; i < 1; i++) {
               try {
                    Connection conn = DriverManager.getConnection(URL_STRING[i],user, password);
                    if (!conn.isClosed())
                         System.out.println("connect successfully");
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    String sql = "insert into t3(payid,paytype,cardid,incardid,amount,paydate) values (?,1,999,1000,99.99,'2016-01-01 20:20:20')";
                    PreparedStatement  ps=(PreparedStatement) conn.prepareStatement(sql);
                    for (int j = 1; j <= 100000; j++) {
                    ps.setInt(1,j);
                                ps.addBatch();
                                if (j%1000==0){
                         System.out.println(j);
                                        ps.executeBatch(); 
                         conn.commit();
                                     }
                    }
                    conn.commit();
                                
                                System.out.println("connect successfully");  
                    conn.close();
               } catch (SQLException e) {
                    e.printStackTrace();
                               System.out.println("11"+ e.getMessage());
               }
          }
          return;
     }
 }

batchinsertp.java

import java.util.*;
import java.sql.*;

public class batchinsertp {
     private static String URL_STRING[] = { "jdbc:informix-sqli://192.168.100.15:9088/db:INFORMIXSERVER=gbase15;IFX_USEPUT=1" };
     private static String user = "gbase";
     private static String password = "gbase";

     public static void main(String[] args) throws SQLException,
               ClassNotFoundException {
          Class.forName("com.informix.jdbc.IfxDriver");
          for (int i = 0; i < 1; i++) {
               try {
                    Connection conn = DriverManager.getConnection(URL_STRING[i],user, password);
                    if (!conn.isClosed())
                         System.out.println("connect successfully");
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    String sql = "insert into t3(payid,paytype,cardid,incardid,amount,paydate) values (?,1,999,1000,99.99,'2016-01-01 20:20:20')";
                    PreparedStatement  ps=(PreparedStatement) conn.prepareStatement(sql);
                    for (int j = 1; j <= 100000; j++) {
                    ps.setInt(1,j);
                                ps.addBatch();
                                if (j%1000==0){
                         System.out.println(j);
                                        ps.executeBatch(); 
                         conn.commit();
                                     }
                    }
                    conn.commit();
                                
                                System.out.println("connect successfully");  
                    conn.close();
               } catch (SQLException e) {
                    e.printStackTrace();
                               System.out.println("11"+ e.getMessage());
               }
          }
          return;
     }
}

附录批量update程序
batchupdate.java 

import java.util.*;
import java.sql.*;

public class batchupdate{
     private static String URL_STRING[] = { "jdbc:informix-sqli://192.168.100.15:9088/db:INFORMIXSERVER=gbase15;" };
     private static String user = "gbase";
     private static String password = "gbase";

     public static void main(String[] args) throws SQLException,
               ClassNotFoundException {
          Class.forName("com.informix.jdbc.IfxDriver");
          for (int i = 0; i < 1; i++) {
               try {
                    Connection conn = DriverManager.getConnection(URL_STRING[i],user, password);
                    if (!conn.isClosed())
                         System.out.println("connect successfully");
                    conn.setAutoCommit(false);
                    Statement statement = conn.createStatement();
                    String sql = "update t2 set balance=balance+1 where userid=? ";
                    PreparedStatement  ps=(PreparedStatement) conn.prepareStatement(sql);
                    for (int j = 200009; j <= 300009; j++) {
                    ps.setInt(1,j);
                                ps.addBatch();
                                if (j%1000==0){
                                ps.executeBatch(); 
                                conn.commit(); 
                                }
                    }
                                //ps.executeBatch(); 
               //     conn.commit();
                                
                                System.out.println("connect successfully");  
                    conn.close();
               } catch (SQLException e) {
                    e.printStackTrace();
                               System.out.println("11"+ e.getMessage());
               }
          }
          return;
     }
}
孙国荣GBase 8t南大通用数据库java
著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

赞0

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

  • 5分钟学会Linux环境GBase 8t安装和部署
    评论 1 · 赞 1
  • IBM公布Informix11.5 停止支持服务时间点
    评论 0 · 赞 2
  • GBase 8t招聘高级数据库技术专家
    评论 6 · 赞 17
  • GBase 8t 时间序列TimeSeries 示例SQL说明
    评论 1 · 赞 0
  • GBase 8t支持JSON/BSON
    评论 1 · 赞 0

    • 相关文章

  • 南大通用对数据库领域国家标准编制与实施的贡献浅析
  • Redis 的优势是如何在保单业务中发挥出来的
  • 数据库行业解决方案都写了啥
  • 企业传统Oracle数据库迁移国产数据库核心难点总结
  • 招聘：建信金科基础技术中心数据库产品团队

    • 相关问题

  • 引入缺乏第三方服务生态的信创数据库，会不会令甲方被厂商锁定而失去议价能力？
  • 对于业务系统的历史数据存放、查询，哪种类型的数据库更为合适？
  • 超融合能否扛起全闪做支撑交易类数据库最佳平台的大旗？
  • 针对不同国产数据库数据存储介质选型，是采用本地盘、SAN存储，还是NAS存储？
  • 金融行业容器数据库对比传统数据库的主要变化在哪里？或者是架构上哪一层发生了变化？

    • 相关资料

  • 分布式数据库金融应用发展报告-2023
  • IDC+中国金融行业国产分布式数据库白皮书
  • Redhat7.6 Oracle 19C RAC部署手册
  • 《迈向YB数据时代》2023春季刊之——【趋势动态】议题1：大中型企业关键应用数据库未来会如何走向？
  • Redhat7.0 Oracle 12C RAC部署手册

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广