Charles, In your case, you have many caches registered on server nodes (debug shows me about 80) which are to be created on the client. These caches don't participate in common activities, instead they are used to prepare execution plan. As for now, a separate request is sent to remote nodes for each unregistered cache. You can use client mode, but you need to register all the missing caches before running queries. After loading missing caches explicitly the time reduced to 50-60ms in client mode.
I've used a trick to avoid declaring all the caches in configuration file or calling getOrCreateCache for each of them. To give it a try, just add the line below before the queries execution logic in your example. IgniteStorage.getInstance().getIgniteCache(Quote.class.getName(), 0 ).unwrap(IgniteCacheProxy.class ).context().kernalContext().cache().createMissingCaches(); Kind regards, Alex. On Thu, Apr 13, 2017 at 10:08 AM, woo charles [via Apache Ignite Users] < ml-node+s70518n11926...@n6.nabble.com> wrote: > "If you select some data from the joined table it leads to all dynamic > cache creation requests being sent" <-- is that mean the client node will > copy the selected table to local? > > If I set false to client mode, it will become server node and > start participate in caching, compute execution, stream processing, etc., > Then it will affect the performance of my client program. How can I > prevent this? > > 2017-04-13 14:24 GMT+08:00 afedotov <[hidden email] > <http:///user/SendEmail.jtp?type=node&node=11926&i=0>>: > >> Hi, >> >> Sergi, when join query is called from client >> It leads to createMissingCaches being called which makes a remote >> requests of a dynamic cache creation for each registered but not enabled >> cache and since there is a cache for each entity there are many requests to >> server nodes. >> >> Charles, >> If you select some data from the joined table it leads to all dynamic >> cache creation requests being sent therefore allowing to skip these on the >> next query runs. >> To disable client mode in your example just pass false to >> Ignition.setClientMode(true) >> >> Kind regards, >> Alex >> >> >> >> 13 апр. 2017 г. 5:22 AM пользователь "woo charles [via Apache Ignite >> Users]" <[hidden email] >> <http:///user/SendEmail.jtp?type=node&node=11925&i=0>> написал: >> >> I found that this time can be reduced to a value below 100ms if I already >> selected some data from join query related table. >> For example, >> if I run 2 query "select * from Quote where stock_id = xxx" & "Select * >> from StockInfo where stock_id = xxx" first and then run the join query, >> the time for 1st join query will become similar to other(around 10 -20 >> ms). >> Why will it happen? >> >> Also, How to run queries from a server node? I had try >> "ignite.compute().run()" but it doesn't work. >> >> >> thanks& best regards, >> Charles >> >> 2017-04-13 0:48 GMT+08:00 Sergi Vladykin <[hidden email] >> <http:///user/SendEmail.jtp?type=node&node=11922&i=0>>: >> >>> Alex, >>> >>> Why do we have such a huge difference between client nodes and server >>> nodes? Looks like we should fix it if possible. Even 7 seconds looks too >>> much for me. >>> >>> Sergi >>> >>> 2017-04-12 18:11 GMT+03:00 afedotov <[hidden email] >>> <http:///user/SendEmail.jtp?type=node&node=11922&i=1>>: >>> >>>> Hi Charles, >>>> >>>> You are running the query from a client node what implies additional >>>> network round trips. >>>> Try to run queries from a server node. In my environment it reduced the >>>> time from about 7 seconds to 220ms for the first run. >>>> >>>> Kind regards, >>>> Alex. >>>> >>>> On Wed, Apr 12, 2017 at 9:21 AM, woo charles [via Apache Ignite Users] >>>> <[hidden email] <http:///user/SendEmail.jtp?type=node&node=11908&i=0>> >>>> wrote: >>>> >>>>> *Sorry for wrong calculation * >>>>> *-> (i.e. 3 Server node stored 236MB [2.2MB * 20 table +3.2MB * 60 >>>>> table] data)* >>>>> >>>>> *Best Regards,* >>>>> *Charles* >>>>> >>>>> 2017-04-12 10:17 GMT+08:00 woo charles <[hidden email] >>>>> <http:///user/SendEmail.jtp?type=node&node=11896&i=0>>: >>>>> >>>>>> *Source >>>>>> code: https://drive.google.com/open?id=0B_-zUEFkybdLQVF4U2dwTE11ajA >>>>>> <https://drive.google.com/open?id=0B_-zUEFkybdLQVF4U2dwTE11ajA>* >>>>>> >>>>>> *Below is a screen cap on GridGain's web console & the log of my >>>>>> client program. * >>>>>> *(i.e. 3 Server node stored 756MB [2.2MB * 10 table +3.2MB * 30 >>>>>> table] data)* >>>>>> [image: 內置圖片 2] >>>>>> >>>>>> Apr 12, 2017 9:50:10 AM java.util.logging.LogManager$RootLogger log >>>>>> SEVERE: Failed to resolve default logging config file: >>>>>> config/java.util.logging.properties >>>>>> [09:50:10] __________ ________________ >>>>>> [09:50:10] / _/ ___/ |/ / _/_ __/ __/ >>>>>> [09:50:10] _/ // (7 7 // / / / / _/ >>>>>> [09:50:10] /___/\___/_/|_/___/ /_/ /___/ >>>>>> [09:50:10] >>>>>> [09:50:10] ver. 1.8.0#20161205-sha1:9ca40dbe >>>>>> [09:50:10] 2016 Copyright(C) Apache Software Foundation >>>>>> [09:50:10] >>>>>> [09:50:10] Ignite documentation: http://ignite.apache.org >>>>>> [09:50:10] >>>>>> [09:50:10] Quiet mode. >>>>>> [09:50:10] ^-- To see **FULL** console log here add >>>>>> -DIGNITE_QUIET=false or "-v" to ignite.{sh|bat} >>>>>> [09:50:10] >>>>>> [09:50:10] OS: Linux 3.10.0-123.el7.x86_64 amd64 >>>>>> [09:50:10] VM information: Java(TM) SE Runtime Environment >>>>>> 1.8.0_121-b13 Oracle Corporation Java HotSpot(TM) 64-Bit Server VM >>>>>> 25.121-b13 >>>>>> [09:50:10] Initial heap size is 128MB (should be no less than 512MB, >>>>>> use -Xms512m -Xmx512m). >>>>>> [09:50:10] Configured plugins: >>>>>> [09:50:10] ^-- None >>>>>> [09:50:10] >>>>>> [09:50:10] Security status [authentication=off, tls/ssl=off] >>>>>> [09:50:11] To start Console Management & Monitoring run >>>>>> ignitevisorcmd.{sh|bat} >>>>>> [09:50:11] >>>>>> [09:50:11] Ignite node started OK (id=b663472c) >>>>>> [09:50:11] Topology snapshot [ver=19, servers=3, clients=1, CPUs=8, >>>>>> heap=3.1GB] >>>>>> PreLoadDataSize = 10000 >>>>>> NumOfTest = 2 >>>>>> /----------------------- Test 0 -----------------------\ >>>>>> Name = SQL_Select_2_Join_10 >>>>>> ActionType = SQL_SELECT_2_JOIN >>>>>> Object class = com.performance.test.dao.Quote >>>>>> Thread Size = 80 >>>>>> [09:50:20] New version is available at ignite.apache.org: 1.9.0 >>>>>> MaxTime(First Time): 12710 >>>>>> Thread [SQL_Select_2_Join_10] Average Time: 18.913ms - 0.018913s, Max >>>>>> Time: 183ms >>>>>> \-----------------------------------------------------/ >>>>>> /----------------------- Test 1 -----------------------\ >>>>>> Name = QUERY_Quote_price_large_1000_Small_950 >>>>>> ActionType = QUERY >>>>>> Object class = com.performance.test.dao.Quote >>>>>> Thread Size = 80 >>>>>> MaxTime(First Time): 30 >>>>>> Thread [QUERY_Quote_price_large_1000_Small_950] Average Time: >>>>>> 118.752ms - 0.118752s, Max Time: 1094ms >>>>>> \-----------------------------------------------------/ >>>>>> >>>>>> *Also, I found that 1st **SQL J**oin query time reduced about 2 - 3s >>>>>> if my program's memory **increase** from 128mb to 1gb.* >>>>>> *Is it show that this problem is due to client side not server side?* >>>>>> >>>>>> *Best Regards,* >>>>>> *Charles* >>>>>> >>>>>> >>>>>> 2017-04-11 23:29 GMT+08:00 afedotov <[hidden email] >>>>>> <http:///user/SendEmail.jtp?type=node&node=11896&i=1>>: >>>>>> >>>>>>> I'm not able to reproduce the timings you specified. Probably I do >>>>>>> something wrong. >>>>>>> Could you please provide a log for that case? >>>>>>> Also, it would be of great help if you provide the reproducer >>>>>>> sources. >>>>>>> >>>>>>> Kind regards, >>>>>>> Alex. >>>>>>> >>>>>>> On Tue, Apr 11, 2017 at 4:57 AM, woo charles [via Apache Ignite >>>>>>> Users] <[hidden email] >>>>>>> <http:///user/SendEmail.jtp?type=node&node=11889&i=0>> wrote: >>>>>>> >>>>>>>> My testing program: https://drive.google.com/open? >>>>>>>> id=0B_-zUEFkybdLcW5LR3RXZnJfOFE >>>>>>>> >>>>>>>> It runs on linux system with 8cpu 24gb ram. >>>>>>>> 3 Server node(each 1 gb) are created. >>>>>>>> 20 set tables created(ie. 1 set table contain table >>>>>>>> Quote, StockInfo, BidAskBrokerQueue, PreviousQuote which mean 20*4 = 80 >>>>>>>> table created) >>>>>>>> Each table will insert 10000 records. >>>>>>>> >>>>>>>> After inserted data, sql join "select q.stock_id, q.price , >>>>>>>> s.symbol from Quote0.Quote as q, StockInfo0.StockInfo as s where >>>>>>>> q.stock_id = s.stock_id and q.stock_id = XXX" is perform. >>>>>>>> >>>>>>>> And about 13s is needed for the first sql query. >>>>>>>> >>>>>>>> >>>>>>>> 2017-04-10 14:53 GMT+08:00 Sergi Vladykin <[hidden email] >>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11870&i=0>>: >>>>>>>> >>>>>>>>> I think 13s is still too much. Can you share a reproducer? >>>>>>>>> >>>>>>>>> Sergi >>>>>>>>> >>>>>>>>> 2017-04-10 9:51 GMT+03:00 afedotov <[hidden email] >>>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11870&i=1>>: >>>>>>>>> >>>>>>>>>> Hello, >>>>>>>>>> >>>>>>>>>> That is an expected behavior. On the first run a query is parsed >>>>>>>>>> and metadata is built for it what takes some additional time >>>>>>>>>> compared to >>>>>>>>>> the following runs. >>>>>>>>>> >>>>>>>>>> Kind regards, >>>>>>>>>> Alex >>>>>>>>>> >>>>>>>>>> 10 апр. 2017 г. 8:13 AM пользователь "woo charles [via Apache >>>>>>>>>> Ignite Users]" <[hidden email] >>>>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11848&i=0>> написал: >>>>>>>>>> >>>>>>>>>>> Hi, >>>>>>>>>>> >>>>>>>>>>> I set up 3 Server nodes(8 cpu, 1gb ram) in the same server. >>>>>>>>>>> 2 table each 10000 rows are entered. >>>>>>>>>>> >>>>>>>>>>> When I perform a *SQL Join* query on two table and run it 100 >>>>>>>>>>> times, I found that the first query is much slower than other(i.e. >>>>>>>>>>> First >>>>>>>>>>> query used 13s & other used around 0.02s). >>>>>>>>>>> >>>>>>>>>>> Why will it happen? >>>>>>>>>>> Can I reduce the running time on 1st query? >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> ------------------------------ >>>>>>>>>>> If you reply to this email, your message will be added to the >>>>>>>>>>> discussion below: >>>>>>>>>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>>>>>>>>> ime-query-SQL-Join-tp11847.html >>>>>>>>>>> To start a new topic under Apache Ignite Users, email [hidden >>>>>>>>>>> email] <http:///user/SendEmail.jtp?type=node&node=11848&i=1> >>>>>>>>>>> To unsubscribe from Apache Ignite Users, click here. >>>>>>>>>>> NAML >>>>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> ------------------------------ >>>>>>>>>> View this message in context: Re: Slow on 1st time query "SQL >>>>>>>>>> Join" >>>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11848.html> >>>>>>>>>> Sent from the Apache Ignite Users mailing list archive >>>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> ------------------------------ >>>>>>>> If you reply to this email, your message will be added to the >>>>>>>> discussion below: >>>>>>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>>>>>> ime-query-SQL-Join-tp11847p11870.html >>>>>>>> To start a new topic under Apache Ignite Users, email [hidden >>>>>>>> email] <http:///user/SendEmail.jtp?type=node&node=11889&i=1> >>>>>>>> To unsubscribe from Apache Ignite Users, click here. >>>>>>>> NAML >>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> ------------------------------ >>>>>>> View this message in context: Re: Slow on 1st time query "SQL Join" >>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11889.html> >>>>>>> Sent from the Apache Ignite Users mailing list archive >>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> ------------------------------ >>>>> If you reply to this email, your message will be added to the >>>>> discussion below: >>>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>>> ime-query-SQL-Join-tp11847p11896.html >>>>> To start a new topic under Apache Ignite Users, email [hidden email] >>>>> <http:///user/SendEmail.jtp?type=node&node=11908&i=1> >>>>> To unsubscribe from Apache Ignite Users, click here. >>>>> NAML >>>>> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >>>>> >>>> >>>> >>>> ------------------------------ >>>> View this message in context: Re: Slow on 1st time query "SQL Join" >>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11908.html> >>>> Sent from the Apache Ignite Users mailing list archive >>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>> >>> >>> >> >> >> ------------------------------ >> If you reply to this email, your message will be added to the discussion >> below: >> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >> ime-query-SQL-Join-tp11847p11922.html >> To start a new topic under Apache Ignite Users, email [hidden email] >> <http:///user/SendEmail.jtp?type=node&node=11925&i=1> >> To unsubscribe from Apache Ignite Users, click here. >> NAML >> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >> >> >> >> *first_run_from_client.png* (119K) Download Attachment >> <http://apache-ignite-users.70518.x6.nabble.com/attachment/11925/0/first_run_from_client.png> >> *first_run_from_client.png* (119K) Download Attachment >> <http://apache-ignite-users.70518.x6.nabble.com/attachment/11925/1/first_run_from_client.png> >> >> ------------------------------ >> View this message in context: Re: Slow on 1st time query "SQL Join" >> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11925.html> >> Sent from the Apache Ignite Users mailing list archive >> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >> > > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > http://apache-ignite-users.70518.x6.nabble.com/Slow-on- > 1st-time-query-SQL-Join-tp11847p11926.html > To start a new topic under Apache Ignite Users, email > ml-node+s70518n1...@n6.nabble.com > To unsubscribe from Apache Ignite Users, click here > <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=1&code=YWxleGFuZGVyLmZlZG90b2ZmQGdtYWlsLmNvbXwxfC0xMzYxNTU0NTg=> > . > NAML > <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11941.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.
