Distinct Query too slow

[Prasad Bhalerao]

Hi,

I am using Ignite 2.6 version. I have around total 6 million entries in my
cache.

Following sql is taking too much time to execute. Some times it takes more
than 180 seconds.

This SQL returns 4.5 million entries for given subscriptionId. I tried to
add query parallelism (4-16 threads) on cache configuration. But it did not
help.

If I remove DISTINCT keyword from sql then it executes quickly. But I need
distinct in this particular case.

Can some please advise?

*SQL:*
select distinct ipStart,ipEnd from IpContainerIpV4Data where subscriptionId
= ?

*2020-01-23 06:49:38,249 264738612 [query-#30600%springDataNode%] WARN
o.a.i.i.p.query.h2.IgniteH2Indexing - Query execution is too long
[time=83159 ms, sql='SELECT DISTINCT __Z0.IPSTART __C0_0, __Z0.IPEND __C0_1
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 WHERE
__Z0.SUBSCRIPTIONID = ?1', plan= SELECT DISTINCT __Z0.IPSTART AS __C0_0,
__Z0.IPEND AS __C0_1 FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0
/* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX2: SUBSCRIPTIONID = ?1 */
WHERE __Z0.SUBSCRIPTIONID = ?1 , parameters=[1234]]*


*Index is as follows:*

public class IpContainerIpV4Data implements Data<DefaultDataAffinityKey>,
UpdatableData<DefaultDataAffinityKey> {

@QuerySqlField
private long id;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 2)})
private int moduleId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 1),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
private long subscriptionId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 4, descending = true),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2, descending
= true)})
private int ipEnd;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 3),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
private int ipStart;
@QuerySqlField
private int partitionId;
@QuerySqlField
private long updatedDate;

}


*Cache Configuration:*

private CacheConfiguration ipContainerIPV4CacheCfg() {

  CacheConfiguration ipContainerIpV4CacheCfg = new
CacheConfiguration<>(CacheName.IP_CONTAINER_IPV4_CACHE.name());
  ipContainerIpV4CacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
  ipContainerIpV4CacheCfg.setWriteThrough(ENABLE_WRITE_THROUGH);
  ipContainerIpV4CacheCfg.setReadThrough(false);
  ipContainerIpV4CacheCfg.setRebalanceMode(CacheRebalanceMode.ASYNC);
  
ipContainerIpV4CacheCfg.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC);
  ipContainerIpV4CacheCfg.setBackups(this.backupCount);
  Factory<IpContainerIpV4CacheStore> storeFactory =
FactoryBuilder.factoryOf(IpContainerIpV4CacheStore.class);
  ipContainerIpV4CacheCfg.setCacheStoreFactory(storeFactory);
  ipContainerIpV4CacheCfg.setIndexedTypes(DefaultDataAffinityKey.class,
IpContainerIpV4Data.class);
  
ipContainerIpV4CacheCfg.setCacheStoreSessionListenerFactories(cacheStoreSessionListenerFactory());
  ipContainerIpV4CacheCfg.*setSqlIndexMaxInlineSize(84);*
  RendezvousAffinityFunction affinityFunction = new
RendezvousAffinityFunction();
  affinityFunction.setExcludeNeighbors(true);
  ipContainerIpV4CacheCfg.setAffinity(affinityFunction);
  ipContainerIpV4CacheCfg.setStatisticsEnabled(true);
  
ipContainerIpV4CacheCfg.setPartitionLossPolicy(PartitionLossPolicy.READ_WRITE_SAFE);

   ipContainerIpV4CacheCfg.setQueryParallelism(4);

  return ipContainerIpV4CacheCfg;
}


Thanks,
Prasad