portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ate Douma <...@douma.nu>
Subject Re: preferences performance tuning
Date Mon, 10 Dec 2007 17:35:12 GMT
David Sean Taylor wrote:
> Ran performance tests here on the preferences, on about 3000+ prefs 
> (10000 nodes)
> 
> NO INDEX         : 62 seconds
> FULL_PATH     : 34 seconds
> NODE_FK             : 35 seconds
> BOTH            :   8 seconds
> 
> Where:
> 
> NO INDEX -- out of the box.
> 
> FULL_PATH :
> 
> ALTER TABLE  PREFS_NODE
>  ADD INDEX FULL_PATH_IDX(`FULL_PATH`);
> 
> NODE_FK:
> 
> ALTER TABLE  PREFS_PROPERTY_VALUE
>  ADD CONSTRAINT `PREFS_PARENT_FK` FOREIGN KEY `PREFS_PARENT_FK` (`NODE_ID`)
>     REFERENCES `PREFS_NODE` (`NODE_ID`)
>     ON DELETE CASCADE;
> 
> 
> I'd like to propose adding both indexes (NODE_FK, FULL_PATH) to the 
> schema/prefs.xml like this:
> 
> (1) UNCOMMENT: I think the wrong FK was commented out.:
> 
>         <!-- Still an issue with OJB 1.0.3 when deleting M-N. Foreign 
> Key Violation.
>         <foreign-key foreignTable="PREFS_NODE">
>             <reference foreign="NODE_ID" local="NODE_ID"/>
>         </foreign-key>
>         -->
> and adding a onDelete="cascade" constraint
> Surely OJB can now handle one-to-many cases like this...
> 
> Here is a self-referential FK, but it is not commented out:
> 
>         <foreign-key foreignTable="PREFS_NODE" name="FK_PREFS_NODE_1">
>             <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
>         </foreign-key>
> 
> (2) I also suggest adding:
> 
>         <index name="IX_FULLPATH_1">
>             <index-column name="FULL_PATH"/>
>         </index>
> 
> Note that all tests pass with these indexes enabled on the following 
> databases:
> 
> * MySQL 5
I've tested and can add:
  * Derby (after manually updating the derby sql (which we don't generate from the schema)
  * PostgreSQL 8.1


---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org


Mime
View raw message