Greetings,
All issues I am describing below are related to J2EEDescriptorStore while using JDBC as presistence. Other performance related issues I have noticed in J2eeDescriptorStore is the liberal creation and use of PreperadStatements. Creating and using PreparedStatements once is counter-productive. If the need is to execute a statement only once then statements objects are significantly faster compared to prepared statements. This is mainly got to do with the overhead associated with preparing a statment i.e validating the metadata, executing the plan, creating and pinning the cursor to memory etc... Again creating and using prepared statements only once is clearly slower that using statement objects. Next I have also noticed some select * from <tablename> kind of queries, which are bad for the following reasons. "Select * " queries do not use any database indexes .... (Does the slide schema recommend any indexes???.. but in case in future..) 99% of the time "Select *" queries result in full table scans, which has many side affects in a databse server and make your JDBC act slower. "Select *" fetches the entire colum structure for every record and results in consumption of resources like network and memory. While Slide has a notion of a transaction and maintains its own representation of a transaction and its state, the underlying database has no idea that its participating in a transaction. Thus the databse is not protecting the data that is participating in a transaction, which has resulted in interesting results at our labs in HP. We have ran into deadlock problem with slide, espically J2EEDescriptorStore.storeObject() method when many users tried to access and modify the same resources concurrently. More on this later. But its important to make the database aware that its participating in a transaction and should it should protect the data. I appriciate efforts going on to implement more caching uisng Hashtables etc... But a word of caution, in an enterprise application, failover and loadbalancing are important requirements and this is achived by implementing multiple instance of an application. I hope in furute silde supports multiple instances and any cahcing effort design should be scalable to be able to synchronize object access accross multiple instance. I am sure implementing a Hashtable look up will solve the problem with an application with one instance of slide, but how will you scale if there are multiple instances of slide all servicing a common namespace? If deleting all the attributes of an object and its children and then reloading the enrite representation from memory into the database when attributes of a single child is modified, (See J2EEDescriptorStore.storeObject()), personally I think is a inefficient design. fixing this will reduce the comsumprion of JDBC resources. -Dhilip Krishnan. -----Original Message----- From: Jean-Philippe Courson [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 01, 2002 1:14 AM To: Slide Users Mailing List Subject: Re: Modified Proposal for a more efficient Slide Database Design Hi Ashok, I've successfully tested your new database scheme with PostgreSQL. I just had one problem : you forgot to enclose a string with '. Here is a patch : --- J2EEDescriptorsStore2.java.origin Thu Aug 1 09:56:34 2002 +++ J2EEDescriptorsStore2.java Thu Aug 1 09:55:59 2002 @@ -1159,7 +1159,7 @@ theSQL = new StringBuffer("insert into VERSION_HISTORY (URI_ID,BRANCH_ID,REVISION_NO) values("); theSQL.append(l_uri).append(", ").append(l_brid); - theSQL.append(", ").append(revisionDescriptor.getRevisionNumber().toString()).append(")"); + theSQL.append(", '").append(revisionDescriptor.getRevisionNumber().toString()).append("')"); statement.execute(theSQL.toString()); } long Version_ID = this.getVersionID(l_uri,revisionDescriptor.getBranchName(),revisionDescripto r.getRevisionNumber().toString()); I think that this new schema is a good starting point, but tables joins are the worst performance killer. So, when performances are the problem, my opinion is that they should be avoid even if it leads to data duplication. What is your opinion on this ? You will find next database creation script for PostgreSQL if it could help someone. Indexes on primary keys are automaticly created by PostgreSQL. Regards Jp DROP TABLE URI; DROP SEQUENCE NEXT_URI_ID; DROP TABLE OBJECT; DROP TABLE CHILDREN; DROP TABLE LINKS; DROP TABLE LOCKS; DROP TABLE BRANCH; DROP SEQUENCE NEXT_BRANCH_ID; DROP TABLE LABEL; DROP SEQUENCE NEXT_LABEL_ID; DROP TABLE VERSION; DROP TABLE VERSION_HISTORY; DROP SEQUENCE NEXT_VERSION_HISTORY_ID; DROP TABLE VERSION_PREDS; DROP TABLE VERSION_LABELS; DROP TABLE VERSION_CONTENT; DROP TABLE PROPERTIES; DROP TABLE PERMISSIONS; CREATE SEQUENCE NEXT_URI_ID; CREATE TABLE URI ( URI_ID BIGINT DEFAULT NEXTVAL('NEXT_URI_ID') PRIMARY KEY, URI_STRING VARCHAR(800) NOT NULL ); CREATE TABLE OBJECT ( URI_ID BIGINT PRIMARY KEY, CLASS_NAME VARCHAR(255) NOT NULL, FOREIGN KEY (URI_ID) REFERENCES URI ); CREATE TABLE CHILDREN ( URI_ID BIGINT NOT NULL REFERENCES URI, CHILD_URI_ID BIGINT NOT NULL REFERENCES URI (URI_ID), PRIMARY KEY (URI_ID, CHILD_URI_ID) ); CREATE TABLE LINKS ( URI_ID BIGINT NOT NULL REFERENCES URI, LINK_TO_ID BIGINT NOT NULL REFERENCES URI (URI_ID), PRIMARY KEY (URI_ID, LINK_TO_ID) ); CREATE TABLE LOCKS ( LOCK_ID BIGINT PRIMARY KEY, OBJECT_ID BIGINT NOT NULL REFERENCES URI (URI_ID), SUBJECT_ID BIGINT NOT NULL REFERENCES URI (URI_ID), TYPE_ID BIGINT NOT NULL REFERENCES URI (URI_ID), EXPIRATION_DATE TIMESTAMP NOT NULL, IS_INHERITABLE SMALLINT NOT NULL, IS_EXCLUSIVE SMALLINT NOT NULL, FOREIGN KEY (LOCK_ID) REFERENCES URI (URI_ID) ); CREATE SEQUENCE NEXT_BRANCH_ID; CREATE TABLE BRANCH ( BRANCH_ID BIGINT DEFAULT NEXTVAL('NEXT_BRANCH_ID') PRIMARY KEY, BRANCH_STRING VARCHAR(255) NOT NULL ); CREATE SEQUENCE NEXT_LABEL_ID; CREATE TABLE LABEL ( LABEL_ID BIGINT DEFAULT NEXTVAL('NEXT_LABEL_ID') PRIMARY KEY, LABEL_STRING VARCHAR(255) NOT NULL ); CREATE TABLE VERSION ( URI_ID BIGINT PRIMARY KEY, IS_VERSIONED SMALLINT NOT NULL, FOREIGN KEY (URI_ID) REFERENCES URI ); CREATE SEQUENCE NEXT_VERSION_HISTORY_ID; CREATE TABLE VERSION_HISTORY ( VERSION_ID BIGINT DEFAULT NEXTVAL('NEXT_VERSION_HISTORY_ID') PRIMARY KEY, URI_ID BIGINT NOT NULL REFERENCES VERSION, BRANCH_ID BIGINT NOT NULL REFERENCES BRANCH, REVISION_NO VARCHAR(20) NOT NULL ); CREATE INDEX VERSION_HISTORY_INDEX ON VERSION_HISTORY (URI_ID, BRANCH_ID, REVISION_NO); CREATE TABLE VERSION_PREDS ( VERSION_ID BIGINT NOT NULL REFERENCES VERSION_HISTORY, PREDECESSOR_ID BIGINT NOT NULL REFERENCES VERSION_HISTORY (VERSION_ID), PRIMARY KEY (VERSION_ID, PREDECESSOR_ID) ); CREATE TABLE VERSION_LABELS ( VERSION_ID BIGINT NOT NULL REFERENCES VERSION_HISTORY, LABEL_ID BIGINT NOT NULL REFERENCES LABEL, PRIMARY KEY (VERSION_ID, LABEL_ID) ); CREATE TABLE VERSION_CONTENT ( VERSION_ID BIGINT PRIMARY KEY, CONTENT OID NOT NULL, FOREIGN KEY (VERSION_ID) REFERENCES VERSION_HISTORY ); CREATE TABLE PROPERTIES ( VERSION_ID BIGINT NOT NULL REFERENCES VERSION_HISTORY, PROPERTY_NAMESPACE VARCHAR(50) NOT NULL, PROPERTY_NAME VARCHAR(50) NOT NULL, PROPERTY_VALUE VARCHAR(255) NOT NULL, PROPERTY_TYPE VARCHAR(50) NOT NULL, IS_PROTECTED SMALLINT NOT NULL, PRIMARY KEY (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME) ); CREATE TABLE PERMISSIONS ( OBJECT_ID BIGINT NOT NULL REFERENCES URI (URI_ID), SUBJECT_ID BIGINT NOT NULL REFERENCES URI (URI_ID), ACTION_ID BIGINT NOT NULL REFERENCES URI (URI_ID), VERSION_NO VARCHAR(20), IS_INHERITABLE SMALLINT NOT NULL, IS_NEGATIVE SMALLINT NOT NULL, PRIMARY KEY (OBJECT_ID, SUBJECT_ID, ACTION_ID) ); -- 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]>
