GP兩種連接方式性能測試

Pivotal

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 stubtry {// URLString 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(); }}

}

Postgresql

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 stubtry {// URLString 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左右。

總結

以上是生活随笔為你收集整理的GP两种连接方式性能测试的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。