ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "alex.glau" <alex.g...@gmail.com>
Subject Re: Performance of join SQL queries
Date Sun, 09 Aug 2015 11:13:29 GMT
I prepared test with smaller number of data:
I have two Ignite caches: "ShipmentCache" including 200000 records and
"ShipmentSegmentCache" including 600500 records. These two caches (tables)
are related by "shipmentId" field. 
We run this test on single Ignite node. The first query (see
countJoinQuery() method) that has to criteria takes ~900msec but second one
(see countJoinQuery_2()) that has one additional criteria took ~2900000msec
!!!. So we want to understand execution plan. Is it possible to request
execution plan as it supported by H2 database. Please see relevant code
below.

//  Set Cache for shipments
CacheConfiguration<?, ?> shipmentCacheConfig = new CacheConfiguration<>();
shipmentCacheConfig.setName(SHIPMENT_CACHE);
shipmentCacheConfig.setCacheMode(CacheMode.PARTITIONED);
shipmentCacheConfig.setAtomicityMode(CacheAtomicityMode.ATOMIC);
shipmentCacheConfig.setIndexedTypes(UUID.class, CachedShipment.class);
//  Set Cache for shipment segments
CacheConfiguration<?, ?> shipmentSegmentCacheConfig = new
CacheConfiguration<>();
shipmentSegmentCacheConfig.setName(SHIPMENT_SEGMENT_CACHE);
shipmentSegmentCacheConfig.setCacheMode(CacheMode.PARTITIONED);
shipmentSegmentCacheConfig.setAtomicityMode(CacheAtomicityMode.ATOMIC);
shipmentSegmentCacheConfig.setIndexedTypes(AffinityKey.class,
CachedShipmentSegment.class);

// Ignite configuration		
IgniteConfiguration igniteConfig = new IgniteConfiguration();
igniteConfig.setCacheConfiguration(shipmentCacheConfig,
shipmentSegmentCacheConfig);

Ignite ignite = Ignition.start(igniteConfig);

private void countJoinQuery() throws Exception {
	IgniteCache<AffinityKey&lt;UUID>, CachedShipment> shipmentSegmentCache =  
ignite.cache(SHIPMENT_SEGMENT_CACHE);
	String joinSql =
		"select count(*) " +
		"from CachedShipmentSegment as segment, \"" + SHIPMENT_CACHE +
"\".CachedShipment as shipment " +
		"where segment.shipmentId = shipment.id and " +
		"segment.startSiteName = ? and shipment.status = ?";
	QueryCursor<List&lt;?>> results = shipmentSegmentCache.query(new
SqlFieldsQuery(joinSql).setArgs("Beta", ShipmentStatus.CLOSED));
	System.out.println("\nSuccessful get count(*) : " +
results.getAll().get(0).get(0));
}

private void countJoinQuery_2() throws Exception {
	IgniteCache<AffinityKey&lt;UUID>, CachedShipment> shipmentSegmentCache =
ignite.cache(SHIPMENT_SEGMENT_CACHE);
	String joinSql =
		"select count(*) " +
		"from CachedShipmentSegment as segment, \"" + SHIPMENT_CACHE +
"\".CachedShipment as shipment " +
		"where segment.shipmentId = shipment.id and " +
		"shipment.status = ? and segment.startSiteName = ? and
shipment.carrierType = ?";

	// Execute queries for find employees for different organizations.
	QueryCursor<List&lt;?>> results = shipmentSegmentCache.query(new
SqlFieldsQuery(joinSql).setArgs(ShipmentStatus.CLOSED, "Beta",
CarrierType.AVIA));
	System.out.println("\nSuccessful get count(*): " +
results.getAll().get(0).get(0));
}

public class CachedShipment implements Externalizable {
	@QuerySqlField(index = true)
	private UUID id;
	@QuerySqlField(index = true)
	private String name;
	@QuerySqlField
	private String description;
	@QuerySqlField(index=true)
	private String startSiteName;
	@QuerySqlField(index=true)
	private Date startDate;
	@QuerySqlField(index = true)
	private ShipmentStatus status;
	@QuerySqlField(index = true)
	private String destOrgName;
	@QuerySqlField(index = true)
	private CarrierType carrierType;
	
//	constructor & getter/setter...
}

public class CachedShipmentSegment implements Externalizable {

	private UUID id;
	@QuerySqlField(index = true)
	private UUID shipmentId;
	@QuerySqlField(index = true)
	private String startSiteName;
	@QuerySqlField(index = true)
	private String endSiteName;

    public AffinityKey<UUID> key() {
        if (key == null)
            key = new AffinityKey<>(id, shipmentId);
        return key;
    }

//	constructor & getter/setter...
}







--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Performance-of-join-SQL-queries-tp845p870.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Mime
View raw message