Found it!
Line 1010:
Old: StringBuffer theSQL = new StringBuffer("delete from VERSION_PREDS
where VERSION_ID = (SELECT VERSION_ID FROM VERSION_HISTORY where URI_ID
= ");
New: StringBuffer theSQL = new StringBuffer("delete from VERSION_PREDS
where VERSION_ID IN (SELECT VERSION_ID FROM VERSION_HISTORY where URI_ID
= ");
I haven't tested through!
/Jacob
-----Original Message-----
From: Jacob Lund [mailto:[EMAIL PROTECTED]]
Sent: 12. juli 2002 09:19
To: [EMAIL PROTECTED]
Subject: RE: Modified Proposal for a more efficient Slide Database
Design
Hi Ashok!
I finally found the time to test you new database scheme - It really
improves the performance. I have tried it with your descriptoresstore
and file store for content!
If I enable automatic versioning then I get following error:
org.apache.slide.common.ServiceAccessException: Service
slidestore.j2ee.J2EEDesc riptorsStore2@3cc262 access error : Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subque ry is used as an
expression.
at
slidestore.j2ee.J2EEDescriptorsStore2.removeRevisionDescriptors(J2EED
escriptorsStore2.java:1016)
at
slidestore.j2ee.J2EEDescriptorsStore2.storeRevisionDescriptors(J2EEDe
scriptorsStore2.java:987)
at
org.apache.slide.store.AbstractStore.storeRevisionDescriptors(Abstrac
tStore.java:982)
at
org.apache.slide.store.StandardStore.storeRevisionDescriptors(Standar
dStore.java:523)
at
org.apache.slide.content.ContentImpl.create(ContentImpl.java:1291)
at
org.apache.slide.content.ContentImpl.create(ContentImpl.java:686)
at
org.apache.slide.webdav.util.VersioningHelper.versionControl(Versioni
ngHelper.java:334)
at
org.apache.slide.webdav.method.VersionControlMethod.executeRequest(Ve
rsionControlMethod.java:201)
at
org.apache.slide.webdav.method.AbstractWebdavMethod.run(AbstractWebda
vMethod.java:323)
at
org.apache.slide.webdav.WebdavServlet.service(WebdavServlet.java:221)
This is really nice work and I will try to get some more detailed
testing done!
Thanks
/Jacob
-----Original Message-----
From: Kumar, Ashok [mailto:[EMAIL PROTECTED]]
Sent: 2. maj 2002 23:24
To: Slide Users Mailing List
Cc: [EMAIL PROTECTED]
Subject: Modified Proposal for a more efficient Slide Database Design
Hi All
Thanks for testing the new proposed schema and for the fixes. But I am
curious to know the reason for converting all the statements to Prepared
Statements?. The reasons are outline below.
In the proposal version I was using normal statements rather than
prepared statments because, as u see there is STATEMENT creation and
subsequent STATEMENT close in every method. Infact this prepared
statements wont be fast in this scenario where u have this recreation on
every method invocation. In this set up I think the prepared statements
will be faster only if the database preparedstatement pooling is used
which would be database driver dependent.Infact JSQLConnect has an
option of enabling the prepared statment pooling while dbconnection is
established. It would be great to have your comments on this aspect
before going in for the changes.
Meanwhile I was doing some more testing based on the Slide TestSuite and
for the remarks reported by Jacob Lund and Michael Smith; which are now
fixed. There is a schema modification too.The schema submitted has
referential integrity set and we can take it off the schema, once we are
confident with the new schema to improve the performance. Please see the
attached J2EE sources and modified Schema.
The following are the test cases checked by me to cover the various
commands used, and the return code checks. My test configuration had 2
users and 4 iterations.
*/code/lock200.xml
*/code/lock423.xml
*/code/head200.xml
*/code/head404.xml
*/code/mkcol201.xml
*/code/mkcol405.xml
*/code/mkcol409.xml
*/code/get200.xml
*/code/get404.xml
*/code/delete204.xml
*/code/delete404.xml
*/code/delete423.xml
*/code/put201.xml
*/code/put409.xml
*/code/put423.xml
*/code/copy201.xml
*/code/copy204.xml
*/code/copy403.xml
*/code/copy409.xml
*/code/copy412.xml
*/code/copy423.xml
*/code/move201.xml
*/code/move204.xml
*/code/move403.xml
*/code/move412.xml
*/code/move423.xml
*/code/propfind207.xml
*/code/proppatch200.xml
*/code/proppatch207.xml
*/code/proppatch404.xml
Ashok
-----Original Message-----
From: Lionel Touati [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 02, 2002 4:20 AM
To: Slide Users Mailing List
Subject: RE: Proposal for a more efficient Slide Database Design
Hi Ashok,
I've done some changes to your classes, mainly using prepared statements
all along + some bug fixing. I've also ported the schema to DB2. Hope
you'll find theses files helpfull
Lionel
-----Message d'origine-----
De :
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
.org]De la part de Kumar, Ashok
Envoyé : Tuesday, April 23, 2002 2:35 PM
À : Slide Users Mailing List
Objet : RE: Proposal for a more efficient Slide Database Design
Hi
Thanks for testing the new schema. I am now in the process of doing some
more extensive testing using the Slide TestSuite and have already came
across this error. Previously I had only tested the mkcol, Put,Get and
Proppatch with my own testcase. As I am using the Slide TestSuite which
covers the lock, unlock, copy, move etc..we could have all the
conditions met. With in this week I will complete the testing with SLIDE
TESTSUITE and repost the revised proposal.
Thanks once again for the testing.
Ashok
-----Original Message-----
From: Jacob Lund [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 23, 2002 5:18 AM
To: 'Slide Users Mailing List'
Subject: RE: Proposal for a more efficient Slide Database Design
Hi,
I was testing you proposal and I am having some problems. My test is on
a ms sql 2000 server and a tomcat 4.0.3. My slide installation is 1_0
from the cvs.
I can see that you test system is tomcat 4.0.4 and newest slide from
cvs.
I am using newest version of jsqlconnect for jdbc connection.
The message I get is:
23 Apr 2002 11:04:23 - slidestore.j2ee.J2EEDescriptorsStore2 - ERROR -
com.jnetd
irect.jsql.u: INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK __PERMISSIO__OBJEC__0D44F85C'. The conflict occurred in
database 'slide', table 'URI', column 'URI_ID'.
com.jnetdirect.jsql.u: INSERT statement conflicted with COLUMN FOREIGN
KEY const raint 'FK__PERMISSIO__OBJEC__0D44F85C'. The conflict occurred
in database 'slide ', table 'URI', column 'URI_ID'.
What kind of constraints do you assume on the relationships (cascading
for update, Insert, delete)?
/Jacob
-----Original Message-----
From: Kumar, Ashok [mailto:[EMAIL PROTECTED]]
Sent: 12. april 2002 22:15
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Proposal for a more efficient Slide Database Design
Hi,
New Slide Database Store Proposal
---------------------------------
Here is a proposal for a more optimized database schema for the Slide
J2EE Database Store. The major changes to the current schema involve
adding numeric identifiers for URI's and other strings that were
previously used as keys. This allows for much faster lookups and the use
of foreign keys for referential integrity. Other changes include merging
and dropping some of the existing tables. All the existing slide
functionality was maintained.
Justification for the new schema
--------------------------------
Using the existing Slide Database store for storing and retrieving a
large number of documents is extremely inefficient. The primary reason
for this is the lack of indexing. Applying indexing to the existing
database schema definitely helps. However, due to the fact that the URI
field (being a large VARCHAR(800) datatype) is the main key linking most
of the tables, it makes a very inefficient index.
In addition, most databases have a limit on index size. In tables like
CHILDREN, where the unique index would be a combination of the URI and
CHILD_URI fields, the index size would exceed most database limits.
Finally, duplicating the data in large fields like URI in almost all of
the database tables is an inefficient waist of space and simple
normalization of this field provides a dramatic improvement in this
area.
The New Schema
--------------
Here is an overview of the schema changes:
- Three new tables were created to hold numeric key mappings for URI,
BRANCH and LABEL
- WORKING_REVISION -> removed
- LATEST_REVISIONS and REVISION -> VERSION_HISTORY
- REVISION_CONTENT -> VERSION_CONTENT
- REVISIONS -> VERSION
- BRANCHES -> VERSION_PREDS (predecessors)
- LABEL -> VERSION_LABELS
- OBJECT -> (same + URI key mapping)
- LOCKS -> (same)
- LINKS -> (same)
- CHILDREN -> (same)
- PERMISSIONS -> (same)
- PROPERTY -> (same)
Plus the addition of indexing and foreign key references.
The following schema will work with MS SQLServer and Sybase. (although
with Sybase the user defined datatypes need to be changed to map to
Sybase datatype limits) Other schema's for Oracle, DB2 and MySQL will
follow.
<<see attached file: new_slide_schema.zip>>
New Schema Implementation
------------------------
In order not to modify the existing database stores we have created
J2EEStore2. This store can be configured to point to a different
database instance that reflects the new schema.
Source files:
J2EEStore2.java( same as the J2EEStore.java with the addition of
hashtables for URI_ID lookup) J2EEDescriptorsStore2.java
J2EEContentStore2.java
<<see attached java source files : J2EEStore.zip>>
Tests Conducted
---------------
Configuration:
Latest version of slide (from CVS as of March 25) with J2EEStore2 Tomcat
4.04 MS SQLServer as the database store.
For the test we created a collection structure /rdf/testcase/test1 and
in this collection stored 1800 files each with a size of 313 KB.
>From the Browser (IE6) a request to view the contents of this
collection
(/rdf/testcase/test1) triggers a GET to this collection,
All Test Results are in Milliseconds. (by tracking Database Logs)
=================================================================
Operation OLD Schema New Schema
-----------------------------------------------------------------
Starting 1933 1559
Put(testcase/test1/xxfile) 2544 1578
Get(testcase) 2805 2103
Get(testcase/test1) 174616 98477
Conclusion
----------
We believe the this is a good start for a new database schema. The test
results already show a large improvement and we believe addition code
modifications will result in a much greater improvement.
In the process of redesigning the database we uncovered a number of
referential errors that were fixed by the new schema. For example,
problems we previously had with deadlocks when storing / updating very
large files are no longer an issue.
We are looking for feedback from this proposal.
Thank you...
Ashok
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
