9:54 p.m.
Author: christian.bauer(a)jboss.com
Date: 2009-02-14 22:54:51 -0500 (Sat, 14 Feb 2009)
New Revision: 10045
Modified:
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/TopicInfo.java
Log:
Minor forum query performance optimizations
Modified: trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java
===================================================================
---
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2009-02-15
03:54:15 UTC (rev 10044)
+++
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2009-02-15
03:54:51 UTC (rev 10045)
@@ -5,6 +5,7 @@
import org.jboss.seam.annotations.AutoCreate;
import org.jboss.seam.annotations.Scope;
import org.jboss.seam.wiki.core.model.*;
+import org.jboss.seam.wiki.core.dao.WikiNodeDAO;
import org.jboss.seam.ScopeType;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
@@ -24,6 +25,9 @@
EntityManager restrictedEntityManager;
@In
+ WikiNodeDAO wikiNodeDAO;
+
+ @In
Integer currentAccessLevel;
public List<WikiMenuItem> findForumsMenuItems(WikiDirectory forumsDirectory) {
@@ -103,27 +107,26 @@
)
.list();
- // Append last topic WikiDocument
- getSession(true).getNamedQuery("forumLastTopic")
- .setParameter("parentDir", forumsDirectory)
+ // Append last topic WikiDocument (faster if we do it with a MySQL specific LIMIT
subselect)
+ List<Object[]> forumsAndLastTopics =
getSession(true).getNamedQuery("forumLastTopic")
+ .setParameter("parentDirId", forumsDirectory.getId())
+ .setParameter("readAccessLevel", currentAccessLevel)
.setComment("Finding last topics for all forums")
- .setResultTransformer(
- new ResultTransformer() {
- public Object transformTuple(Object[] result, String[] strings) {
- if (forumInfoMap.containsKey((Long)result[0]))
- forumInfoMap.get( (Long)result[0] ).setLastTopic(
(WikiDocument)result[1] );
- return null;
- }
- public List transformList(List list) { return list; }
- }
- )
+ .setCacheable(true)
.list();
+ for (Object[] lastTopicRow : forumsAndLastTopics) {
+ if (forumInfoMap.containsKey((Long)lastTopicRow[0])) {
+ WikiDocument lastTopic = wikiNodeDAO.findWikiDocument(
(Long)lastTopicRow[1] );
+ forumInfoMap.get( (Long)lastTopicRow[0] ).setLastTopic( lastTopic );
+ }
+ }
// Append last reply WikiComment
getSession(true).getNamedQuery("forumLastReply")
.setParameter("parentDirId", forumsDirectory.getId())
.setParameter("readAccessLevel", currentAccessLevel)
.setComment("Finding last replies for all forums")
+ .setCacheable(true)
.setResultTransformer(
new ResultTransformer() {
public Object transformTuple(Object[] result, String[] strings) {
@@ -196,32 +199,58 @@
}
public Map<Long, TopicInfo> findTopics(WikiDirectory forum, long firstResult,
long maxResults) {
+
+ // Limited list of topics, first retrieve identifiers only (faster on ORDER
BY/LIMIT) then
+ // batch select the topic instances, then batch select the reply instances, we
collect all
+ // of this stuff in this map:
final Map<Long, TopicInfo> topicInfoMap = new LinkedHashMap<Long,
TopicInfo>();
- getSession(true).getNamedQuery("forumTopics")
+ // Retrieve topic identifier, sticky? and hasReplies? data
+ getSession(true).getNamedQuery("forumTopicsList")
.setParameter("parentNodeId", forum.getId())
.setParameter("readAccessLevel", currentAccessLevel)
- .setComment("Retrieving forum topics")
+ .setComment("Retrieving forum topics list")
.setFirstResult(new Long(firstResult).intValue())
.setMaxResults(new Long(maxResults).intValue())
.setResultTransformer(
- new ResultTransformer() {
- public Object transformTuple(Object[] result, String[] strings) {
- topicInfoMap.put(
- ((WikiDocument)result[0]).getId(),
- new TopicInfo( (WikiDocument)result[0], (Integer)result[1],
(Boolean)result[2])
- );
- return null;
+ new ResultTransformer() {
+ public Object transformTuple(Object[] result, String[] strings)
{
+ Long topicId = (Long) result[0];
+ Integer sticky = (Integer)result[1];
+ Boolean hasReplies = (Boolean)result[2];
+ topicInfoMap.put(topicId, new TopicInfo(sticky,
hasReplies));
+ return null;
+ }
+ public List transformList(List list) { return list; }
}
- public List transformList(List list) { return list; }
- }
)
.list();
+ if (topicInfoMap.keySet().size() == 0) return topicInfoMap; // Early exist
possible
+
+ // Retrieve the topic entity instances and shove them into the map
+ getSession(true).getNamedQuery("forumTopics")
+ .setParameterList("topicIds", topicInfoMap.keySet())
+ .setComment("Retrieving forum topic list instances")
+ .setResultTransformer(
+ new ResultTransformer() {
+ public Object transformTuple(Object[] result, String[] strings)
{
+ WikiDocument topicInstance = (WikiDocument)result[0];
+
topicInfoMap.get(topicInstance.getId()).setTopic(topicInstance);
+ return null;
+ }
+ public List transformList(List list) { return list; }
+ }
+ )
+ .list();
+
+
+ // Figure out which and if we even should query the reply instances
List<Long> topicIdsWithReplies = new ArrayList<Long>();
for (Map.Entry<Long, TopicInfo> entry : topicInfoMap.entrySet()) {
if (entry.getValue().isReplies()) topicIdsWithReplies.add(entry.getKey());
}
+
if (topicIdsWithReplies.size() == 0) return topicInfoMap; // Early exit possible
getSession(true).getNamedQuery("forumTopicsReplies")
Modified:
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml
===================================================================
---
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2009-02-15
03:54:15 UTC (rev 10044)
+++
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2009-02-15
03:54:51 UTC (rev 10045)
@@ -3,6 +3,13 @@
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
+<!--
+ Most of these queries are optimized for best execution plan and execution time. In
some cases it might
+ not be obvious why a query is structured as it is, but that's the secret of MySQL
tuning-by-accident!
+ Some queries are slow for no reason and can not be further optimized - MySQL is a bad
DBMS that struggles
+ with a few nested joins on indexed columns only.
+-->
+
<hibernate-mapping>
<query name="forumsCount">
@@ -68,24 +75,34 @@
group by dir0.NODE_ID
</sql-query>
- <!-- TODO: This is based on the idea that the last topic is "one in a
second", we should use a subselect with limit 1 order by -->
- <query name="forumLastTopic">
+ <sql-query name="forumLastTopic">
+ <return-scalar column="FORUM_ID" type="long"/>
+ <return-scalar column="LAST_TOPIC_ID" type="long"/>
select
- f.id, t
+ dir0.NODE_ID as FORUM_ID, doc0.NODE_ID as LAST_TOPIC_ID
from
- WikiDirectory f, WikiDocument t left join fetch t.parent
- where
- f.parent = :parentDir
- and t.parent = f
- and (t.headerMacrosString like '%forumPosting%' or
t.headerMacrosString like '%forumStickyPosting%')
- and t.createdOn = (select max(t2.createdOn) from WikiDocument t2 where t2 =
t)
- </query>
+ 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 doc0 on doc0.NODE_ID =
+ (
+ select
+ dlast0.NODE_ID
+ from
+ WIKI_NODE dlast0
+ inner join WIKI_DOCUMENT dlast1 on dlast0.NODE_ID =
dlast1.NODE_ID
+ and (dlast1.HEADER_MACROS like '%forumPosting%'
or dlast1.HEADER_MACROS like '%forumStickyPosting%')
+ where
+ dlast0.PARENT_NODE_ID = dir0.NODE_ID and
dlast0.READ_ACCESS_LEVEL <= :readAccessLevel
+ order by dlast0.CREATED_ON desc limit 1
+ )
+ group by dir0.NODE_ID
+ </sql-query>
<sql-query name="forumLastReply">
<return-scalar column="FORUM_ID" type="long"/>
<return class="org.jboss.seam.wiki.core.model.WikiComment"/>
select
- dir1.NODE_ID as FORUM_ID,
+ dir0.NODE_ID as FORUM_ID,
c1.NODE_ID as NODE_ID,
c1.OBJ_VERSION as OBJ_VERSION,
@@ -112,30 +129,29 @@
c0.NS_RIGHT as NS_RIGHT,
c0.NS_THREAD as NS_THREAD
from
- WIKI_DIRECTORY dir0
- inner join WIKI_NODE dir1
- on dir0.NODE_ID = dir1.NODE_ID
- and dir1.PARENT_NODE_ID = :parentDirId
- and dir1.READ_ACCESS_LEVEL <= :readAccessLevel,
- WIKI_COMMENT c0
- inner join WIKI_NODE c1 on c0.NODE_ID = c1.NODE_ID
+ WIKI_NODE dir0,
+ WIKI_NODE c1 inner join WIKI_COMMENT c0 on c1.NODE_ID = c0.NODE_ID
where
- c0.NODE_ID = (
- select ct3.NODE_ID
- from WIKI_COMMENT ct
- inner join WIKI_NODE ct1 on ct.NODE_ID = ct1.NODE_ID
- inner join WIKI_COMMENT ct2 on ct2.NS_THREAD in
(ct.NS_THREAD)
- inner join WIKI_NODE ct3 on ct2.NODE_ID = ct3.NODE_ID,
- WIKI_NODE doc0 inner join WIKI_DOCUMENT doc1
- on doc0.NODE_ID = doc1.NODE_ID
- and (doc1.HEADER_MACROS like '%forumPosting%' or
doc1.HEADER_MACROS like '%forumStickyPosting%')
+ dir0.PARENT_NODE_ID = :parentDirId
+ and
+ dir0.READ_ACCESS_LEVEL <= :readAccessLevel
+ and
+ c1.CREATED_ON = (
+ select max(ct3.CREATED_ON)
+ from
+ WIKI_NODE doc0
+ inner join WIKI_DOCUMENT doc1 on doc0.NODE_ID = doc1.NODE_ID
+ and (doc1.HEADER_MACROS like
'%forumPosting%' or doc1.HEADER_MACROS like '%forumStickyPosting%')
+ inner join WIKI_NODE ct0 on doc1.NODE_ID =
ct0.PARENT_NODE_ID
+ inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID
+ inner join WIKI_COMMENT ct2 on ct2.NS_THREAD in
(ct1.NS_THREAD)
+ inner join WIKI_NODE ct3 on ct2.NODE_ID = ct3.NODE_ID
where
doc0.PARENT_NODE_ID= dir0.NODE_ID
- and doc0.READ_ACCESS_LEVEL <= :readAccessLevel
- and ct1.PARENT_NODE_ID = doc0.NODE_ID
- order by ct3.CREATED_ON desc
- limit 1
+ and
+ doc0.READ_ACCESS_LEVEL <= :readAccessLevel
)
+
</sql-query>
<query name="forumUnreadTopics"><![CDATA[
@@ -217,64 +233,78 @@
and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString
like '%forumStickyPosting%')
</query>
- <!-- TODO: We could optimize this a little if we'd also retrieve the
CREATED_BY_USER_ID guy but
- we'll hit the 2nd level cache anyway or we load them in batches...
-->
- <sql-query name="forumTopics">
- <return class="org.jboss.seam.wiki.core.model.WikiDocument"/>
+ <sql-query name="forumTopicsList">
+ <return-scalar column="NODE_ID" type="long"/>
<return-scalar column="STICKY" type="integer"/>
<return-scalar column="HAS_REPLIES" type="boolean"/>
<return-scalar column="LAST_POST" type="timestamp"/>
<![CDATA[
select distinct
- doc2.NODE_ID as NODE_ID,
- doc2.OBJ_VERSION as OBJ_VERSION,
- doc2.RATING as RATING,
- doc2.AREA_NR as AREA_NR,
- doc2.NAME as NAME,
- doc2.WIKINAME as WIKINAME,
- doc2.MESSAGE_ID as MESSAGE_ID,
- doc2.CREATED_ON as CREATED_ON,
- doc2.CREATED_BY_USER_ID as CREATED_BY_USER_ID,
- doc2.LAST_MODIFIED_ON as LAST_MODIFIED_ON,
- doc2.LAST_MODIFIED_BY_USER_ID as LAST_MODIFIED_BY_USER_ID,
- doc2.WRITE_ACCESS_LEVEL as WRITE_ACCESS_LEVEL,
- doc2.READ_ACCESS_LEVEL as READ_ACCESS_LEVEL,
- doc2.WRITE_PROTECTED as WRITE_PROTECTED,
- doc2.PARENT_NODE_ID as PARENT_NODE_ID,
-
- doc1.FILE_REVISION as FILE_REVISION,
-
- doc0.NAME_AS_TITLE as NAME_AS_TITLE,
- doc0.ENABLE_COMMENTS as ENABLE_COMMENTS,
- doc0.ENABLE_COMMENT_FORM as ENABLE_COMMENT_FORM,
- doc0.ENABLE_COMMENTS_ON_FEEDS as ENABLE_COMMENTS_ON_FEEDS,
- doc0.HEADER as HEADER,
- doc0.HEADER_MACROS as HEADER_MACROS,
- doc0.CONTENT_MACROS as CONTENT_MACROS,
- doc0.FOOTER as FOOTER,
- doc0.FOOTER_MACROS as FOOTER_MACROS,
-
- case when (doc0.HEADER_MACROS like '%forumStickyPosting%') then 1
else 0 end as STICKY,
+ d0.NODE_ID as NODE_ID,
+ case when (d2.HEADER_MACROS like '%forumStickyPosting%') then 1 else
0 end as STICKY,
case when c0.CREATED_ON is null then false else true end as HAS_REPLIES,
- case when c0.CREATED_ON is null then doc2.CREATED_ON else c0.CREATED_ON end
as LAST_POST
-
+ case when c0.CREATED_ON is null then d0.CREATED_ON else c0.CREATED_ON end as
LAST_POST
from
- WIKI_DOCUMENT doc0 inner join WIKI_FILE doc1 on doc0.NODE_ID=doc1.NODE_ID
inner join WIKI_NODE doc2 on doc0.NODE_ID=doc2.NODE_ID
- left outer join WIKI_NODE c0
- on c0.NODE_ID = (
- select ct3.NODE_ID from WIKI_COMMENT ct
+ WIKI_NODE d0
+ inner join WIKI_DOCUMENT d2 on d0.NODE_ID = d2.NODE_ID and
(d2.HEADER_MACROS like '%forumPosting%' or d2.HEADER_MACROS like
'%forumStickyPosting%')
+ left outer join WIKI_NODE c0
+ on c0.CREATED_ON = (
+ select max(ct3.CREATED_ON) from WIKI_COMMENT ct
inner join WIKI_NODE ct1 on ct.NODE_ID = ct1.NODE_ID
inner join WIKI_COMMENT ct2 on ct2.NS_THREAD in (ct.NS_THREAD)
inner join WIKI_NODE ct3 on ct2.NODE_ID = ct3.NODE_ID
- where ct1.PARENT_NODE_ID = doc0.NODE_ID order by ct3.CREATED_ON desc
- limit 1
+ where ct1.PARENT_NODE_ID = d2.NODE_ID
)
where
- doc2.PARENT_NODE_ID = :parentNodeId
- and doc2.READ_ACCESS_LEVEL <= :readAccessLevel
- and (doc0.HEADER_MACROS like '%forumPosting%' or doc0.HEADER_MACROS
like '%forumStickyPosting%')
+ d0.READ_ACCESS_LEVEL <= :readAccessLevel and d0.PARENT_NODE_ID =
:parentNodeId
order
by STICKY desc, LAST_POST desc
+ ]]>
+ </sql-query>
+
+ <sql-query name="forumTopics">
+ <return alias="d"
class="org.jboss.seam.wiki.core.model.WikiDocument"/>
+ <return-join alias="u" property="d.createdBy"/>
+ <![CDATA[
+ select
+ d0.NODE_ID as NODE_ID,
+ d0.OBJ_VERSION as OBJ_VERSION,
+ d0.RATING as RATING,
+ d0.AREA_NR as AREA_NR,
+ d0.NAME as NAME,
+ d0.WIKINAME as WIKINAME,
+ d0.MESSAGE_ID as MESSAGE_ID,
+ d0.CREATED_ON as CREATED_ON,
+ d0.CREATED_BY_USER_ID as CREATED_BY_USER_ID,
+ d0.LAST_MODIFIED_ON as LAST_MODIFIED_ON,
+ d0.LAST_MODIFIED_BY_USER_ID as LAST_MODIFIED_BY_USER_ID,
+ d0.WRITE_ACCESS_LEVEL as WRITE_ACCESS_LEVEL,
+ d0.READ_ACCESS_LEVEL as READ_ACCESS_LEVEL,
+ d0.WRITE_PROTECTED as WRITE_PROTECTED,
+ d0.PARENT_NODE_ID as PARENT_NODE_ID,
+
+ d1.FILE_REVISION as FILE_REVISION,
+
+ d2.NAME_AS_TITLE as NAME_AS_TITLE,
+ d2.ENABLE_COMMENTS as ENABLE_COMMENTS,
+ d2.ENABLE_COMMENT_FORM as ENABLE_COMMENT_FORM,
+ d2.ENABLE_COMMENTS_ON_FEEDS as ENABLE_COMMENTS_ON_FEEDS,
+ d2.HEADER as HEADER,
+ d2.HEADER_MACROS as HEADER_MACROS,
+ d2.CONTENT_MACROS as CONTENT_MACROS,
+ d2.FOOTER as FOOTER,
+ d2.FOOTER_MACROS as FOOTER_MACROS,
+
+ u.USER_ID, u.ACTIVATED, u.ACTIVATION_CODE, u.CREATED_ON, u.EMAIL,
u.FIRSTNAME, u.LAST_LOGIN_ON,
+ u.LASTNAME, u.MEMBER_HOME_WIKI_DIRECTORY_ID, u.PASSWORDHASH,
u.USER_PROFILE_ID, u.USERNAME, u.OBJ_VERSION
+
+ from
+ WIKI_NODE d0
+ inner join USERS u on d0.CREATED_BY_USER_ID = u.USER_ID
+ inner join WIKI_FILE d1 on d0.NODE_ID=d1.NODE_ID
+ inner join WIKI_DOCUMENT d2 on d1.NODE_ID=d2.NODE_ID
+ where
+ d0.NODE_ID in (:topicIds)
]]></sql-query>
<sql-query name="forumTopicsReplies">
Modified: trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/TopicInfo.java
===================================================================
---
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/TopicInfo.java 2009-02-15
03:54:15 UTC (rev 10044)
+++
trunk/examples/wiki/src/plugin/org/jboss/seam/wiki/plugin/forum/TopicInfo.java 2009-02-15
03:54:51 UTC (rev 10045)
@@ -12,12 +12,15 @@
private long numOfReplies;
private WikiComment lastComment;
- public TopicInfo(WikiDocument topic, Integer sticky, Boolean replies) {
- this.topic = topic;
+ public TopicInfo(Integer sticky, boolean replies) {
this.sticky = sticky != 0;
this.replies = replies;
}
+ public void setTopic(WikiDocument topic) {
+ this.topic = topic;
+ }
+
public WikiDocument getTopic() {
return topic;
}