<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">Nice one Mircea.<DIV><BR class="khtml-block-placeholder"></DIV><DIV>Some comments:</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>* Re: your "second impl", For the sake of general portability I'd stay away from prepared statements/triggers and strive for a Java-only solution. I'd leave optimisations such as prepared statements for people who want to further customise the JDBCCacheLoader for their specific DBMS product. Then again, this could be a configuration param where the "default" is to use the Java-only solution, and *if* the name and signature of a prepared statement is provided for a batch insert then use this.</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>* Again in the case of your second approach, you're using the fact that Fqns "start with" a particular string (e.g., "/a/b/c/") and removing these when you need to call a remove ("/a/b/c") is fine, only it still assumes that Fqns are comprised only of Strings. This is fine, this limitation currently exists in the JDBCCacheLoader anyway, just highlighting.</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>* "Also the firstAncestor query takes quite long on mysql." - What is this query? Is this a case of, if I am adding ("/a/b/c") you need to do a SELECT ... WHERE FQN="/a/b" ?Why is this specifically slower than any other SELECT on an indexed column? </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>* "Another observation is that new impl proves much better in the case of deeper trees, and fails up-2 50% in the case of flatter trees." - what do you mean by fails? I hope this "just" means worse performance rather than functional failure? :-) And if so, how much worse is this with flatter trees, and what is this threshold? 2 nodes deep, 3 nodes deep, etc?</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Cheers,</DIV><DIV><DIV><SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><DIV>--</DIV><DIV>Manik Surtani</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Lead, JBoss Cache</DIV><DIV>JBoss, a division of Red Hat</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Email: <A href="mailto:manik@jboss.org">manik@jboss.org</A></DIV><DIV>Telephone: +44 7786 702 706</DIV><DIV>MSN: <A href="mailto:manik@surtani.org">manik@surtani.org</A></DIV><DIV>Yahoo/AIM/Skype: maniksurtani</DIV><DIV><BR class="khtml-block-placeholder"></DIV><BR class="Apple-interchange-newline"></SPAN></SPAN> </DIV><BR><DIV><DIV>On 29 Jan 2007, at 00:42, Mircea Markus wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite">Hi guys,<BR><BR>I've drafted the alternative implementation of JDBC cache loader. Just a brief, the base idea is to to traverse the tree in preorder and add some indexing info to each node.<BR>The affected methods are remove(Fqn), loadState(Fqn subtree, ..) and put(Fqn, value). <BR><BR>How I've tested the performance:<BR> a tree with a TREE_DEPTH depth is created. Each node in the tree has a CHILDREN number of children. By default I've used TREE_DEPTH=7 and CHILDREN=3 which gave a total number of 3280 children. A node is selected at each depth from disjunct subtrees. ( i.e. in the benchmark used 7 nodes are selected). On all those nodes a certain operation is performed: loadState or remove. For benchmarking the put operation, the in memory tree nodes are Collections.shuffle(List<Fqn>), then they are (randomly) added to the database/classLoader.put. Also additional testing was performed in the case of put for deep trees (100 - 200 nodes) <BR><BR><BR>Results: <BR>On the load and delete the new implementation is much more better. The new deletion time is linear compared with the old approach where recursive db calls lead to an exponential raise(see - remove_node_all.PNG). Relatively the same in the case of loading subtrees(see - remove_node_all.PNG). <BR>The problem is in the case of additions. When adding all 3280 nodes the time is constantly 10 times bigger than when using existing implementation. Profiling shewed that the update_indexes operation behaves really badly - nothing to do in this area. Even if the operation is totally moved in the database (mysql 5.0.X has limitations on working with triggers so I've used a stored procedure) is doesn't cause an significant increase. <BR><BR>Another approach.<BR>Another approach I've consider is to reduce the recursive DB calls to a fix number. All operation rely on the fact that the entire fqn is persisted for each node. E.g. when deleting node(/a/b/c) I'll remove all records that have a path starting with (/a/b/c/). Same in the case of node loading. It proved to work even better than in the case of first implementation.<BR>Recursion in the case of insertions - I've solved this by <BR>a) querying for the first persited ancestor <BR>b) add all the children in a batch pr statement. <BR>Interestingly enough this always-2-queries approach is 30% slower than the old recursive impl. Profiling showed that the db driver (mysql-connector/J) takes the same time for executing a batch (new impl) as it takes in the case of individual calls (old impl).Also the firstAncestor query takes quite long on mysql. Another observation is that new impl proves much better in the case of deeper trees, and fails up-2 50% in the case of flatter trees. An interesting thing would be to see it working on an Oracle/SqlServ... The good news is that old implementation can be used instead - or a policy can be defined to use the new impl where tree structure requires it. <BR><BR>Naturally I'd chose the second approach - if you don't see any problem with it<BR><BR>I'll stop here :) <BR><BR>Cheers,<BR>Mircea<BR><BR><BR><BR><BR><BR> <SPAN><DIV><load_state_all.PNG></DIV></SPAN><SPAN><DIV><remove_node_all.PNG></DIV></SPAN><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">jbosscache-dev mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:jbosscache-dev@lists.jboss.org">jbosscache-dev@lists.jboss.org</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="https://lists.jboss.org/mailman/listinfo/jbosscache-dev">https://lists.jboss.org/mailman/listinfo/jbosscache-dev</A></DIV> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>