9:23 a.m.
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 <=
: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 <=
: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 <=
: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 <= :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();