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 <[email protected]> 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 > - 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 <[email protected]> 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 <[email protected]> >> 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 <[email protected]> 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 <[email protected]> >>>> 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 <[email protected]> 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 <[email protected]> >>>>>> 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 <[email protected]> >>>>>>> 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 <[email protected]> >>>>>>>> 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 <[email protected]> >>>>>>>>> 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 < >>>>>>>>>> [email protected]> 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. >>>>>>>>>>> >>>>>>>>>>
