One more thing - what jdbc driver are you using? /Jacob
-----Original Message----- From: Kumar, Ashok [mailto:[EMAIL PROTECTED]] Sent: 16. juli 2002 20:04 To: Slide Users Mailing List Subject: RE: Modified Proposal for a more efficient Slide Database Design Hi We have it working with both TC404b and TC404 release. The one major difference I could spot is we have the versioning turned off. It may be the case that when versioning is on the content store defaults to file based store.. let me know... Ashok -----Original Message----- From: Jacob Lund [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 2:19 AM To: 'Slide Users Mailing List' Subject: RE: Modified Proposal for a more efficient Slide Database Design Hmm - I tried with tc 4.0.4 - same result? What version of slide are you using? /Jacob -----Original Message----- From: Jacob Lund [mailto:[EMAIL PROTECTED]] Sent: 15. juli 2002 15:09 To: 'Slide Users Mailing List' Subject: RE: Modified Proposal for a more efficient Slide Database Design Hi Again! Here is my domain.xml - I have also attached my log file. As you can see it throws an exception but the server run fine and stores all descriptorsstore data correctly in the database! The content are stored as a file contentstore running with versioning=true. To verify that it reads this domain.xml file I did try to misspell something at it did stop the server with an exception? This is really strange! BTW I am using latest JSQLConnect jdbc driver! Thanks /Jacob -----Original Message----- From: Kumar, Ashok [mailto:[EMAIL PROTECTED]] Sent: 15. juli 2002 14:22 To: Slide Users Mailing List Subject: RE: Modified Proposal for a more efficient Slide Database Design Hi Jacob I have not seen this behaviour of slide so far. But I am still using the TC4.0.4. . Therefore i am not sure whether it is related to the TC417 version. Can i have alook at the domain.xml u are using ?? Ashok -----Original Message----- From: Jacob Lund [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 5:35 AM To: 'Slide Users Mailing List' Subject: RE: Modified Proposal for a more efficient Slide Database Design Hi Ashok! I have been testing the j2ee descriptors store with filestore and now I wanted to test the j2ee contentstore too. I have a small problem though - I have configured my slide for using the j2ee descriptors store and j2ee content store and the log file tells me that the content store is slidestore.j2ee.J2EEContetStore2 but it saves the files on the harddrive?? It seems like it ignores the setup at defaults to file content store? Have you seen this before? I am running on tomcat 4.1.7 /Jacob -----Original Message----- From: Kumar, Ashok [mailto:[EMAIL PROTECTED]] Sent: 12. juli 2002 22:47 To: Slide Users Mailing List Cc: [EMAIL PROTECTED] Subject: RE: Modified Proposal for a more efficient Slide Database Design Hi Jacob Good to know that someone other than me is showing interest for a new and better database design. I have done some more testing and attached is the latest set of sources. I have also incorporated the sql changes pointed out by U. Also attached is a utility java file for file content compression. I think this is a nice feature to have(both for File contentstore(not done) or J2EECOntentStore(done)). Considering from the point of storage for file contents in database, his could save a lot of time as the retrieval will be effcient for smaller blobs. The related code can be found in the J2EEContentStore2 and is controlled by a domain.xml parameter "compress". Please do some more testing by which we should be able to commit it to Slide. Thanks Once again for testing. Ashok Metatomix INC,. -----Original Message----- From: Jacob Lund [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 4:15 AM To: 'Slide Users Mailing List' Subject: RE: Modified Proposal for a more efficient Slide Database Design 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]> -- 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]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
