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

Can you please attach full sql query with selected columns? What kind of serialization is used in com.finmason.finriver.product.Product class? Is it simple Java serialization? — pveentjer, Feb 04 '22 at 07:42
And maybe one more question about counts . We tried to switch to HZ 5 because we need Count functionality which is implemented in HZ 5. Is there any workearound for HZ5 to get counts in SQL intrerface ? Thanks — Lukas Karasek, Feb 04 '22 at 10:30
Only one of your indexes can be used. We have some cost calculations (e.g. `=` is better than `>`), but your filters seem equal. In that case the older index is used. Perhaps a different order was used in your 5.0 test? — Oliv, Feb 04 '22 at 22:38
I'm little a bit confused by your answer. - Only one index -? - You mean we can have only Sorted or Hash index - Or you mean that it when is search then you only one index and which one ? for Order by or First in Where clausule ? - What do you mean by this : In that case the older index is used. what does ment the Older index ? And as a note, The query is in all cases exact same — Lukas Karasek, Feb 07 '22 at 10:32
You can have multiple indexes, but one query can use only one index. It is a common misconception that if I use fields `a` and `b` in the WHERE clause, then I create 2 indexes, one for each. That's not correct. Hazelcast doesn't use statistics, so we don't know which index would be better to use. Both indexes are equivalent, therefore we pick the one that was created first. — Oliv, Feb 07 '22 at 15:24
Actually, an issue was filled about this. In 5.0, index resolution was broken for queries of type `a>? && a`, see https://github.com/hazelcast/hazelcast/pull/20681 — Oliv, Feb 11 '22 at 09:56

Krzysztof · Answer 1 · 2022-02-18T12:28:34.340

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

edited Feb 18 '22 at 12:28

answered Feb 17 '22 at 18:58

Krzysztof

709
6
12

I tried beta version 5.1-BETA-1 and it was slow. I'll wait for full 5.1 version. thx – Lukas Karasek Feb 22 '22 at 12:39
The fix was added after 5.1-BETA-1, so you must wait for 5.1 final release. They moved 5.1 release date to March 1 - https://github.com/hazelcast/hazelcast/milestone/177 – Krzysztof Feb 24 '22 at 08:42
Ok we will wait for 5.1 version tomorrow and will see if it will be better . thanks – Lukas Karasek Feb 28 '22 at 17:13
Hazelcast 5.1 was released: https://docs.hazelcast.com/hazelcast/5.1/release-notes – Krzysztof Mar 01 '22 at 16:42

score 1 · Answer 2 · answered Feb 25 '22 at 07:47

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.

Hazelcast SQL interface slow performance HZ 4.2.2 vs HZ 5.0.2

2 Answers2