Ignite的jdbc与网格的连接方式的查询性能对比

Ignite的jdbc与网格的连接方式的查询性能对比
环境:
数据量100万
Ignite2.5
Windows10 8g

  1. jdbc方式连接
  2. java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class IgniteQury {

public static void main(String[] args) throws SQLException, ClassNotFoundException {
    // TODO Auto-generated method stub
    //连接远程单节点ignite
    Class.forName("org.apache.ignite.IgniteJdbcDriver");
    Connection conn = DriverManager.getConnection("jdbc:ignite:thin://ip:10800");        
    PreparedStatement ps = conn.prepareStatement("select s.*,i.* from staff s join internetcafe i on s.ID=i.STAFFID"
            +" union all "
            +"select s.*,p.TrackID,p.STAFFID,concat(concat(p.TAKEOFFNAME,','),p.FALLDOWNNAME) FLIGHTADD,p.FLIGHTDATE from staff s left join plane p on s.ID=p.STAFFID");

// +" union all "
// +"select s.*,t.TrackID,t.STAFFID,concat(concat(t.OUTSETNAME,','),t.TERMINAL) TRIVALADD,t.travelDate from staff s join train t on s.ID=t.STAFFID");

    long t1 = System.currentTimeMillis();
    ResultSet res=ps.executeQuery();
    long t2 = System.currentTimeMillis();
    long t3=t2-t1;
    float s=(float)t3/1000;
    System.out.println("执行时间:"+s+"秒");
}

}
使用jdbc连接,用java程序取数据,合并两个查询子集,查询时间50几秒,合并三个查询子集,拉跨ignite

  1. 网格方式连接
  2. java.util.Arrays;

import java.util.List;

import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.configuration.IgniteConfiguration;
import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi;
import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder;

public class IgniteCollocationQuery {

public static void main(String[] args) {
    // TODO Auto-generated method stub
    TcpDiscoverySpi spi = new TcpDiscoverySpi();
    TcpDiscoveryVmIpFinder ipFinder = new TcpDiscoveryVmIpFinder();
    ipFinder.setAddresses(Arrays.asList("192.168.76.95:47500..47509"));

// spi.setJoinTimeout(4000);

    spi.setIpFinder(ipFinder);
    IgniteConfiguration cfg = new IgniteConfiguration();
    cfg.setDiscoverySpi(spi);
    cfg.setClientMode(true);
    Ignition.setClientMode(true);
    Ignite ignite = Ignition.start(cfg);   

// IgniteCache() cache = ignite.cache("SQL_PUBLIC_STAFF");

    CacheConfiguration ccfg = new CacheConfiguration();
    ccfg.setName("SQL_PUBLIC_STAFF");
    IgniteCache cache = ignite.getOrCreateCache(ccfg);

// SqlFieldsQuery query = new SqlFieldsQuery("select * from staff");

    SqlFieldsQuery query = new SqlFieldsQuery("select s.*,i.* from staff s join internetcafe i on s.ID=i.STAFFID"
            +" union all "
            +"select s.*,p.TrackID,p.STAFFID,concat(concat(p.TAKEOFFNAME,','),p.FALLDOWNNAME) FLIGHTADD,p.FLIGHTDATE from staff s left join plane p on s.ID=p.STAFFID"
            +" union all "
            +"select s.*,t.TrackID,t.STAFFID,concat(concat(t.OUTSETNAME,','),t.TERMINAL) TRIVALADD,t.travelDate from staff s join train t on s.ID=t.STAFFID");
    //设置collocation可用
   query.setDistributedJoins(true);
    
    long t1 = System.currentTimeMillis();
    QueryCursor<List<?>> cursor=cache.query(query);
    long t2 = System.currentTimeMillis();
    long t3=t2-t1;
    float s=(float)t3/1000;
    System.out.println("执行时间:"+s+"秒");
}

}
使用网格连接,合并三个查询子集,1.9秒,设置query.setDistributedJoins(true)性能提升10倍,0.19秒

上一篇:五节点HadoopHA安装教程


下一篇:Greenplum 三节点安装教程(非root用户)