Yes, that’s right. On Thursday, June 18, 2020, narges saleh <snarges...@gmail.com> wrote:
> Hi Denis > But if all the involved tables/caches have the same affinity key, > shouldn't they be collocated on the same node, if the data fits on that > node? > thanks. > > On Wed, Jun 17, 2020 at 10:53 PM Denis Magda <dma...@apache.org> wrote: > >> You can always send a compute task via one of IgniteCompute.affinityX() >> methods and run a *local* SQL query on that node: >> >> - affinity calls: https://apacheignite.readme.io/docs/collocate- >> compute-and-data#affinity-call-and-run-methods >> >> <https://apacheignite.readme.io/docs/collocate-compute-and-data#affinity-call-and-run-methods> >> - local SQL queries: https://apacheignite-sql.readme.io/docs/local- >> queries >> >> My selects are complex but they do use the primary keys for the >>> partitioned tables/caches participating in the query. But they perform >>> poorly if the data is distributed across multiple nodes. >> >> >> If the data is co-located and scattered across cluster nodes then it's >> expected that Ignite will execute a query across several nodes. It's a >> standard approach of distributed databases that support SQL engines. My >> suggestion would be to investigate the root cause of low performance. You >> just might need to configure some indexes: https://apacheignite-sql. >> readme.io/docs/performance-and-debugging >> >> - >> Denis >> >> >> On Wed, Jun 17, 2020 at 7:33 PM narges saleh <snarges...@gmail.com> >> wrote: >> >>> My selects are complex but they do use the primary keys for the >>> partitioned tables/caches participating in the query. But they perform >>> poorly if the data is distributed across multiple nodes. >>> >>> On Wed, Jun 17, 2020 at 8:04 PM narges saleh <snarges...@gmail.com> >>> wrote: >>> >>>> Thanks Denis. In case of selects, can I push ignite to broadcast the >>>> sql to where the data is, say by using employing services deployed on the >>>> targeted data nodes, or utilizing affinity calls (with the sql as part of >>>> the logic)? >>>> >>>> On Wed, Jun 17, 2020 at 7:02 PM Denis Magda <dma...@apache.org> wrote: >>>> >>>>> Narges, >>>>> >>>>> INSERTS/UPDATES/DELETES will be forwarded to primary nodes based on >>>>> the value of primary keys you pass into queries. Ignite maps a primary key >>>>> to a partition and, finally, to a primary node that holds that partition. >>>>> All this happens transparently in Ignite. You don't need to do anything >>>>> except for sending those SQL commands. >>>>> >>>>> As for SELECTS, most of them will be broadcasted to your server nodes >>>>> unless Ignite can pre-select a sub-set of target nodes a query needs to be >>>>> executed on. This optimization always takes place for simple queries when >>>>> you try to get records based on the value of a primary key, such as >>>>> "SELECT >>>>> * FROM Table WHERE primaryKeyColumn = 2". >>>>> >>>>> - >>>>> Denis >>>>> >>>>> >>>>> On Wed, Jun 17, 2020 at 12:43 PM narges saleh <snarges...@gmail.com> >>>>> wrote: >>>>> >>>>>> Thanks Denis. Will watch the video. >>>>>> One more question, related to SQL execution. If I issue a SQL from a >>>>>> client, how would ignite know which node to run the SQL query on? I >>>>>> assume >>>>>> the SQL is parsed on the server nodes, and I will still need to direct >>>>>> the >>>>>> SQL to be executed on the right server node (assuming that all the data >>>>>> is >>>>>> collocated on a single node). Am I right? >>>>>> >>>>>> On Wed, Jun 17, 2020 at 2:26 PM Denis Magda <dma...@apache.org> >>>>>> wrote: >>>>>> >>>>>>> The collocated flag is an extra optimization hint for SQL queries >>>>>>> with GROUP BYs. The parameter requests to group records locally on the >>>>>>> nodes that participated in the query execution. For instance, if your >>>>>>> application sends a query with GROUP BY to a couple of server nodes, >>>>>>> then >>>>>>> the nodes will group their portions of the result sets locally not >>>>>>> making >>>>>>> that the burden of the application that merges/reduces the result sets >>>>>>> into >>>>>>> one. The description of this parameter here should be helpful as well: >>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters >>>>>>> >>>>>>> <https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters> >>>>>>>> >>>>>>>> Should I assume that if the table is defined with affinity set, >>>>>>>> then compute collocation, including sql execution will happen >>>>>>>> automatically? >>>>>>> >>>>>>> >>>>>>> That's right. You just need to set up the affinity keys and Ignite >>>>>>> will take care of the rest. Btw, you might be interested in this SQL >>>>>>> webinar we delivered recently. The webinar explains our SQL >>>>>>> capabilities in >>>>>>> detail: https://www.youtube.com/watch?v=eYV-tNLzIts >>>>>>> >>>>>>> - >>>>>>> Denis >>>>>>> >>>>>>> >>>>>>> On Wed, Jun 17, 2020 at 11:28 AM narges saleh <snarges...@gmail.com> >>>>>>> wrote: >>>>>>> >>>>>>>> Then can you please explain what the option collocated=true for >>>>>>>> JDBC url is for? >>>>>>>> Should I assume that if the table is defined with affinity set, >>>>>>>> then compute collocation, including sql execution will happen >>>>>>>> automatically? >>>>>>>> I am sorry if this is just a basic question. >>>>>>>> >>>>>>>> On Wed, Jun 17, 2020 at 12:57 PM Denis Magda <dma...@apache.org> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> If the data is already co-located you don't need to pass in any >>>>>>>>> other parameters. >>>>>>>>> >>>>>>>>> - >>>>>>>>> Denis >>>>>>>>> >>>>>>>>> >>>>>>>>> On Wed, Jun 17, 2020 at 10:13 AM narges saleh < >>>>>>>>> snarges...@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> Hi Denis. The tables are defined with affinity keys specified. >>>>>>>>>> What I am asking whether to set the collection option when I insert >>>>>>>>>> or >>>>>>>>>> query the data via SQL line. >>>>>>>>>> >>>>>>>>>> thanks >>>>>>>>>> >>>>>>>>>> On Wed, Jun 17, 2020 at 11:39 AM Denis Magda <dma...@apache.org> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> Hi, >>>>>>>>>>> >>>>>>>>>>> You need to pass the required parameters to the JDBC driver's >>>>>>>>>>> connection URL: >>>>>>>>>>> https://apacheignite-sql.readme.io/docs/jdbc-driver#parameters >>>>>>>>>>> >>>>>>>>>>> However, I'm not quite sure what you mean under the >>>>>>>>>>> "collocation" in the following context. All your data needs to be >>>>>>>>>>> co-located beforehand (ensure you have affinity keys configured) >>>>>>>>>>> and then >>>>>>>>>>> just send your queries with joins with no additional connection >>>>>>>>>>> parameters. >>>>>>>>>>> >>>>>>>>>>> - >>>>>>>>>>> Denis >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Wed, Jun 17, 2020 at 9:33 AM narges saleh < >>>>>>>>>>> snarges...@gmail.com> wrote: >>>>>>>>>>> >>>>>>>>>>>> Hi All, >>>>>>>>>>>> >>>>>>>>>>>> Is collocation enforced when SQL is issues in sqline command >>>>>>>>>>>> line or do I need to give the option when I start sqlline? >>>>>>>>>>>> >>>>>>>>>>>> thanks. >>>>>>>>>>>> >>>>>>>>>>> -- - Denis
