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