Hazelcast SQL interface slow performance HZ 4.2.2 vs HZ 5.0.2

Question

Situation :

• We have a product with approx 30 attributes (String, Enum, Double) values
• We have iMap with indexes for all attributes IndexType.HASH for string value and IndexType.SORTED for double values. (900MB together)
• We have 300k products in map.(aprox 500MB )
• We use local Datagrid with one member
• JVM config: -Xms6G -Xmx8G
• For HZ 5: we enabled JetConfig
  config.getJetConfig().setEnabled(true);
• Use Java AdoptOpenJDK 11.0.8

When invoking SQL query with pagination in HZ4 we got a response approx in 20-50ms, but the same query in Hazelcast 5 we got results in 2000-2500 ms

...ORDER BY param1  ASC LIMIT  20 OFFSET  0... 
SqlResult sqlRows = hazelcastInstance.getSql().execute(sqlBuilder.toString());                                                                                           

When we tried to use predicates on the same map and in HZ4 and HZ5 we got the same results about 2000-2500 ms to get predicated page

PagingPredicate<Long, Product> pagingPredicate = Predicates.pagingPredicate(predicate, ProductComparatorFactory.getEntryComparator(sortName), max);
pagingPredicate.setPage(from / max);
///get final list of products
List<Product> selectedPageA = new ArrayList<>(productMap.getAll(productMap.keySet(pagingPredicate)).values());

For HZ 5 we add Mapping

hazelcastInstance.getSql().execute("CREATE MAPPING "ProductScreenerRepositoryProductMap" EXTERNAL NAME "ProductScreenerRepositoryProductMap"
TYPE IMap
OPTIONS (
  'keyFormat' = 'java',
  'keyJavaClass' = 'java.lang.Long',
  'valueFormat' = 'java',
  'valueJavaClass' = 'com.finmason.finriver.product.Product'
)");
}

There is used SQL

 SELECT * FROM ProductScreenerRepositoryProductMap 
 WHERE doubleValue1 >= -0.9624378795139998 
 AND doubleValue1 <= 0.9727269574354098 
 AND doubleValue2 >= -0.9 
 AND doubleValue2 <= 0.9 
 ORDER BY doubleValue3  ASC LIMIT  20 OFFSET  0

And Product use simple serialization

Answer 1

Please upgrade to Hazelcast 5.1 (planned for February 23 right now). It should be fixed with https://github.com/hazelcast/hazelcast/pull/20681

Answer 2

Actually this case will speed up by 3 separate PRs from 5.1:

• https://github.com/hazelcast/hazelcast/pull/20681 - this one make your query use index
• https://github.com/hazelcast/hazelcast/pull/20402 - this one will do less deserialization on cluster side
• https://github.com/hazelcast/hazelcast/pull/20398 - this one makes deserialization on client side faster for multi-column queries

There are two cases not resolved in 5.1, they are described in https://github.com/hazelcast/hazelcast/pull/20796 - it should not be a problem in your case, but if someone else see this post, it may be his/her. I hope that fix will be delivered in 5.1.1.

If you have a possibility to upgrade to full 5.1 after the release then I strongly recommend you to do it.