GP两种连接方式性能测试

GP两种连接方式性能测试

1. Pivotal

import 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

import 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左右。