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

2019独角兽企业重金招聘Python工程师标准>>>

环境：

数据量100万

Ignite2.5

Windows10 8g

 

1. 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

 

1. 网格方式连接

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<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秒

转载于:https://my.oschina.net/zhouwang93/blog/1833043