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 <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. >>>>>>>>>> >>>>>>>>>
