关于libpq的使用，内附测试代码及测试结果

最新推荐文章于 2024-07-31 17:58:34 发布

joliny

最新推荐文章于 2024-07-31 17:58:34 发布

阅读量1.8k

点赞数

分类专栏：技术空间文章标签：测试 insert sql java string database

技术空间专栏收录该内容

717 篇文章

订阅专栏

本文探讨了使用libpq和Java在PostgreSQL中批量插入数据的方法，对比不同策略下的性能表现，并提出通过减少提交次数来提高效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

libpq 代码：

/* insert.c */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include "libpq-fe.h"

int main(int argc, char *argv[])
{
    char         sql[] = "INSERT INTO tb_test (id, name) VALUES (1, 'test')";
    PGconn      *conn;
    int          i;
    PGresult    *ret;
    time_t       tp1, tp2;
    char        *msg;

    conn = PQconnectdb ("hostaddr=ip  port=5432 dbname=db_name user=db_user password=******");

    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s/n", PQerrorMessage(conn));
        PQfinish (conn);
        return -1;
    }

    time (&tp1);
    for (i = 0; i < 20000; i ++)
    {
        ret = PQexec (conn, sql);
        msg = PQresultErrorMessage (ret);
        PQclear (ret);
        if (strlen (msg) > 0)
        {
            printf ("execute prepared statemnt faild at line %d caused by :O %s/n", i, msg);
        }
    }
    time (&tp2);
    printf ("execute time: %d second(s)/n", tp2 - tp1);
    PQfinish (conn);
    return 0;
}

$ gcc -I/usr/include/ -I/usr/local/postgres/include/ -L/usr/local/postgres/lib/ -llibpg -o insert insert.c
$ export LD_LIBRARY_PATH=/usr/local/postgres/lib
$ ./insert
execute time: 84 second(s)

想到是否因为没有用预编译语句导致了插入如此缓慢，遂改进了一下代码：

/* insert1.c */
#include <stdio.h>
#include <time.h>
#include <string.h>
#include "libpq-fe.h"

int main(int argc, char *argv[])
{
    char         sql[] = "INSERT INTO tb_test (id, name) VALUES ($1, $2)";
    PGconn      *conn;
    const char  *stmtName = "stmt";
    const char  *values[] = {"1", "test"};
    int          i;
    PGresult    *ret;
    time_t       tp1, tp2;
    char        *msg;

    conn = PQconnectdb ("hostaddr=ip  port=5432 dbname=db_name user=db_user password=******");
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s/n", PQerrorMessage(conn));
        PQfinish (conn);
        return -1;
    }

    ret = PQprepare (conn, stmtName, sql, 2, NULL);
	msg = PQresultErrorMessage (ret);
	PQclear (ret);
	if (strlen (msg) > 0)
	{
		printf ("prepared faild/n");
		PQfinish (conn);
		return -1;
	}
    for (i = 0; i < 20000; i ++)
    {
        ret = PQexecPrepared (conn, stmtName, 2, values, NULL, NULL, 0);
	msg = PQresultErrorMessage (ret);
	PQclear (ret);
	if (strlen (msg) > 0)
	{
		printf ("execute prepared statemnt faild at line %d caused by :O %s/n", i, msg);
	}
    }
    PQfinish (conn);
    return 0;
}

$ gcc -I/usr/include/ -I/usr/local/postgres/include/ -L/usr/local/postgres/lib/ -llibpg -o insert1 insert1.c
$ export LD_LIBRARY_PATH=/usr/local/postgres/lib
$ ./insert1
execute time: 83 second(s)

只有1秒的提高。接着使用java来做测试。首先模拟PQexec () 函数

以下是java的测试代码

/* Insert.java */
import org.postgresql.jdbc2.optional.PoolingDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class Insert {
    private static DataSource ds;

    static {
        PoolingDataSource pds = new PoolingDataSource ();
        pds.setDatabaseName ("db_name");
        pds.setUser ("db_user");
        pds.setPassword ("******");
        pds.setServerName ("ip");
        ds = pds;
    }

    public static void main (String[] args) throws Exception {
        int method = 0;
        if (args.length != 0) method = Integer.parseInt (args [0]);
        switch (method) {
            case 1 :O insert1 (); break;
            case 2 :O insert2 (); break;
            case 3 :O insert3 (); break;
            default: insert (); break;
        }
    }
    private static void insert () throws SQLException {
        Connection conn = ds.getConnection ();
        String sql = "INSERT INTO tb_test (id, name) VALUES (1, 'test')";
        try {
            long current = System.currentTimeMillis ();
			Statement stmt = conn.createStatement ();
            for (int i = 0; i < 20000; i ++) {
				stmt.execute (sql);
			}
            System.out.println ("execute time " + (System.currentTimeMillis () - current) / 1000 + " second(s)");
        } finally {
            conn.close ();
        }
    }

    private static void insert1 () throws SQLException {
        Connection conn = ds.getConnection ();
        conn.setAutoCommit (false);
        String sql = "INSERT INTO tb_test (id, name) VALUES (1, 'test')";
        try {
            long current = System.currentTimeMillis ();
			Statement stmt = conn.createStatement ();
            for (int i = 0; i < 20000; i ++) {
				stmt.execute (sql);
			}
            conn.commit ();
            System.out.println ("execute time " + (System.currentTimeMillis () - current) / 1000 + " second(s)");
        } catch (SQLException ex) {
            conn.rollback ();
            throw ex;
        } finally {
            conn.close ();
        }
    }

    private static void insert2 () throws SQLException {
        Connection conn = ds.getConnection ();
        String sql = "INSERT INTO tb_test (id, name) VALUES (?, ?)";
        try {
            long current = System.currentTimeMillis ();
            PreparedStatement pstmt = conn.prepareStatement (sql);
            for (int i = 0; i < 20000; i ++) {
                pstmt.setInt (1, 1);
                pstmt.setString (1, "test");
                pstmt.executeUpdate ();
            }
            System.out.println ("execute time " + (System.currentTimeMillis () - current) / 1000 + " second(s)");
        } finally {
            conn.close ();
        }
    }

    private static void insert3 () throws SQLException {
        Connection conn = ds.getConnection ();
        String sql = "INSERT INTO tb_test (id, name) VALUES (?, ?)";
        try {
            long current = System.currentTimeMillis ();
            PreparedStatement pstmt = conn.prepareStatement (sql);
            for (int i = 0; i < 20000; i ++) {
                pstmt.setInt (1, 1);
                pstmt.setString (1, "test");
                pstmt.addBatch ();
            }
            pstmt.executeBatch ();
            System.out.println ("execute time " + (System.currentTimeMillis () - current) / 1000 + " second(s)");
        } finally {
            conn.close ();
        }
    }
}

$ export PATH=/usr/local/jdk1.5/bin:$PATH
$ export CLASSPATH=.:/usr/local/postgres/jdbc/postgresql-jdbc-8.2.5.jar:$CLASSPATH
编译：
$ javac Insert.java
首先测试第一种情况 (直接运行，autocommit = true)
$ java Insert
execute time 83 second(s)
竟然也只要83秒

第二种情况 (直接运行，autocommit = false)
$ java Insert 1
execute time 3 second(s)
!!! 看来问题出在commit的次数上

第三种情况 (prepared statement)
$ java Insert 2
execute time 84 second(s)
用了prepared statement 反而要这么长时间，开始动摇信仰了；上面的C代码也许不是因为是否使用 prepared statement 的缘故。

第四种情况 (prepared statement betch execute)
$ java Insert 3
execute time 2 second(s)
情理之中！！！

以上所有测试均在 DELL PowerEdge 2950 上进行的
CPU: 2 * 3.0GHz的英特尔至强5100
RAM: 4G
SYS: rhel5_x64
GCC: x86_64-redhat-linux 4.1.1 20070105 (Red Hat 4.1.1-52)
JVM: Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_14-b03, mixed mode)

根据java的测试情况来看，是否使用 prepared statement 没太大关系，瓶颈应该在 commit 的次数上。
现在请问各位牛人，在 libpq 中是否有类似 java 的 autocommit 的接口啊？或者有其他方式把效率提高上去呢，至少不能比 java 慢吧。
先行谢过了!

1、把sql语句连接成一个字符串后一起执行，速度应该可以提高
2、加begin,end实现一个事务块