GP两种连接方式性能测试

1. Pivotal
2. java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class GPQueryString {

public static void main(String[] args) {
    // TODO Auto-generated method stub
    try {
        // URL
        String url = "jdbc:pivotal:greenplum://ip:5432;DatabaseName=testtpch";            
        // 数据库用户名
        String username = "xx";    
        // 数据库密码
        String password = "xx";            
        // 加载驱动
        Class.forName("com.pivotal.jdbc.GreenplumDriver");            
        // 获取连接
        Connection conn = DriverManager.getConnection(url, username, password);            

// String sql18 = "select c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity) from customer,orders,lineitem"
// +" where o_orderkey in (select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 312) and c_custkey = o_custkey and o_orderkey = l_orderkey"
// +" group by c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice"
// +" order by o_totalprice desc,o_orderdate LIMIT 100";
// String sql17="select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part,"
// +"(SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg"
// +" where p_partkey = l_partkey and agg_partkey = l_partkey and p_brand = 'Brand#11' and p_container = 'WRAP DRUM' and l_quantity < avg_quantity LIMIT 1";

        String sql21="select s_name,count(*) as numwait from supplier,lineitem l1,orders,nation"
                +" where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists (select * from lineitem l2"
                +" where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey)and not exists (select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = 'BRAZIL'"
                +" group by s_name order by numwait desc,s_name LIMIT 100";

// PreparedStatement pre = conn.prepareStatement(sql18);
// long t1 = System.currentTimeMillis();
// ResultSet rs = pre.executeQuery();
// long t2 = System.currentTimeMillis();
// long t3=t2-t1;
// float s=(float)t3/1000;
// System.out.println("sql18执行时间："+s+"秒");

// PreparedStatement pre17 = conn.prepareStatement(sql17);
// long t17 = System.currentTimeMillis();
// ResultSet rs17 = pre17.executeQuery();
// long t18 = System.currentTimeMillis();
// long t19=t18-t17;
// float s17=(float)t19/1000;
// System.out.println("sql17执行时间："+s17+"秒");

        
        PreparedStatement pre21 = conn.prepareStatement(sql21);
        long t21 = System.currentTimeMillis();
        ResultSet rs21 = pre21.executeQuery();
        long t22 = System.currentTimeMillis();
        long t23=t22-t21;
        float s21=(float)t23/1000;
        System.out.println("sql21执行时间："+s21+"秒");

// while(rs.next())
// {
// System.out.println(rs.getString(1));
// }

    } catch (Exception e) {
        e.printStackTrace();

    }

}

}

1. Postgresql
2. java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GPpostgres {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    // TODO Auto-generated method stub
    try {
        // URL
        String url = "jdbc:postgresql://ip:5432/testtpch";

// String url = "jdbc:postgresql://ip:5432/testcolumn";

        // 数据库用户名
        String username = "xx";    
        // 数据库密码
        String password = "xx";            
        // 加载驱动
        Class.forName("org.postgresql.Driver");            
        // 获取连接
        Connection conn = DriverManager.getConnection(url, username, password);            

// String sql18 = "select c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity) from customer,orders,lineitem"
// +" where o_orderkey in (select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 312) and c_custkey = o_custkey and o_orderkey = l_orderkey"
// +" group by c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice"
// +" order by o_totalprice desc,o_orderdate LIMIT 100";

        String sql17="select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part,"
                +"(SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg"
                +" where p_partkey = l_partkey and agg_partkey = l_partkey and p_brand = 'Brand#11' and p_container = 'WRAP DRUM' and l_quantity < avg_quantity LIMIT 1";

// String sql21="select s_name,count(*) as numwait from supplier,lineitem l1,orders,nation"
// +" where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists (select * from lineitem l2"
// +" where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey)and not exists (select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = 'BRAZIL'"
// +" group by s_name order by numwait desc,s_name LIMIT 100";
// PreparedStatement pre = conn.prepareStatement(sql18);
// long t1 = System.currentTimeMillis();
// ResultSet rs = pre.executeQuery();
// long t2 = System.currentTimeMillis();
// long t3=t2-t1;
// float s=(float)t3/1000;
// System.out.println("sql18执行时间："+s+"秒");

        
        PreparedStatement pre17 = conn.prepareStatement(sql17);
        long t17 = System.currentTimeMillis();
        ResultSet rs17 = pre17.executeQuery();
        long t18 = System.currentTimeMillis();
        long t19=t18-t17;
        float s17=(float)t19/1000;
        System.out.println("sql17执行时间："+s17+"秒");
        

// PreparedStatement pre21 = conn.prepareStatement(sql21);
// long t21 = System.currentTimeMillis();
// ResultSet rs21 = pre21.executeQuery();
// long t22 = System.currentTimeMillis();
// long t23=t22-t21;
// float s21=(float)t23/1000;
// System.out.println("sql21执行时间："+s21+"秒");
// while(rs.next())
// {
// System.out.println(rs.getString(1));
// }

    } catch (Exception e) {
        e.printStackTrace();

    }

}

}

同样的数据，postgresql方式的查询速度是pivotal方式连接的查询速度的0.61左右。