[jbosscache-issues] [JBoss JIRA] Resolved: (JBCACHE-1460) Inefficient remove() in JDBCCacheLoader

Inefficient remove() in JDBCCacheLoader --------------------------------------- Key: JBCACHE-1460 URL: https://jira.jboss.org/jira/browse/JBCACHE-1460 Project: JBoss Cache Issue Type: Bug Security Level: Public(Everyone can see) Components: Cache loaders Affects Versions: 2.2.1.CR1, 3.0.2.CR1 Reporter: Krzysztof Sobolewski Assignee: Manik Surtani Fix For: 3.0.2.GA As I can see, after JBCACHE-1176 the remove() method in JDBCCacheLoader uses a single statement to remove a node and all its children, but this statement is hugely inefficient itself, at least with MySQL 5.0 (on which we detected this problem). Granted that half a million rows is probably uncommon (and it was by accident this table had so many), but it could (and should?) be very efficient for much larger tables. Right now we see execution times of at least several seconds and reaching into minute range (if there are several concurrent deletes). The query in question (with defaults in the config) is: delete from jbosscache where concat(fqn, '/') like concat(?, '%') It's so clever that it totally baffles the database's optimizer and forces it to do a full table scan instead of range index lookup. The biggest problem here is that it's not easy to replace it with something more efficient. I'd like to change deleteNodeSql in the config to: delete from jbosscache where fqn = ? or fqn like concat(?, '/', '%') (and throw out appending '/' to the fqn in Java) This statement does the same thing while allowing the database engine to use the primary key, but it uses *two* placeholders instead of one as in the original, so a simple replacement does not work. I had to subclass the cache loader and almost-duplicate remove() to do it. (Another problem is that setDeleteNodeSql() in JDBCCacheLoaderConfig is ineffective - it's inherited from the superclass, but should be overriden as this class holds its own private copy of this value; I guess I should report it as a separate bug) BTW: The same, except for the setter problem, applies to recursiveChildrenSql property. BTW2: I'm also very uncomfortable with the thought that any '%' or '_' embedded in the fqn can do considerable damage, not only in remove... Yet another bug for that? :)