You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-dev@portals.apache.org by David Sean Taylor <da...@bluesunrise.com> on 2007/12/10 06:44:37 UTC
preferences performance tuning
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
....
Re: preferences performance tuning
Posted by David Sean Taylor <da...@bluesunrise.com>.
On Dec 9, 2007, at 9:44 PM, 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
>
Just another note on this, to address the performance issues from
another angle,
I am looking into a spring bean that runs in the initialization phase
of the Spring container to pre-load all (or a subset) of Portlet
default preferences at container startup
Possible candidates for pre-loading:
* portlet default preferences (by app, by portlet, all)
* page default preferences (specific pages...)
* no-entity preferences (from 2.1)
* user-specific ....
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by Vitaly Baranovsky <vi...@gmail.com>.
David, I can't test this schema file, because maven 1.0.2 gives me
Network is unreachable: connect. If you say me how can I solve this
problem I will check ms sql script for you.
Anyway, this schema will not work, because SQL can't create foreign
keys with ON DELETE CASCADE if foreign keys references to same table
(I've created such JIRA record:
http://issues.apache.org/jira/browse/JS2-838). So, you have to change
to onDelete="none" for FK_PREFS_NODE_1.
Output of maven build process:
j2:portal.conf.sql:
[mkdir] Created dir:
D:\java\src\portals-jetspeed-2.1.3-dev\target\portal-sql
[copy] Copying 1 file to
D:\java\src\portals-jetspeed-2.1.3-dev\target\portal-sql
[copy] Copying C:\Documents and
Settings\BaranovskyV\.maven\cache\maven-jetspeed2-plugin-2.1.3-dev\plugin-resources\sql\drop.sql
to D:\java\src\portals-j
etspeed-2.1.3-dev\target\portal-sql\drop.sql
j2:_set.rdbms.props:
j2:_generate.sql:
[echo] Using Torque schema located under: C:\Documents and
Settings\BaranovskyV/.maven/cache/maven-jetspeed2-plugin-2.1.3-dev/plugin-resources/schema
torque:init:
[echo] Generating SQL schema creation scripts for mssql
[mkdir] Created dir:
D:\java\src\portals-jetspeed-2.1.3-dev\target\portal-sql\mssql\schema
[torque-sql] Using classpath
[torque-sql] Generating to file
D:\java\src\portals-jetspeed-2.1.3-dev\target\portal-sql\mssql\schema\report.portal.sql.generation
BUILD FAILED
File...... C:\Documents and
Settings\BaranovskyV\.maven\cache\maven-jetspeed2-plugin-2.1.3-dev\plugin.jelly
Element... torque-sql
Line...... 824
Column.... 37
org.apache.torque.engine.EngineException: java.net.SocketException:
Network is unreachable: connect
Total time: 55 seconds
Finished at: Wed Dec 26 22:31:38 EET 2007
2007/12/19, David Sean Taylor <da...@bluesunrise.com>:
>
> On Dec 10, 2007, at 9:35 AM, Ate Douma wrote:
>
> Here is the proposed prefs.xml schema:
> I am afraid this may break SQL Server. Can someone test who knows SQL
> Server?
>
> <?xml version="1.0"?>
> <!--
> Licensed to the Apache Software Foundation (ASF) under one or more
> contributor license agreements. See the NOTICE file distributed with
> this work for additional information regarding copyright ownership.
> The ASF licenses this file to You under the Apache License, Version 2.0
> (the "License"); you may not use this file except in compliance with
> the License. You may obtain a copy of the License at
>
> http://www.apache.org/licenses/LICENSE-2.0
>
> Unless required by applicable law or agreed to in writing, software
> distributed under the License is distributed on an "AS IS" BASIS,
> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
> See the License for the specific language governing permissions and
> limitations under the License.
> -->
> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/
> database_3_2.dtd">
> <!-- Autogenerated by JDBCToXMLSchema! -->
> <database name="j2">
>
> <!--
> pref_node
> -->
> <table name="PREFS_NODE">
> <column name="NODE_ID" primaryKey="true" required="true"
> type="INTEGER"/>
> <column name="PARENT_NODE_ID" type="INTEGER"/>
> <column name="NODE_NAME" size="100" type="VARCHAR"/>
> <column name="NODE_TYPE" type="SMALLINT"/>
> <column name="FULL_PATH" size="254" type="VARCHAR"/>
> <column name="CREATION_DATE" type="TIMESTAMP"/>
> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> <foreign-key foreignTable="PREFS_NODE"
> name="FK_PREFS_NODE_1" onDelete="cascade">
> <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
> </foreign-key>
> <index name="IX_PREFS_NODE_1">
> <index-column name="PARENT_NODE_ID"/>
> </index>
> <index name="IX_PREFS_NODE_2">
> <index-column name="FULL_PATH"/>
> </index>
> </table>
> <!--
> prefs_property_value
> -->
> <table name="PREFS_PROPERTY_VALUE">
> <column name="PROPERTY_VALUE_ID" primaryKey="true"
> required="true" type="INTEGER"/>
> <column name="NODE_ID" type="INTEGER"/>
> <column name="PROPERTY_NAME" size="100" type="VARCHAR"/>
> <column name="PROPERTY_VALUE" size="254" type="VARCHAR"/>
> <column name="CREATION_DATE" type="TIMESTAMP"/>
> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> <!-- Still an issue with OJB 1.0.3 when deleting M-N.
> Foreign Key Violation.
> 2007/12/13 - DST - I've been testing with this index
> enabled on Oracle, MySQL
> and experiencing no issues. I think the index on
> should be the default case,
> and inferior databases will have to turn it off as
> part of the integration process
> -->
> <foreign-key foreignTable="PREFS_NODE" onDelete="cascade">
> <reference foreign="NODE_ID" local="NODE_ID" />
> </foreign-key>
> <index name="IX_FKPPV_1">
> <index-column name="NODE_ID"/>
> </index>
> </table>
> </database>
>
>
--
With best regards,
Vitaly Baranovsky
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by David Sean Taylor <da...@bluesunrise.com>.
On Dec 21, 2007, at 7:38 AM, Vitaly Baranovsky wrote:
> Hi, David!
>
> Are you changed source code during prefs optimization? Or you just
> changed database schema?
>
There is also a preference cache preload feature
Modified: portals/jetspeed-2/branches/JETSPEED-RELEASE-2.1-
POSTRELEASE/src/webapp/WEB-INF/assembly/prefs.xml
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/branches/
JETSPEED-RELEASE-2.1-POSTRELEASE/src/webapp/WEB-INF/assembly/
prefs.xml?rev=605693&r1=605692&r2=605693&view=diff
========================================================================
======
--- portals/jetspeed-2/branches/JETSPEED-RELEASE-2.1-POSTRELEASE/src/
webapp/WEB-INF/assembly/prefs.xml (original)
+++ portals/jetspeed-2/branches/JETSPEED-RELEASE-2.1-POSTRELEASE/src/
webapp/WEB-INF/assembly/prefs.xml Wed Dec 19 12:58:03 2007
@@ -25,6 +25,15 @@
<constructor-arg index="1">
<ref bean="preferencesCache" />
</constructor-arg>
+ <!-- list of portlet applications default preferences to
preload, leave list empty to not preload -->
+ <constructor-arg index='2'>
+ <list>
+ <value>j2-admin</value>
+ <value>demo</value>
+ </list>
+ </constructor-arg>
+ <!-- preload ALL Entities: warning this can chew up lots of
memory -->
+ <constructor-arg index='3'><value type="boolean">false</
value></constructor-arg>
</bean>
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by Vitaly Baranovsky <vi...@gmail.com>.
Hi, Ate!
Ok, I will test this changes in few days. And I will test schema for MS SQL.
2007/12/21, Ate Douma <at...@douma.nu>:
> Vitaly,
>
> The changes are only to the database schema, e.g. the new foreign key and indexes.
>
> Ate
>
> Vitaly Baranovsky wrote:
> > Hi, David!
> >
> > Are you changed source code during prefs optimization? Or you just
> > changed database schema?
> >
> > Thanks!
> >
> > 2007/12/19, David Sean Taylor <da...@bluesunrise.com>:
> >> On Dec 10, 2007, at 9:35 AM, Ate Douma wrote:
> >>
> >> Here is the proposed prefs.xml schema:
> >> I am afraid this may break SQL Server. Can someone test who knows SQL
> >> Server?
> >>
> >> <?xml version="1.0"?>
> >> <!--
> >> Licensed to the Apache Software Foundation (ASF) under one or more
> >> contributor license agreements. See the NOTICE file distributed with
> >> this work for additional information regarding copyright ownership.
> >> The ASF licenses this file to You under the Apache License, Version 2.0
> >> (the "License"); you may not use this file except in compliance with
> >> the License. You may obtain a copy of the License at
> >>
> >> http://www.apache.org/licenses/LICENSE-2.0
> >>
> >> Unless required by applicable law or agreed to in writing, software
> >> distributed under the License is distributed on an "AS IS" BASIS,
> >> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
> >> See the License for the specific language governing permissions and
> >> limitations under the License.
> >> -->
> >> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/
> >> database_3_2.dtd">
> >> <!-- Autogenerated by JDBCToXMLSchema! -->
> >> <database name="j2">
> >>
> >> <!--
> >> pref_node
> >> -->
> >> <table name="PREFS_NODE">
> >> <column name="NODE_ID" primaryKey="true" required="true"
> >> type="INTEGER"/>
> >> <column name="PARENT_NODE_ID" type="INTEGER"/>
> >> <column name="NODE_NAME" size="100" type="VARCHAR"/>
> >> <column name="NODE_TYPE" type="SMALLINT"/>
> >> <column name="FULL_PATH" size="254" type="VARCHAR"/>
> >> <column name="CREATION_DATE" type="TIMESTAMP"/>
> >> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> >> <foreign-key foreignTable="PREFS_NODE"
> >> name="FK_PREFS_NODE_1" onDelete="cascade">
> >> <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
> >> </foreign-key>
> >> <index name="IX_PREFS_NODE_1">
> >> <index-column name="PARENT_NODE_ID"/>
> >> </index>
> >> <index name="IX_PREFS_NODE_2">
> >> <index-column name="FULL_PATH"/>
> >> </index>
> >> </table>
> >> <!--
> >> prefs_property_value
> >> -->
> >> <table name="PREFS_PROPERTY_VALUE">
> >> <column name="PROPERTY_VALUE_ID" primaryKey="true"
> >> required="true" type="INTEGER"/>
> >> <column name="NODE_ID" type="INTEGER"/>
> >> <column name="PROPERTY_NAME" size="100" type="VARCHAR"/>
> >> <column name="PROPERTY_VALUE" size="254" type="VARCHAR"/>
> >> <column name="CREATION_DATE" type="TIMESTAMP"/>
> >> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> >> <!-- Still an issue with OJB 1.0.3 when deleting M-N.
> >> Foreign Key Violation.
> >> 2007/12/13 - DST - I've been testing with this index
> >> enabled on Oracle, MySQL
> >> and experiencing no issues. I think the index on
> >> should be the default case,
> >> and inferior databases will have to turn it off as
> >> part of the integration process
> >> -->
> >> <foreign-key foreignTable="PREFS_NODE" onDelete="cascade">
> >> <reference foreign="NODE_ID" local="NODE_ID" />
> >> </foreign-key>
> >> <index name="IX_FKPPV_1">
> >> <index-column name="NODE_ID"/>
> >> </index>
> >> </table>
> >> </database>
> >>
> >>
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
> For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
>
>
--
With best regards,
Vitaly Baranovsky
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by Ate Douma <at...@douma.nu>.
Vitaly,
The changes are only to the database schema, e.g. the new foreign key and indexes.
Ate
Vitaly Baranovsky wrote:
> Hi, David!
>
> Are you changed source code during prefs optimization? Or you just
> changed database schema?
>
> Thanks!
>
> 2007/12/19, David Sean Taylor <da...@bluesunrise.com>:
>> On Dec 10, 2007, at 9:35 AM, Ate Douma wrote:
>>
>> Here is the proposed prefs.xml schema:
>> I am afraid this may break SQL Server. Can someone test who knows SQL
>> Server?
>>
>> <?xml version="1.0"?>
>> <!--
>> Licensed to the Apache Software Foundation (ASF) under one or more
>> contributor license agreements. See the NOTICE file distributed with
>> this work for additional information regarding copyright ownership.
>> The ASF licenses this file to You under the Apache License, Version 2.0
>> (the "License"); you may not use this file except in compliance with
>> the License. You may obtain a copy of the License at
>>
>> http://www.apache.org/licenses/LICENSE-2.0
>>
>> Unless required by applicable law or agreed to in writing, software
>> distributed under the License is distributed on an "AS IS" BASIS,
>> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>> See the License for the specific language governing permissions and
>> limitations under the License.
>> -->
>> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/
>> database_3_2.dtd">
>> <!-- Autogenerated by JDBCToXMLSchema! -->
>> <database name="j2">
>>
>> <!--
>> pref_node
>> -->
>> <table name="PREFS_NODE">
>> <column name="NODE_ID" primaryKey="true" required="true"
>> type="INTEGER"/>
>> <column name="PARENT_NODE_ID" type="INTEGER"/>
>> <column name="NODE_NAME" size="100" type="VARCHAR"/>
>> <column name="NODE_TYPE" type="SMALLINT"/>
>> <column name="FULL_PATH" size="254" type="VARCHAR"/>
>> <column name="CREATION_DATE" type="TIMESTAMP"/>
>> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
>> <foreign-key foreignTable="PREFS_NODE"
>> name="FK_PREFS_NODE_1" onDelete="cascade">
>> <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
>> </foreign-key>
>> <index name="IX_PREFS_NODE_1">
>> <index-column name="PARENT_NODE_ID"/>
>> </index>
>> <index name="IX_PREFS_NODE_2">
>> <index-column name="FULL_PATH"/>
>> </index>
>> </table>
>> <!--
>> prefs_property_value
>> -->
>> <table name="PREFS_PROPERTY_VALUE">
>> <column name="PROPERTY_VALUE_ID" primaryKey="true"
>> required="true" type="INTEGER"/>
>> <column name="NODE_ID" type="INTEGER"/>
>> <column name="PROPERTY_NAME" size="100" type="VARCHAR"/>
>> <column name="PROPERTY_VALUE" size="254" type="VARCHAR"/>
>> <column name="CREATION_DATE" type="TIMESTAMP"/>
>> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
>> <!-- Still an issue with OJB 1.0.3 when deleting M-N.
>> Foreign Key Violation.
>> 2007/12/13 - DST - I've been testing with this index
>> enabled on Oracle, MySQL
>> and experiencing no issues. I think the index on
>> should be the default case,
>> and inferior databases will have to turn it off as
>> part of the integration process
>> -->
>> <foreign-key foreignTable="PREFS_NODE" onDelete="cascade">
>> <reference foreign="NODE_ID" local="NODE_ID" />
>> </foreign-key>
>> <index name="IX_FKPPV_1">
>> <index-column name="NODE_ID"/>
>> </index>
>> </table>
>> </database>
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by Vitaly Baranovsky <vi...@gmail.com>.
Hi, David!
Are you changed source code during prefs optimization? Or you just
changed database schema?
Thanks!
2007/12/19, David Sean Taylor <da...@bluesunrise.com>:
>
> On Dec 10, 2007, at 9:35 AM, Ate Douma wrote:
>
> Here is the proposed prefs.xml schema:
> I am afraid this may break SQL Server. Can someone test who knows SQL
> Server?
>
> <?xml version="1.0"?>
> <!--
> Licensed to the Apache Software Foundation (ASF) under one or more
> contributor license agreements. See the NOTICE file distributed with
> this work for additional information regarding copyright ownership.
> The ASF licenses this file to You under the Apache License, Version 2.0
> (the "License"); you may not use this file except in compliance with
> the License. You may obtain a copy of the License at
>
> http://www.apache.org/licenses/LICENSE-2.0
>
> Unless required by applicable law or agreed to in writing, software
> distributed under the License is distributed on an "AS IS" BASIS,
> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
> See the License for the specific language governing permissions and
> limitations under the License.
> -->
> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/
> database_3_2.dtd">
> <!-- Autogenerated by JDBCToXMLSchema! -->
> <database name="j2">
>
> <!--
> pref_node
> -->
> <table name="PREFS_NODE">
> <column name="NODE_ID" primaryKey="true" required="true"
> type="INTEGER"/>
> <column name="PARENT_NODE_ID" type="INTEGER"/>
> <column name="NODE_NAME" size="100" type="VARCHAR"/>
> <column name="NODE_TYPE" type="SMALLINT"/>
> <column name="FULL_PATH" size="254" type="VARCHAR"/>
> <column name="CREATION_DATE" type="TIMESTAMP"/>
> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> <foreign-key foreignTable="PREFS_NODE"
> name="FK_PREFS_NODE_1" onDelete="cascade">
> <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
> </foreign-key>
> <index name="IX_PREFS_NODE_1">
> <index-column name="PARENT_NODE_ID"/>
> </index>
> <index name="IX_PREFS_NODE_2">
> <index-column name="FULL_PATH"/>
> </index>
> </table>
> <!--
> prefs_property_value
> -->
> <table name="PREFS_PROPERTY_VALUE">
> <column name="PROPERTY_VALUE_ID" primaryKey="true"
> required="true" type="INTEGER"/>
> <column name="NODE_ID" type="INTEGER"/>
> <column name="PROPERTY_NAME" size="100" type="VARCHAR"/>
> <column name="PROPERTY_VALUE" size="254" type="VARCHAR"/>
> <column name="CREATION_DATE" type="TIMESTAMP"/>
> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> <!-- Still an issue with OJB 1.0.3 when deleting M-N.
> Foreign Key Violation.
> 2007/12/13 - DST - I've been testing with this index
> enabled on Oracle, MySQL
> and experiencing no issues. I think the index on
> should be the default case,
> and inferior databases will have to turn it off as
> part of the integration process
> -->
> <foreign-key foreignTable="PREFS_NODE" onDelete="cascade">
> <reference foreign="NODE_ID" local="NODE_ID" />
> </foreign-key>
> <index name="IX_FKPPV_1">
> <index-column name="NODE_ID"/>
> </index>
> </table>
> </database>
>
>
--
With best regards,
Vitaly Baranovsky
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by Vitaly Baranovsky <vi...@gmail.com>.
Ok. Our preference database is in MS SQL Server. I'll try. But I just
came back from vacation and have much work in my company. I'll try to
test with MS SQL in shortest time...
2007/12/19, David Sean Taylor <da...@bluesunrise.com>:
>
> On Dec 10, 2007, at 9:35 AM, Ate Douma wrote:
>
> Here is the proposed prefs.xml schema:
> I am afraid this may break SQL Server. Can someone test who knows SQL
> Server?
>
> <?xml version="1.0"?>
> <!--
> Licensed to the Apache Software Foundation (ASF) under one or more
> contributor license agreements. See the NOTICE file distributed with
> this work for additional information regarding copyright ownership.
> The ASF licenses this file to You under the Apache License, Version 2.0
> (the "License"); you may not use this file except in compliance with
> the License. You may obtain a copy of the License at
>
> http://www.apache.org/licenses/LICENSE-2.0
>
> Unless required by applicable law or agreed to in writing, software
> distributed under the License is distributed on an "AS IS" BASIS,
> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
> See the License for the specific language governing permissions and
> limitations under the License.
> -->
> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/
> database_3_2.dtd">
> <!-- Autogenerated by JDBCToXMLSchema! -->
> <database name="j2">
>
> <!--
> pref_node
> -->
> <table name="PREFS_NODE">
> <column name="NODE_ID" primaryKey="true" required="true"
> type="INTEGER"/>
> <column name="PARENT_NODE_ID" type="INTEGER"/>
> <column name="NODE_NAME" size="100" type="VARCHAR"/>
> <column name="NODE_TYPE" type="SMALLINT"/>
> <column name="FULL_PATH" size="254" type="VARCHAR"/>
> <column name="CREATION_DATE" type="TIMESTAMP"/>
> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> <foreign-key foreignTable="PREFS_NODE"
> name="FK_PREFS_NODE_1" onDelete="cascade">
> <reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
> </foreign-key>
> <index name="IX_PREFS_NODE_1">
> <index-column name="PARENT_NODE_ID"/>
> </index>
> <index name="IX_PREFS_NODE_2">
> <index-column name="FULL_PATH"/>
> </index>
> </table>
> <!--
> prefs_property_value
> -->
> <table name="PREFS_PROPERTY_VALUE">
> <column name="PROPERTY_VALUE_ID" primaryKey="true"
> required="true" type="INTEGER"/>
> <column name="NODE_ID" type="INTEGER"/>
> <column name="PROPERTY_NAME" size="100" type="VARCHAR"/>
> <column name="PROPERTY_VALUE" size="254" type="VARCHAR"/>
> <column name="CREATION_DATE" type="TIMESTAMP"/>
> <column name="MODIFIED_DATE" type="TIMESTAMP"/>
> <!-- Still an issue with OJB 1.0.3 when deleting M-N.
> Foreign Key Violation.
> 2007/12/13 - DST - I've been testing with this index
> enabled on Oracle, MySQL
> and experiencing no issues. I think the index on
> should be the default case,
> and inferior databases will have to turn it off as
> part of the integration process
> -->
> <foreign-key foreignTable="PREFS_NODE" onDelete="cascade">
> <reference foreign="NODE_ID" local="NODE_ID" />
> </foreign-key>
> <index name="IX_FKPPV_1">
> <index-column name="NODE_ID"/>
> </index>
> </table>
> </database>
>
>
--
With best regards,
Vitaly Baranovsky
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org
Re: preferences performance tuning
Posted by David Sean Taylor <da...@bluesunrise.com>.
On Dec 10, 2007, at 9:35 AM, Ate Douma wrote:
Here is the proposed prefs.xml schema:
I am afraid this may break SQL Server. Can someone test who knows SQL
Server?
<?xml version="1.0"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/
database_3_2.dtd">
<!-- Autogenerated by JDBCToXMLSchema! -->
<database name="j2">
<!--
pref_node
-->
<table name="PREFS_NODE">
<column name="NODE_ID" primaryKey="true" required="true"
type="INTEGER"/>
<column name="PARENT_NODE_ID" type="INTEGER"/>
<column name="NODE_NAME" size="100" type="VARCHAR"/>
<column name="NODE_TYPE" type="SMALLINT"/>
<column name="FULL_PATH" size="254" type="VARCHAR"/>
<column name="CREATION_DATE" type="TIMESTAMP"/>
<column name="MODIFIED_DATE" type="TIMESTAMP"/>
<foreign-key foreignTable="PREFS_NODE"
name="FK_PREFS_NODE_1" onDelete="cascade">
<reference foreign="NODE_ID" local="PARENT_NODE_ID"/>
</foreign-key>
<index name="IX_PREFS_NODE_1">
<index-column name="PARENT_NODE_ID"/>
</index>
<index name="IX_PREFS_NODE_2">
<index-column name="FULL_PATH"/>
</index>
</table>
<!--
prefs_property_value
-->
<table name="PREFS_PROPERTY_VALUE">
<column name="PROPERTY_VALUE_ID" primaryKey="true"
required="true" type="INTEGER"/>
<column name="NODE_ID" type="INTEGER"/>
<column name="PROPERTY_NAME" size="100" type="VARCHAR"/>
<column name="PROPERTY_VALUE" size="254" type="VARCHAR"/>
<column name="CREATION_DATE" type="TIMESTAMP"/>
<column name="MODIFIED_DATE" type="TIMESTAMP"/>
<!-- Still an issue with OJB 1.0.3 when deleting M-N.
Foreign Key Violation.
2007/12/13 - DST - I've been testing with this index
enabled on Oracle, MySQL
and experiencing no issues. I think the index on
should be the default case,
and inferior databases will have to turn it off as
part of the integration process
-->
<foreign-key foreignTable="PREFS_NODE" onDelete="cascade">
<reference foreign="NODE_ID" local="NODE_ID" />
</foreign-key>
<index name="IX_FKPPV_1">
<index-column name="NODE_ID"/>
</index>
</table>
</database>
Re: preferences performance tuning
Posted by Ate Douma <at...@douma.nu>.
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