Author: christian.bauer(a)jboss.com Date: 2009-10-06 03:23:25 -0400 (Tue, 06 Oct 2009) New Revision: 11546 Added: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/WikiDocumentCountComment.java Modified: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/action/CommentHome.java branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/WikiNodeDAO.java branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/DatabaseObjects.hbm.xml branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/NativeQueries.hbm.xml branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml branches/community/Seam_2_2/examples/wiki/src/test/org/jboss/seam/wiki/test/editing/Commenting.java Log: Minor forum query performance optimization Modified: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/action/CommentHome.java =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/action/CommentHome.java 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/action/CommentHome.java 2009-10-06 07:23:25 UTC (rev 11546) @@ -145,7 +145,7 @@ } getLog().debug("updating last comment aggregation for: " + documentHome.getInstance()); - getWikiNodeDAO().updateWikiDocumentLastComment(documentHome.getInstance()); + getWikiNodeDAO().updateWikiDocumentComments(documentHome.getInstance()); getEntityManager().flush(); Events.instance().raiseEvent("Comment.persisted"); @@ -180,7 +180,7 @@ getEntityManager().clear(); getLog().debug("updating last comment aggregation for: " + documentHome.getInstance()); - getWikiNodeDAO().updateWikiDocumentLastComment(documentHome.getInstance()); + getWikiNodeDAO().updateWikiDocumentComments(documentHome.getInstance()); getEntityManager().flush(); getEntityManager().clear(); Modified: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/WikiNodeDAO.java =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/WikiNodeDAO.java 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/WikiNodeDAO.java 2009-10-06 07:23:25 UTC (rev 11546) @@ -670,7 +670,11 @@ nestedSetQuery.list(); // Append all children hierarchically to the transformers rootWrapper } - public void updateWikiDocumentLastComment(WikiDocument document) { + // TODO: This is not great + public void updateWikiDocumentComments(WikiDocument document) { + + // First, the denormalized "last comment" data duplication + // TODO: This probably is vulnerable to a race condition if we don't lock the whole WIKI_DOCUMENT_LAST_COMMENT table Long lastCommentId = (Long) @@ -699,6 +703,24 @@ existingLastCommentEntry.setLastCommentCreatedOn(lastComment.getCreatedOn()); restrictedEntityManager.persist(existingLastCommentEntry); } + + // Next, the denormalized "total comment count" data duplication + + Long commentCount = (Long) + getSession(true).getNamedQuery("countCommentOfDocument") + .setParameter("documentId", document.getId()) + .setComment("Counting comments of document: " + document.getId()) + .uniqueResult(); + + WikiDocumentCountComment existingCommentCount = + restrictedEntityManager.find(WikiDocumentCountComment.class, document.getId()); + if (existingCommentCount != null) { + existingCommentCount.setCommentCount(commentCount); + } else { + existingCommentCount = new WikiDocumentCountComment(); + existingCommentCount.setDocumentId(document.getId()); + restrictedEntityManager.persist(existingCommentCount); + } } private Session getSession(boolean restricted) { Modified: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/DatabaseObjects.hbm.xml =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/DatabaseObjects.hbm.xml 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/DatabaseObjects.hbm.xml 2009-10-06 07:23:25 UTC (rev 11546) @@ -54,6 +54,14 @@ <dialect-scope name="org.hibernate.dialect.HSQLDialect"/> </database-object> + <database-object> + <create> + alter table WIKI_DOCUMENT_COUNT_COMMENT add constraint FK_WIKI_DOCUMENT_COUNT_COMMENT_DOCUMENT_ID foreign key (WIKI_DOCUMENT_ID) references WIKI_DOCUMENT (NODE_ID) on delete cascade; + </create> + <drop/> + <dialect-scope name="org.hibernate.dialect.HSQLDialect"/> + </database-object> + <!-- ############################### MySQL ############################### --> @@ -112,6 +120,14 @@ <database-object> <create> + alter table WIKI_DOCUMENT_COUNT_COMMENT add index FK_WIKI_DOCUMENT_COUNT_COMMENT_DOCUMENT_ID (WIKI_DOCUMENT_ID), add constraint FK_WIKI_DOCUMENT_COUNT_COMMENT_DOCUMENT_ID foreign key (WIKI_DOCUMENT_ID) references WIKI_DOCUMENT (NODE_ID) on delete cascade; + </create> + <drop/> + <dialect-scope name="org.jboss.seam.wiki.util.WikiMySQL5HibernateDialect"/> + </database-object> + + <database-object> + <create> create index IDX_PREFERENCE_ENTITY_NAME on PREFERENCE (ENTITY_NAME) </create> <drop> Modified: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/NativeQueries.hbm.xml =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/NativeQueries.hbm.xml 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/NativeQueries.hbm.xml 2009-10-06 07:23:25 UTC (rev 11546) @@ -21,4 +21,16 @@ limit 1 </sql-query> + <sql-query name="countCommentOfDocument"> + <return-scalar column="COMMENT_COUNT" type="long"/> + select + count(distinct c0.NODE_ID) as COMMENT_COUNT + from + WIKI_NODE ct0 + inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID + inner join WIKI_COMMENT c0 on c0.NS_THREAD in (ct1.NS_THREAD) + where + ct0.PARENT_NODE_ID = :documentId + </sql-query> + </hibernate-mapping> \ No newline at end of file Copied: branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/WikiDocumentCountComment.java (from rev 11534, branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/WikiDocumentLastComment.java) =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/WikiDocumentCountComment.java (rev 0) +++ branches/community/Seam_2_2/examples/wiki/src/main/org/jboss/seam/wiki/core/model/WikiDocumentCountComment.java 2009-10-06 07:23:25 UTC (rev 11546) @@ -0,0 +1,42 @@ +package org.jboss.seam.wiki.core.model; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.Table; + +/** + * An ugly denormalization and duplication of data, so aggregation queries + * can execute faster on nested set trees. + * + * @author Christian Bauer + */ +@Entity +@Table(name = "WIKI_DOCUMENT_COUNT_COMMENT") +public class WikiDocumentCountComment { + + @Id + @Column(name = "WIKI_DOCUMENT_ID", nullable = false) + private Long documentId; + + @Column(name = "COMMENT_COUNT") + protected Long commentCount; + + public WikiDocumentCountComment() {} + + public Long getDocumentId() { + return documentId; + } + + public void setDocumentId(Long documentId) { + this.documentId = documentId; + } + + public Long getCommentCount() { + return commentCount; + } + + public void setCommentCount(Long commentCount) { + this.commentCount = commentCount; + } +} \ No newline at end of file Modified: branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2009-10-06 07:23:25 UTC (rev 11546) @@ -135,38 +135,18 @@ ) .list(); - // Find topic count (topics are just wiki documents in the forum directories) - getSession(true).getNamedQuery("forumTopicCount") - .setParameter("parentDir", forumsDirectory) - .setComment("Finding topic count for all forums") - .setResultTransformer( - new ResultTransformer() { - public Object transformTuple(Object[] result, String[] strings) { - if (forumInfoMap.containsKey((Long)result[0])) { - ForumInfo info = forumInfoMap.get( (Long)result[0] ); - info.setTotalNumOfTopics((Long)result[1]); - info.setTotalNumOfPosts(info.getTotalNumOfTopics()); - } - return null; - } - public List transformList(List list) { return list; } - } - ) - .list(); - - // Add reply count to topic count to get total num of posts - getSession(true).getNamedQuery("forumReplyCount") + // Find topic and replies count (topics are just wiki documents in the forum directories) + getSession(true).getNamedQuery("forumTopicReplyCount") .setParameter("parentDirId", forumsDirectory.getId()) .setParameter("readAccessLevel", currentAccessLevel) - .setComment("Finding reply count for all forums") + .setComment("Finding topic and replies count for all forums") .setResultTransformer( new ResultTransformer() { public Object transformTuple(Object[] result, String[] strings) { if (forumInfoMap.containsKey((Long)result[0])) { ForumInfo info = forumInfoMap.get( (Long)result[0] ); - info.setTotalNumOfPosts( - info.getTotalNumOfPosts() + (Long)result[1] - ); + info.setTotalNumOfTopics((Long)result[1]); + info.setTotalNumOfPosts(info.getTotalNumOfTopics() + (Long)result[2]); } return null; } Modified: branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2009-10-06 07:23:25 UTC (rev 11546) @@ -76,36 +76,25 @@ order by m.displayPosition asc </query> - <query name="forumTopicCount"> - select - f.id, count(distinct t) - from - WikiDirectory f, WikiDocument t - where - f.parent = :parentDir - and t.parent = f - and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString like '%forumStickyPosting%') - group - by f.id - </query> - - <sql-query name="forumReplyCount"> + <sql-query name="forumTopicReplyCount"> <return-scalar column="FORUM_ID" type="long"/> + <return-scalar column="TOPIC_COUNT" type="long"/> <return-scalar column="REPLY_COUNT" type="long"/> select - dir0.NODE_ID as FORUM_ID, count(distinct c0.NODE_ID) as REPLY_COUNT + dir0.NODE_ID as FORUM_ID, + count(doc0.NODE_ID) as TOPIC_COUNT, + sum(dc.COMMENT_COUNT) as REPLY_COUNT from - WIKI_DIRECTORY dir0 - inner join WIKI_NODE dir1 on dir0.NODE_ID=dir1.NODE_ID - and dir1.READ_ACCESS_LEVEL &lt;= :readAccessLevel and dir1.PARENT_NODE_ID = :parentDirId - inner join WIKI_NODE doc1 on doc1.PARENT_NODE_ID = dir0.NODE_ID - and doc1.READ_ACCESS_LEVEL &lt;= :readAccessLevel - inner join WIKI_DOCUMENT doc0 on doc0.NODE_ID=doc1.NODE_ID - and (doc0.HEADER_MACROS like '%forumPosting%' or doc0.HEADER_MACROS like '%forumStickyPosting%') - inner join WIKI_NODE ct0 on ct0.PARENT_NODE_ID = doc0.NODE_ID - inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID - inner join WIKI_COMMENT c0 on c0.NS_THREAD in (ct1.NS_THREAD) - group by dir0.NODE_ID + WIKI_NODE dir0 + inner join WIKI_NODE doc1 on doc1.PARENT_NODE_ID = dir0.NODE_ID + and doc1.READ_ACCESS_LEVEL &lt;= :readAccessLevel + inner join WIKI_DOCUMENT doc0 on doc0.NODE_ID=doc1.NODE_ID + and (doc0.HEADER_MACROS like '%forumPosting%' or doc0.HEADER_MACROS like '%forumStickyPosting%') + left outer join WIKI_DOCUMENT_COUNT_COMMENT dc on dc.WIKI_DOCUMENT_ID = doc0.NODE_ID + where + dir0.READ_ACCESS_LEVEL &lt;= :readAccessLevel and dir0.PARENT_NODE_ID = :parentDirId + group by + dir0.NODE_ID </sql-query> <sql-query name="forumLastTopic"> Modified: branches/community/Seam_2_2/examples/wiki/src/test/org/jboss/seam/wiki/test/editing/Commenting.java =================================================================== --- branches/community/Seam_2_2/examples/wiki/src/test/org/jboss/seam/wiki/test/editing/Commenting.java 2009-10-05 12:38:07 UTC (rev 11545) +++ branches/community/Seam_2_2/examples/wiki/src/test/org/jboss/seam/wiki/test/editing/Commenting.java 2009-10-06 07:23:25 UTC (rev 11546) @@ -7,12 +7,13 @@ package org.jboss.seam.wiki.test.editing; import org.dbunit.operation.DatabaseOperation; +import org.jboss.seam.mock.DBUnitSeamTest; import org.jboss.seam.wiki.core.action.CommentHome; import org.jboss.seam.wiki.core.action.CommentQuery; -import org.jboss.seam.wiki.core.model.*; import org.jboss.seam.wiki.core.dao.WikiNodeDAO; -import org.jboss.seam.mock.DBUnitSeamTest; -import org.jboss.seam.contexts.Contexts; +import org.jboss.seam.wiki.core.model.User; +import org.jboss.seam.wiki.core.model.WikiDocumentCountComment; +import org.jboss.seam.wiki.core.model.WikiDocumentLastComment; import org.testng.annotations.Test; import javax.persistence.EntityManager; @@ -78,6 +79,10 @@ WikiDocumentLastComment lastComment = em.find(WikiDocumentLastComment.class, 6l); assert lastComment != null; assert lastComment.getLastCommentId().equals(commentQuery.getComments().get(6).getId()); + + WikiDocumentCountComment countComment = em.find(WikiDocumentCountComment.class, 6l); + assert countComment != null; + assert countComment.getCommentCount() == 8; } }.run(); @@ -138,6 +143,10 @@ WikiDocumentLastComment lastComment = em.find(WikiDocumentLastComment.class, 6l); assert lastComment != null; assert lastComment.getLastCommentId().equals(commentQuery.getComments().get(6).getId()); + + WikiDocumentCountComment countComment = em.find(WikiDocumentCountComment.class, 6l); + assert countComment != null; + assert countComment.getCommentCount() == 7; } }.run(); @@ -179,6 +188,9 @@ assert lastComment != null; assert lastComment.getLastCommentId().equals(13l); + WikiDocumentCountComment countComment = em.find(WikiDocumentCountComment.class, 6l); + assert countComment != null; + assert countComment.getCommentCount() == 4; } }.run();