From Bugzilla Helper: User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20030925 Description of problem: Category.getDefaultAncestors() should split the "ids" string by "/" to generate the ancestorsList. Coincidentally, the current implementation returns the correct result because all default_ancestors values end with a "/", but it makes the query slow. For a category with x ancestors and n characters in the default_ancestors string, the database has to search for n categories instead of just x+1. This gets worse the longer your category IDs are. Version-Release number of selected component (if applicable): How reproducible: Always Steps to Reproduce: 1. Execute some code which uses getDefaultAncestors(). 2. Take a look at the "default_ancestors in (...)" of the executed query. Actual Results: The "(...)" contains one string for every character in the category's default_ancestors column. Expected Results: Only use strings which can actually match something in the "(...)" list. Additional info:
We need to merge the following change from the trunk onto the 6.0 tree: Change 36775 by vadim@vadim-at-home on 2003/10/07 17:44:15 Fixed some very sketchy logic that was responsible for populating the IN clause of a SQL filter. Before the change, the ancestor path of "2654/2653/" would result in the following list being used as the IN filter: [, 2, 26, 265, 2654, 2654/, 2654/2, 2654/26, 2654/265, 2654/2653, 2654/2653/] After this change, the value of the list is computed as [, 2654/, 2654/2653/] Affected files ... ... //core-platform/dev/src/com/arsdigita/categorization/Category.java#39 edit
This change (36775) should be integrated into the 6.0 branch. One of our clients is currently experiencing performance problems because of the old getDefaultAncestors(). In fact, the query generated by the old version is by far the one with the highest number of total block gets.
You also may want to use changelist 40504 on top of 36775, which at least on Oracle makes things a little faster by filtering on the primary key instead of the default_ancestors column.
Closing old tickets.