Author: christian.bauer(a)jboss.com Date: 2008-02-16 10:30:10 -0500 (Sat, 16 Feb 2008) New Revision: 7430 Modified: trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumDAOTests.java trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumQueryTests.java trunk/examples/wiki/view/plugins/tags/plugin.xhtml Log: Optimized execution plan of forum list SQL queries Modified: trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java =================================================================== --- trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2008-02-15 17:48:01 UTC (rev 7429) +++ trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2008-02-16 15:30:10 UTC (rev 7430) @@ -22,6 +22,9 @@ @In EntityManager restrictedEntityManager; + @In + Integer currentAccessLevel; + public List<WikiMenuItem> findForumsMenuItems(WikiDirectory forumsDirectory) { return getSession(true).getNamedQuery("forumsMenuItems") .setParameter("parentDir", forumsDirectory) @@ -70,7 +73,8 @@ // Add reply count to topic count to get total num of posts getSession(true).getNamedQuery("forumReplyCount") - .setParameter("parentDir", forumsDirectory) + .setParameter("parentDirId", forumsDirectory.getId()) + .setParameter("readAccessLevel", currentAccessLevel) .setComment("Finding reply count for all forums") .setCacheable(true) .setResultTransformer( @@ -107,7 +111,8 @@ // Append last reply WikiComment getSession(true).getNamedQuery("forumLastReply") - .setParameter("parentDir", forumsDirectory) + .setParameter("parentDirId", forumsDirectory.getId()) + .setParameter("readAccessLevel", currentAccessLevel) .setComment("Finding last replies for all forums") .setResultTransformer( new ResultTransformer() { @@ -184,6 +189,7 @@ getSession(true).getNamedQuery("forumTopics") .setParameter("parentNodeId", forum.getId()) + .setParameter("readAccessLevel", currentAccessLevel) .setComment("Retrieving forum topics") .setFirstResult(new Long(firstResult).intValue()) .setMaxResults(new Long(maxResults).intValue()) Modified: trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml =================================================================== --- trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2008-02-15 17:48:01 UTC (rev 7429) +++ trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2008-02-16 15:30:10 UTC (rev 7430) @@ -38,25 +38,30 @@ by f.id </query> - <query name="forumReplyCount"> + <sql-query name="forumReplyCount"> + <return-scalar column="FORUM_ID" type="long"/> + <return-scalar column="REPLY_COUNT" type="long"/> select - f.id, count(distinct c) + dir0.NODE_ID as FORUM_ID, count(distinct c0.NODE_ID) as REPLY_COUNT from - WikiDirectory f, WikiDocument t, WikiComment c - where - f.parent = :parentDir - and t.parent = f - and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString like '%forumStickyPosting%') - and c.nodeInfo.nsThread in (select c2.nodeInfo.nsThread from WikiComment c2 where c2.parent = t) - group - by f.id - </query> + 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 + </sql-query> <query name="forumLastTopic"> select f.id, t from - WikiDirectory f, WikiDocument t left join fetch t.parent left join fetch t.createdBy u left join fetch u.profile + WikiDirectory f, WikiDocument t left join fetch t.parent where f.parent = :parentDir and t.parent = f @@ -64,23 +69,61 @@ and t.createdOn = (select max(t2.createdOn) from WikiDocument t2 where t2 = t) </query> - <query name="forumLastReply"> + <sql-query name="forumLastReply"> + <return-scalar column="FORUM_ID" type="long"/> + <return class="org.jboss.seam.wiki.core.model.WikiComment"/> select - f.id, c + dir1.NODE_ID as FORUM_ID, + + c1.NODE_ID as NODE_ID, + c1.OBJ_VERSION as OBJ_VERSION, + c1.RATING as RATING, + c1.AREA_NR as AREA_NR, + c1.NAME as NAME, + c1.WIKINAME as WIKINAME, + c1.CREATED_ON as CREATED_ON, + c1.CREATED_BY_USER_ID as CREATED_BY_USER_ID, + c1.LAST_MODIFIED_ON as LAST_MODIFIED_ON, + c1.LAST_MODIFIED_BY_USER_ID as LAST_MODIFIED_BY_USER_ID, + c1.WRITE_ACCESS_LEVEL as WRITE_ACCESS_LEVEL, + c1.READ_ACCESS_LEVEL as READ_ACCESS_LEVEL, + c1.WRITE_PROTECTED as WRITE_PROTECTED, + c1.PARENT_NODE_ID as PARENT_NODE_ID, + + c0.SUBJECT as SUBJECT, + c0.FROM_USER_NAME as FROM_USER_NAME, + c0.FROM_USER_EMAIL as FROM_USER_EMAIL, + c0.FROM_USER_HOMEPAGE as FROM_USER_HOMEPAGE, + c0.USE_WIKI_TEXT as USE_WIKI_TEXT, + c0.NS_LEFT as NS_LEFT, + c0.NS_RIGHT as NS_RIGHT, + c0.NS_THREAD as NS_THREAD from - WikiDirectory f, WikiDocument t, WikiComment c left join fetch c.parent left join fetch c.createdBy u left join fetch u.profile + 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 &lt;= :readAccessLevel, + WIKI_COMMENT c0 + inner join WIKI_NODE c1 on c0.NODE_ID = c1.NODE_ID where - f.parent = :parentDir - and t.parent = f - and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString like '%forumStickyPosting%') - and c.nodeInfo.nsThread in (select c2 from WikiComment c2 where c2.parent = t) - and c.createdOn = ( - select max(c3.createdOn) from WikiDirectory f2, WikiDocument t2, WikiComment c3 - where f2.parent = :parentDir and t2.parent = f2 - and (t2.headerMacrosString like '%forumPosting%' or t2.headerMacrosString like '%forumStickyPosting%') - and c3.nodeInfo.nsThread in (select c4.nodeInfo.nsThread from WikiComment c4 where c4.parent = t2) + 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%') + where + doc0.PARENT_NODE_ID= dir0.NODE_ID + and doc0.READ_ACCESS_LEVEL &lt;= :readAccessLevel + and ct1.PARENT_NODE_ID = doc0.NODE_ID + order by ct3.CREATED_ON desc + limit 1 ) - </query> + </sql-query> <query name="forumUnreadTopics"><![CDATA[ select @@ -136,8 +179,6 @@ and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString like '%forumStickyPosting%') </query> - - <!-- TODO: HQL doesn't support CASE...WHEN or arbitrary OUTER JOINs --> <!-- 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"> @@ -180,17 +221,18 @@ 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 - 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 - ) + left outer join WIKI_NODE c0 + on 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 + where ct1.PARENT_NODE_ID = doc0.NODE_ID order by ct3.CREATED_ON desc + limit 1 + ) where doc2.PARENT_NODE_ID = :parentNodeId + and doc2.READ_ACCESS_LEVEL <= :readAccessLevel and (doc0.HEADER_MACROS like '%forumPosting%' or doc0.HEADER_MACROS like '%forumStickyPosting%') order by STICKY desc, LAST_POST desc @@ -223,7 +265,6 @@ ]]></query> - <!-- TODO: HQL doesn't support FROM clause subselect --> <sql-query name="forumPostersAndRatingPoints"> <return class="org.jboss.seam.wiki.core.model.User"/> <return-scalar column="RATING_POINTS" type="long"/> Modified: trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumDAOTests.java =================================================================== --- trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumDAOTests.java 2008-02-15 17:48:01 UTC (rev 7429) +++ trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumDAOTests.java 2008-02-16 15:30:10 UTC (rev 7430) @@ -30,6 +30,8 @@ @Test public void findForumsGuest() throws Exception { + if (!database.equals(Database.mysql)) return; + new FacesRequest() { protected void invokeApplication() throws Exception { WikiNodeDAO nodeDAO = (WikiNodeDAO)getInstance(WikiNodeDAO.class); @@ -47,6 +49,7 @@ @Test public void findForumsMember() throws Exception { + if (!database.equals(Database.mysql)) return; loginMember(); @@ -69,6 +72,7 @@ @Test public void findForumInfoMember() throws Exception { + if (!database.equals(Database.mysql)) return; loginMember(); Modified: trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumQueryTests.java =================================================================== --- trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumQueryTests.java 2008-02-15 17:48:01 UTC (rev 7429) +++ trunk/examples/wiki/src/test/org/jboss/seam/wiki/test/plugin/ForumQueryTests.java 2008-02-16 15:30:10 UTC (rev 7430) @@ -29,6 +29,7 @@ @Test public void findForums() throws Exception { + if (!database.equals(Database.mysql)) return; loginMember(); Modified: trunk/examples/wiki/view/plugins/tags/plugin.xhtml =================================================================== --- trunk/examples/wiki/view/plugins/tags/plugin.xhtml 2008-02-15 17:48:01 UTC (rev 7429) +++ trunk/examples/wiki/view/plugins/tags/plugin.xhtml 2008-02-16 15:30:10 UTC (rev 7430) @@ -13,29 +13,55 @@ <h:outputText value="#{messages['tags.label.Tags']}"/> </s:div> - <div class="boxContent"> + <div class="boxContent" style="padding: 5px;"> - <h:dataTable value="#{tagsSortedByCount}" var="tagCount" - columnClasses="defaultColumn, onePercentColumn" - cellpadding="0" cellspacing="0" border="0"> - <h:column> - <s:span rendered="#{param.tag != tagCount.tag and preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> - <h:outputLink value="#{wikiURLRenderer.renderURL(currentDocument)}/Tag/#{wiki:encodeURL(tagCount.tag)}">#{tagCount.tag}</h:outputLink> + <s:fragment rendered="#{not preferences.get('Tags', currentMacro).cloud}"> + + <h:dataTable value="#{tagsSortedByCount}" var="tagCount" + rendered="#{not preferences.get('Tags', currentMacro).cloud}" + columnClasses="defaultColumn, onePercentColumn" + cellpadding="0" cellspacing="0" border="0"> + <h:column> + <s:span rendered="#{param.tag != tagCount.tag and preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> + <h:outputLink value="#{wikiURLRenderer.renderURL(currentDocument)}/Tag/#{wiki:encodeURL(tagCount.tag)}">#{tagCount.tag}</h:outputLink> + </s:span> + <s:span rendered="#{param.tag != tagCount.tag and not preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> + <h:outputLink value="#{wikiURLRenderer.renderTagURL(tagCount.tag)}"> + <h:outputText value="#{tagCount.tag}"/> + </h:outputLink> + </s:span> + <s:span rendered="#{param.tag == tagCount.tag}"> + <h:outputText value="&gt;&#160;#{tagCount.tag}&#160;&lt;"/> + </s:span> + </h:column> + <h:column> + <h:outputText value="(#{tagCount.count})"/> + </h:column> + </h:dataTable> + + </s:fragment> + + <s:fragment rendered="#{preferences.get('Tags', currentMacro).cloud}"> + + <ui:repeat var="tagCount" value="#{tagsSortedByCount}"> + <s:span styleClass="tagCloudItem noWrapWhitespace" style="font-size: #{50+(100/tagsAggregator.highestTagCount*tagCount.count)}%;"> + <s:span rendered="#{param.tag != tagCount.tag and preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> + <h:outputLink value="#{wikiURLRenderer.renderURL(currentDocument)}/Tag/#{wiki:encodeURL(tagCount.tag)}">#{tagCount.tag}</h:outputLink> + </s:span> + <s:span rendered="#{param.tag != tagCount.tag and not preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> + <h:outputLink value="#{wikiURLRenderer.renderTagURL(tagCount.tag)}"> + <h:outputText value="#{tagCount.tag}"/> + </h:outputLink> + </s:span> + <s:span rendered="#{param.tag == tagCount.tag}"> + <h:outputText value="&gt;&#160;#{tagCount.tag}&#160;&lt;"/> + </s:span> </s:span> - <s:span rendered="#{param.tag != tagCount.tag and not preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> - <h:outputLink value="#{wikiURLRenderer.renderTagURL(tagCount.tag)}"> - <h:outputText value="#{tagCount.tag}"/> - </h:outputLink> - </s:span> - <s:span rendered="#{param.tag == tagCount.tag}"> - <h:outputText value="&gt;&#160;#{tagCount.tag}&#160;&lt;"/> - </s:span> - </h:column> - <h:column> - <h:outputText value="(#{tagCount.count})"/> - </h:column> - </h:dataTable> + <h:outputText value="&#160;&#160; "/> + </ui:repeat> + </s:fragment> + <s:span styleClass="undecoratedLink defaultColumn" rendered="#{preferences.get('Tags', currentMacro).linkToCurrentDocument and not empty param.tag}"> <h:outputLink value="#{wikiURLRenderer.renderURL(currentDocument)}">