The do one prepared statement per query-type and put them in a map per query string
Sent from mobile device Am 06.05.2014 um 11:26 schrieb Gene Tan <[email protected]>: > Hi Michael > > Is it possible to do this if the labels and relationships for nodes changes, > as upon reading i am not able to parameterized labels and relationships. > > as the batch of per 500 queries may differ on relationships and labels being > used per query. > > On Tuesday, May 6, 2014 3:47:07 PM UTC+8, Michael Hunger wrote: >> >> Don't acquire new connection objects >> Don't create new prepared statements >> >> Sent from mobile device >> >> Am 06.05.2014 um 09:31 schrieb Gene Tan <[email protected]>: >> >>> Hi Michael, >>> >>> I have made changes to the query and the code used but it seems the query >>> time for jdbc driver is still slower comapred to Java Rest Binding, >>> >>> MERGE (firstNode:Soap {id:{1}}) >>> ON CREATE SET firstNode.brand={2}, firstNode.updated = timestamp() >>> ON MATCH SET firstNode.updated=timestamp() >>> MERGE (secondNode:Company {id:'{3}'}) >>> ON CREATE SET secondNode.name="{4}", secondNode.updated = timestamp() >>> ON MATCH SET secondNode.updated=timestamp() >>> WITH firstNode, secondNode >>> OPTIONAL MATCH firstNode - [existing:`manufacturer`] - () >>> DELETE existing >>> MERGE (firstNode)-[r:`manufacturer`]-(secondNode); >>> >>> public void processList(List<CYPHERExecuteQueryObject> list) { >>> for (CYPHERExecuteQueryObject c : list) { >>> neo4jjdbcinsert(c.getQuery(), c.getParams()); >>> ctr++; >>> if (ctr == 500) { >>> commit(); >>> ctr = 0; >>> } >>> } >>> } >>> >>> // set auto commit to false >>> public void neo4jjdbcinsert(String query, Object... params) { >>> try { >>> if (con.isClosed()) { >>> con = >>> DriverManager.getConnection("jdbc:neo4j://localhost:7474/"); >>> con.setAutoCommit(false); >>> } >>> try { >>> PreparedStatement preparedStatement = >>> con.prepareStatement(query); >>> preparedStatement.clearParameters(); >>> for (int i = 0; i < params.length; i++) { >>> preparedStatement.setObject(i+1, params[i]); >>> } >>> preparedStatement.execute(); >>> } catch (Exception e) { >>> e.printStackTrace(); >>> } >>> } catch (SQLException e) { >>> e.printStackTrace(); >>> } >>> } >>> >>> public void commit() { >>> try { >>> con.commit(); >>> } catch (SQLException e) { >>> e.printStackTrace(); >>> } finally { >>> try { >>> con.close(); >>> } catch (SQLException e) { >>> e.printStackTrace(); >>> } >>> } >>> } >>> >>> >>> >>> >>> On Tuesday, May 6, 2014 2:37:17 PM UTC+8, Michael Hunger wrote: >>>> >>>> 1. You still don't use labels and indexes in MERGE, it should look like >>>> this: >>>> >>>>> MERGE (firstNode:Soap {id:{1}}) >>>>> ON CREATE SET firstNode.brand={2} >>>> SET firstNode.updated = timestamp() >>>> 2. You don't use the API like that, but instead create a single >>>> PreparedStatement then add parameters and execute, then clear, add new >>>> parameters and execute >>>> You don't operate on Neo4jConnection but only with the JDBC APIs. >>>> >>>> Also this doesn't work: firstNode.brand="{2}" >>>> it must be: firstNode.brand={2} >>>> >>>> >>>> Am 06.05.2014 um 08:19 schrieb Gene Tan <[email protected]>: >>>> >>>>> Hi Michael. >>>>> >>>>> I tried out using jdbc driver using the same scenario mentioned earlier >>>>> inserting data by batch of 500 queries >>>>> and query >>>>> MERGE (firstNode {id:{1}}) >>>>> ON CREATE SET firstNode.brand="{2}", firstNode.updated = timestamp(), >>>>> firstNode:Soap >>>>> ON MATCH SET firstNode.updated=timestamp() >>>>> MERGE (secondNode{id:'{3}'}) >>>>> ON CREATE SET secondNode.name="{4}", secondNode.updated = timestamp(), >>>>> secondNode:Company >>>>> ON MATCH SET secondNode.updated=timestamp() >>>>> WITH firstNode, secondNode >>>>> //CHANGE MANUFACTURER if there is an existing manufacturer >>>>> OPTIONAL MATCH firstNode - [existing:`manufacturer`] - () >>>>> DELETE existing >>>>> CREATE UNIQUE p = (firstNode)-[r:`manufacturer`]-(secondNode) RETURN p; >>>>> and was wondering why jdbc driver processing time is slower that the java >>>>> rest binding, or I am just using the wrong way... because for a small >>>>> sample data 619 nodes and 4611 relationships it took the jdbc driver one >>>>> minute to insert the data while, it just took 21 seconds for the java >>>>> rest binding to insert the data. >>>>> Here is the code used for inserting data: >>>>> public void processList(List<CYPHERExecuteQueryObject> list) { >>>>> for (CYPHERExecuteQueryObject c : list) { >>>>> neo4jjdbcinsert(c.getQuery(), c.getParams()); >>>>> ctr++; >>>>> if (ctr == 500) { >>>>> commit(); >>>>> ctr = 0; >>>>> } >>>>> } >>>>> } >>>>> >>>>> // set auto commit to false >>>>> public void neo4jjdbcinsert(String query, Object... params) { >>>>> try { >>>>> if (con.isClosed()) { >>>>> con = (Neo4jConnection) >>>>> DriverManager.getConnection("jdbc:neo4j://localhost:7474/"); >>>>> con.setAutoCommit(false); >>>>> } >>>>> HashMap<String, Object> map = new HashMap<>(); >>>>> for (int i = 0; i < params.length; i++) { >>>>> map.put(i + 1 +"", params[i]); >>>>> } >>>>> try { >>>>> con.executeQuery(query, map); >>>>> } catch (Exception e) { >>>>> e.printStackTrace(); >>>>> } >>>>> } catch (SQLException e) { >>>>> e.printStackTrace(); >>>>> } >>>>> } >>>>> >>>>> public void commit() { >>>>> try { >>>>> con.commit(); >>>>> } catch (SQLException e) { >>>>> e.printStackTrace(); >>>>> } finally { >>>>> try { >>>>> con.close(); >>>>> } catch (SQLException e) { >>>>> e.printStackTrace(); >>>>> } >>>>> } >>>>> } >>>>> >>>>> >>>>> Thanks >>>>> >>>>> On Friday, May 2, 2014 4:14:30 PM UTC+8, Gene Tan wrote: >>>>>> >>>>>> Thanks! Michael, will try this out >>>>>> >>>>>> On Thursday, May 1, 2014 5:15:12 PM UTC+8, Michael Hunger wrote: >>>>>>> >>>>>>> 1. Use labels + indexes (or constraints) >>>>>>> >>>>>>> Without an label + existing index your first operation (merge) will >>>>>>> have to go over all nodes in the graph to find if there is already a >>>>>>> duplicate which it would then merge to >>>>>>> the second CREATE UNIQUE should be a MERGE too. >>>>>>> >>>>>>> 2. Use parameters for your literal input values, otherwise cypher has >>>>>>> to recompile the query every time and cannot reuse-the pre-compiled >>>>>>> query plans. >>>>>>> 3. you might want to move from Java-Rest-Binding to the JDBC driver, >>>>>>> which is much better for cypher interactions with the server. >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Wed, Apr 30, 2014 at 9:25 AM, Gene Tan <[email protected]> wrote: >>>>>>>> Hello, >>>>>>>> >>>>>>>> I am new to neo4j >>>>>>>> currently already have a graph that contains at least 180000 nodes and >>>>>>>> 1400000 relationships , >>>>>>>> I am inserting data by batch of 500 queries, through the java rest >>>>>>>> binding library, and have observed that some queries had encountered >>>>>>>> read timeout, I was wondering if it is because of my query used for >>>>>>>> inserting data. Or is it related to configurations with neo4j.. >>>>>>>> Here is a sample query that I am using for inserting data >>>>>>>>> MERGE (firstNode {id:'1234'}) >>>>>>>>> ON CREATE SET firstNode.brand="Dove", firstNode.updated = >>>>>>>>> timestamp(), firstNode:Soap >>>>>>>>> ON MATCH SET firstNode.updated=timestamp() >>>>>>>>> MERGE (secondNode{id:'2345'}) >>>>>>>>> ON CREATE SET secondNode.name="Dove Manufacturer", secondNode.updated >>>>>>>>> = timestamp(), firstNode:Company >>>>>>>>> ON MATCH SET secondNode.updated=timestamp() >>>>>>>>> WITH firstNode, secondNode >>>>>>>>> //CHANGE MANUFACTURER if there is an existing manufacturer >>>>>>>>> OPTIONAL MATCH firstNode - [existing:`manufacturer`] - () >>>>>>>>> DELETE existing >>>>>>>>> CREATE UNIQUE p = (firstNode)-[r:`manufacturer`]-(secondNode) RETURN >>>>>>>>> p; >>>>>>>> >>>>>>>> Is there anyway to make this query run faster? >>>>>>>> >>>>>>>> Thanks! >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> You received this message because you are subscribed to the Google >>>>>>>> Groups "Neo4j" 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 >>>>> "Neo4j" 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 >>> "Neo4j" 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 > "Neo4j" 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 "Neo4j" 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.
