Thank you very much for the details. I'll work on this soon and let you know
Luigi 2015-05-20 15:38 GMT+02:00 machak <[email protected]>: > On Wednesday, May 20, 2015 at 2:52:26 PM UTC+2, machak wrote: >> >> Hi Luigi, >> On Wednesday, May 20, 2015 at 9:24:59 AM UTC+2, Luigi Dell'Aquila wrote: >>> >>> Hi, >>> >>> thank you for reporting. I'd like to have some more details to >>> understand if it's a corner case or if it's the average. >>> Anyway I'll investigate it. >>> In the roadmap we have the caching of SQL statements (like prepared >>> statements) but it will not be released in 2.1 >>> >>> >> please see attached simple testcase...my query is similar to one below, >> timings on 2.0.9 are around *1.3* >> and for 2.1-SNAPSHOT *3.2* >> >> > forgot to say, you can test it easily on snapshot version by switching > strictsql property..e.g. > > final OCommandRequest command = new OCommandSQL("alter database custom > strictSql = false"); > graph.command(command).execute(); > > > > > > > >> Normally I wouldn't care that much, but for one of my projects I need >> quite low latencies (it's not anything like trading platform, but I need to >> keep it quite low) so I am optimizing anything I can.. :) >> >> > >> import java.util.Calendar; >> >> import com.orientechnologies.orient.core.command.OCommandRequest; >> import com.orientechnologies.orient.core.metadata.schema.OType; >> import com.orientechnologies.orient.core.sql.query.OSQLSynchQuery; >> import com.tinkerpop.blueprints.impls.orient.OrientGraphNoTx; >> import com.tinkerpop.blueprints.impls.orient.OrientVertex; >> import com.tinkerpop.blueprints.impls.orient.OrientVertexType; >> >> import org.apache.logging.log4j.LogManager; >> import org.apache.logging.log4j.Logger; >> import org.testng.annotations.BeforeClass; >> import org.testng.annotations.Test; >> >> @Test >> public class SimpleTest { >> private static final Logger log = LogManager.getLogger(SimpleTest.class); >> >> private OrientGraphNoTx graph; >> int counter = 0; >> >> @Test >> public void testQuery() { >> >> final long start = System.nanoTime(); >> for (int i = 0; i < 10000; i++) { >> final OCommandRequest command = new OSQLSynchQuery<>("select >> from Game where (user1 = 'user1' or user2 = 'user1') and result = -1 order >> by gamedate asc"); >> final Iterable<OrientVertex> it = graph.command(command) >> .setFetchPlan("*:-2") >> .setLimit(10) >> .execute(); >> for (final OrientVertex anIt : it) { >> counter++; >> } >> } >> final long end = System.nanoTime(); >> log.info("# {}", ((end - start) / 1000000)); >> log.info("counter {}", counter); >> >> // 3280 @2.1-SNAPSHOT >> // 1304 @2.0.9 >> >> } >> >> @BeforeClass >> public void setup() { >> >> >> graph = new OrientGraphNoTx("memory:/TestDB", "admin", "admin"); >> // game >> final OrientVertexType game = graph.createVertexType("Game"); >> game.createProperty("result", OType.INTEGER); >> game.createProperty("user1", OType.STRING); >> game.createProperty("user2", OType.STRING); >> game.createProperty("gamedate", OType.DATETIME); >> // data >> createGame("user1", "user2"); >> createGame("user2", "user1"); >> >> >> } >> >> private void createGame(final String user1, final String user2) { >> final OrientVertex g1 = graph.addVertex("class:Game"); >> g1.setProperty("result", -1); >> g1.setProperty("user1", user1); >> g1.setProperty("user2", user2); >> g1.setProperty("gamedate", Calendar.getInstance().getTime()); >> graph.commit(); >> } >> >> >> } >> >> >> >> >> >> >> >>> Luigi >>> >>> >>> 2015-05-19 17:02 GMT+02:00 machak <[email protected]>: >>> >>>> On Monday, February 16, 2015 at 10:54:10 AM UTC+1, Luigi Dell'Aquila >>>> wrote: >>>>> >>>>> Hi, >>>>> >>>>> OrientDB 2.0 was released just a few week ago, but we are already >>>>> working hard to the next release. >>>>> >>>>> You know that two of the oldest pieces of code inside OrientDB core >>>>> are the SQL parser and the query executor, and you know that both have >>>>> some >>>>> flaws. >>>>> Some of you struggled to make queries work with additional blank >>>>> spaces(see https://github.com/orientechnologies/orientdb/issues/3559 >>>>> and https://github.com/orientechnologies/orientdb/issues/3519 ) >>>>> or to pass named/unnamed parameters to functions and inner queries >>>>> (see https://github.com/orientechnologies/orientdb/issues/1069). >>>>> >>>>> After releasing 2.0 we started a new development path to have: >>>>> - strict query language definition >>>>> - strict query validation with better syntax error messages >>>>> - full support to prepared statements and parameters (also where now >>>>> it fails) >>>>> - new command/query executor structure (stateless, less memory >>>>> consuming) >>>>> - better query execution plans and index management >>>>> - some additional operators (suspense here ;-) you have to wait for >>>>> 3.0) >>>>> >>>>> This path will end with 3.0, but in 2.1 we are releasing the first >>>>> step, that is the new query parser. >>>>> >>>>> The new parser is based on well known JavaCC. It processes all the SQL >>>>> statements and produces a tree structure that will be the future query >>>>> executor. So the final query execution structure will be the following: >>>>> >>>>> 1. Get the SQL statement >>>>> 2. look for a cached executor >>>>> 3. if it exists goto 7 >>>>> 4. parse the statement >>>>> 5. optimize the statement based on schema and indexes >>>>> 6. put it in the statement cache >>>>> 7. run the (new) executor with input parameters >>>>> 8. send the (streamed) result to the client >>>>> >>>>> At this step, the SQL parser is ready, but the query executor is too >>>>> complex to be refactored in time for 2.1, so we decided to go with a >>>>> hybrid >>>>> solution, to let you have a taste of the new features. >>>>> Here's how the query execution changes in 2.1: >>>>> >>>>> 1. Get the SQL statement >>>>> 2. pre-parse the statement with the new SQL parser <- this gives you >>>>> strict SQL syntax and much better syntax errors >>>>> 3. replace named/unnamed parameters <- this solves problems in >>>>> parameter passing to subqueries, functions etc. >>>>> 4. rewrite the query based on the parsed structure <- this solves >>>>> problems with white spaces and so on... I know it's dirty stuff, but the >>>>> old executor is too coupled with the old parser to be completely split in >>>>> such a short time >>>>> 5. use the old parser to parse the rewritten query and generate the >>>>> old executor <- a very small fraction of old parsing problems will remain >>>>> for now, sorry... >>>>> 6. run the (old) executor >>>>> >>>>> >>>>> ** AN EYE TO THE PAST ** >>>>> >>>>> Advantages in the short term are obvious, but you can also experience >>>>> small problems in the migration, eg. >>>>> >>>>> - old parser lets you write things like >>>>> SELECT FROM FOO ORDER BY A ASC ORDER BY A DESC GROUP BY A ORDER BY A >>>>> DESC >>>>> or even >>>>> SELECT FROM FOO ORDER BY A ASC, , , , A ASC >>>>> with the new parser you will not be allowed to do this, now every >>>>> clause has its own position in the statement (see docs) >>>>> >>>>> - old parser supports some old (deprecated) operators like traverse() >>>>> function (different from TRAVERSE statement) >>>>> eg. SELECT FROM Profile WHERE any() traverse(0,3) (city = 'Rome') >>>>> This syntax will not be supported anymore, so with the new parser you >>>>> will get a syntax error >>>>> >>>>> - in old parser IN and CONTAINS operators validation is somehow lazy, >>>>> eg. you can write >>>>> SELECT FROM FOO WHERE a IN 'bar' >>>>> but it's supposed to be incorrect, because 'bar' is a string, not a >>>>> collection, so the new parser will throw an exception >>>>> >>>>> >>>>> ** AND HOW ABOUT BACKWARD COMPATIBILITY...? ** >>>>> >>>>> To allow a smooth transition, we decided to apply the following policy >>>>> to 2.1: >>>>> - old databases will continue to work with the old parser >>>>> - databases created with 2.1 will have the new parser enabled by >>>>> default >>>>> - for backward compatibility, you will be able to enable/disable the >>>>> new parser (for now) setting the "strictSql" parameter to the db (ALTER >>>>> DATABASE docs will be updated shortly) >>>>> >>>>> In next releases we will probably drop a lot of old code, so I suggest >>>>> you to get used to the new strict SQL validation ;-) >>>>> >>>>> ** HOW CAN I TRY IT NOW? ** >>>>> >>>>> All this is now in a GIT branch named "javacc_parser", but today we >>>>> will merge it in "develop" branch, so you will be able to try it in next >>>>> 2.1-SNAPSHOT releases. >>>>> >>>>> FOR NOW the new parser is enabled only on SELECT and TRAVERSE >>>>> statements, in next days we will also cover UPDATE, INSERT, DELETE, CREATE >>>>> EDGE, CREATE VERTEX and so on... >>>>> >>>>> If you have some time, please try it out and give us your feedback, WE >>>>> NEED YOUR HELP TO MAKE IT PERFECT!!! >>>>> >>>>> Thanks >>>>> >>>>> >>>> Hi Luigi, >>>> >>>> in a fairly simple app, I see performance drop between 2.0.9 and >>>> 2.1-SNAPSHOT, mainly due to time spent in orient parser classes,... 5000 >>>> req/sec drops to 3400 req/sec (34% of time is spent in query >>>> parsing/tokenizing). >>>> This is a small dataset and whole request is executing only one query, >>>> but still may be worth investigating... >>>> cheers >>>> /m >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>>> Luigi >>>>> >>>>> >>>>> >>>>> -- >>>>> Luigi Dell'Aquila >>>>> Orient Technologies LTD >>>>> >>>> -- >>>> >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "OrientDB" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
