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] < ml-node+s70518n11896...@n6.nabble.com> 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-time-query-SQL-Join-tp11847p11896.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-tp11847p11908.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.