Re: Affinity key in SQL execution

Prasad, To achieve the best performance, join criteria should be the same as collocation criteria. E.g., if you join by userId, then userId should be used as affinity key. Please learn more about distributed joins here: https://apacheignite-sql.readme.io/docs/distributed-joins -Val -- Sent

Re: Affinity key in SQL execution

3. I did not understand your answer in point 3. I must join by userId to get the correct data. Are you saying that (c1.affinityId = c2.affinityId) condition should be included in on clause? If I rewrite SQL as follows, will it work? Select * from cache1 c1 join cache2 c2 on ( c1.userId =

Re: Affinity key in SQL execution

3. I did not understand your answer in point 3. My colocation strategy as follows: I have around 20 caches. These caches contains subscription's data. I have around 5000 subscriptions. Affinity key for all 20 caches in "subscriptionId". This is done to make sure that data in all 20 caches

Re: Affinity key in SQL execution

Prasad, 1. Yes, you will always see it in the execution plan because there are always two stages. Of course, if only one server node participates in the execution, the reduce stage is effectively no-op. 2. Yes, you need to put the annotation to make sure you can access it from queries. I would

Re: Affinity key in SQL execution

Hi val 1. If the second execution plan is for reduce phase, should it come in queries which includes affinity key in their where clause? Because there is going to be no reduce phase in this case. 2. Is it mandatory to anotate affinity id field in my key class with @QueryField? Without this

Re: Affinity key in SQL execution

Hi Prasad, 1. First plan is for map phase (executed on server side) and the second one is for reduce phase (executed on client side). Merge scan means that it just merges result sets from all participating server nodes. Sometime it can contain additional reduce steps like final groupings,

Re: Affinity key in SQL execution

Hi Val, Can you please help me out with these questions? On Thu, Aug 23, 2018 at 8:38 PM Prasad Bhalerao < prasadbhalerao1...@gmail.com> wrote: > Problem is resolved now. The index type setting in cache configuration was > incorrect. After setting DefaultDataAffinityKey.class in cache config

Re: Affinity key in SQL execution

Can someone please reply to this? On Thu, Aug 23, 2018, 8:38 PM Prasad Bhalerao wrote: > Problem is resolved now. The index type setting in cache configuration was > incorrect. After setting DefaultDataAffinityKey.class in cache config index > type query executed successfully. > > New sql is as

Re: Affinity key in SQL execution

Problem is resolved now. The index type setting in cache configuration was incorrect. After setting DefaultDataAffinityKey.class in cache config index type query executed successfully. New sql is as follows. I have few questions on it. 1) Execution plan is showing 2 sqls. What does second sql

Re: Affinity key in SQL execution

Hi, Problem is resolved now. The index type setting in cache configuration was incorrect. After setting DefaultDataAffinityKey.class in cache config index type query executed successfully. New sql is as follows. I have couple of questions on it. 1) Execution plan is showing 2 sqls. What does

Re: Affinity key in SQL execution

Hi, I tried your suggestion but I am getting query parsing error now. I am attaching my data and data key class in this mail. Could you please help me out? SQL : select assetGroupId, name from AssetGroupData where affinityId = ? *Exception:* javax.cache.CacheException: Failed to parse query.

Re: Affinity key in SQL execution

Ok, I tried to write generic impl to use the same key class with different caches . That's why kept the name affinityId. The reason I am not getting error is I have the subscriptionId in Data(value) class as well. So it means the affinity key field name matters. I was thinking/trying to map the

Re: Affinity key in SQL execution

Prasad, In this case using subscriptionId in query would be a syntax error, because the name of the field is affinityId. If you use affinityId, however, Ignite will route the query to a single node. It knows that it's affinity key based on @AffinityKeyMapped annotation. -Val -- Sent from:

Re: Affinity key in SQL execution

I am still not getting it. The field affinityId in my data key is just like any other field which hold the value of affinity key column. For cache1 it will hold subscriptionId and for other cache it might hold value of some other affinity key. But when I write SQL like 'select * from Cahce_1

Re: Affinity key in SQL execution

There are several ways to do this. First one is shown in your first message - DefaultDataAffinityKey instance plays the role of primary key, while enclosed affinityId field plays the of affinity key, because it's annotated with @AffinityKeyMapped. In case you don't have classes at all and

Re: Affinity key in SQL execution

I could not find any cache config setting where one can provide the primary key column name or affinity column name of a given cache. How does ignite identify that column provided in select query's where clause is a primary key or affinity key? Can you give an example? Thanks, Prasad On Wed,

Re: Affinity key in SQL execution

Prasad, Affinity key is always a part of primary key. For example, in your case primary key consists of two fields - id and subscriptionId. Therefore you can query by primary key without providing the affinity key. On the other hand, however, query can be routed to a single node even if only

Affinity key in SQL execution

Hi, I am using key value store and I have configured multiple caches. These caches have different affinity key. To store the values in cache I am using following key. Id and affinityId column can be different for different caches. public class DefaultDataAffinityKey implements DataKey {