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

环境：

数据量100万

Ignite2.5

Windows10 8g

jdbc方式连接

import 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

网格方式连接

import 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("ip: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> 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秒