[jboss-user] [JBoss Seam] - EntityQuery (entity-query) General Performance Questions
jfrankman
do-not-reply at jboss.com
Tue Aug 21 16:28:18 EDT 2007
Has anyone had experience using the EntityQuery class to query a mid to large sized table (300,000+ records)?
I used seam gen to create a CRUD application for a single table. The main list.xhmtl page uses a generated component that extends from the EntityQuery class. The table I am querying has about 300,000 records. I have set the maxresults=25. But the applicaiton is extremely slow. It takes several seconds to load the list page and at least 3-6 seconds to do a search. The database is DB2 V9. Other application components I have written work well with DB2 and there are not any performance problems. But the generated list page is still very slow. Worse yet, if the class had any EAGER associations with other classes the query never came back with any results.
Is 300,000 records too much for the EntityQuery to handle?
I would not thnk so, but I found that when the page first loads the application exectues a "select all" statment (select * from tablex) It seems that is does not limit the result set on the query, but selects all the records and the filters the results down to 25 after the database has returned the query results. Of course I could be totally wrong on this but I can't see any other problem. It would be helpful if there was some more in depth documentation for the EntityHome and EntityQuery classes.
Anyhow, has anyone used the EntityQuery to do work over a mid to large sized table? If so, did it work "out of the box" or did you have to do some additional configuration and/or coding to get it to work for you?
Here is the class that extends QueryList:
package com.idfbins.nexus.presentation;
|
| import java.util.Arrays;
| import java.util.List;
|
| import org.jboss.seam.annotations.Name;
| import org.jboss.seam.framework.EntityQuery;
|
| import com.idfbins.nexus.common.vo.busent.ClientVO;
|
|
|
| @Name("clientList")
| public class ClientListQuery extends EntityQuery {
|
| public ClientListQuery() {
| this.setMaxResults(25);
| this.setOrder("search");
| }
|
| private static final String[] RESTRICTIONS = {
| "lower(clientVO.search) like concat(#{clientList.clientVO.search},'%')",
| "lower(clientVO.memberNumber) like concat(#{clientList.clientVO.memberNumber},'%')",
| };
|
|
| private ClientVO clientVO = new ClientVO();
|
| @Override
| public String getEjbql() {
| return "select clientVO from ClientVO clientVO";
| //return "select clientVO from ClientVO clientVO left join fetch clientVO.entityLocations entityLocation " ;
| }
|
|
| @Override
| public String getOrder() {
| // TODO Auto-generated method stub
| return "search";
| }
|
|
| @Override
| public Integer getMaxResults() {
| return 25;
| }
|
| public ClientVO getClientVO() {
| return clientVO;
| }
|
| @Override
| public List<String> getRestrictions() {
| return Arrays.asList(RESTRICTIONS);
| }
| }
Here is the list page:
<!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
| "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
| <ui:composition xmlns="http://www.w3.org/1999/xhtml"
| xmlns:s="http://jboss.com/products/seam/taglib"
| xmlns:ui="http://java.sun.com/jsf/facelets"
| xmlns:f="http://java.sun.com/jsf/core"
| xmlns:h="http://java.sun.com/jsf/html"
| xmlns:rich="http://richfaces.ajax4jsf.org/rich"
| template="layout/template.xhtml">
|
| <ui:define name="body">
|
| <h:messages globalOnly="true" styleClass="message" id="globalMessages"/>
|
| <h:form id="fbLenderSearch" styleClass="edit">
|
| <rich:simpleTogglePanel label="Lender search parameters" switchType="server">
|
| <s:decorate template="layout/display.xhtml">
| <ui:define name="label">memberno</ui:define>
| <h:inputText id="memberno" value="#{clientList.clientVO.memberNumber}"/>
| </s:decorate>
|
|
|
| <s:decorate template="layout/display.xhtml">
| <ui:define name="label">search</ui:define>
| <h:inputText id="search" value="#{clientList.clientVO.search}"/>
| </s:decorate>
|
|
|
|
| </rich:simpleTogglePanel>
|
| <div class="actionButtons">
| <h:commandButton id="search" value="Search" action="/LenderLookupList.xhtml"/>
| </div>
|
| </h:form>
|
| <rich:panel rendered="#{not empty clientList.clientVO.search}">
| <f:facet name="header">Lender search results</f:facet>
| <div class="results" id="clientList">
|
| <h:outputText value="No Lender exists"
| rendered="#{empty clientList.resultList}"/>
|
| <rich:dataTable id="clientList"
| var="clientVO"
| value="#{clientList.resultList}"
| rendered="#{not empty clientList.resultList}">
| <h:column>
| <f:facet name="header">
| <s:link styleClass="columnHeader"
| value="clientid #{clientList.order=='id asc' ? messages.down : ( fbclientList.order=='id desc' ? messages.up : '' )}">
| <f:param name="order" value="#{clientList.order=='id asc' ? 'id desc' : 'id asc'}"/>
| </s:link>
| </f:facet>
| #{clientVO.id}
| </h:column>
| <h:column>
| <f:facet name="header">
| <s:link styleClass="columnHeader"
| value="memberno #{clientList.order=='memberNumber asc' ? messages.down : ( clientList.order=='memberNumber desc' ? messages.up : '' )}">
| <f:param name="order" value="#{clientList.order=='memberNumber asc' ? 'memberNumber desc' : 'memberNumber asc'}"/>
| </s:link>
| </f:facet>
| #{clientVO.memberNumber}
| </h:column>
| <h:column>
| <f:facet name="header">
| <s:link styleClass="columnHeader"
| value="search #{clientList.order=='search asc' ? messages.down : ( clientList.order=='search desc' ? messages.up : '' )}">
| <f:param name="order" value="#{clientList.order=='search asc' ? 'search desc' : 'search asc'}"/>
| </s:link>
| </f:facet>
| #{clientVO.search}
| </h:column>
| <h:column >
| <f:facet name="header">
| city state
| </f:facet>
| <h:outputText value="#{clientVO.primaryLocation.summary}" rendered="#{clientVO.primaryLocation!=null}"/>
|
| </h:column>
| </rich:dataTable>
|
| </div>
| </rich:panel>
|
| <div class="tableControl">
|
| <s:link view="/LenderLookupList.xhtml"
| rendered="#{clientList.previousExists}"
| value="#{messages.left}#{messages.left} First Page"
| id="firstPage">
| <f:param name="firstResult" value="0"/>
| </s:link>
|
| <s:link view="/LenderLookupList.xhtml"
| rendered="#{clientList.previousExists}"
| value="#{messages.left} Previous Page"
| id="previousPage">
| <f:param name="firstResult"
| value="#{clientList.previousFirstResult}"/>
| </s:link>
|
| <s:link view="/LenderLookupList.xhtml"
| rendered="#{clientList.nextExists}"
| value="Next Page #{messages.right}"
| id="nextPage">
| <f:param name="firstResult"
| value="#{clientList.nextFirstResult}"/>
| </s:link>
|
| <s:link view="/LenderLookupList.xhtml"
| rendered="#{clientList.nextExists}"
| value="Last Page #{messages.right}#{messages.right}"
| id="lastPage">
| <f:param name="firstResult"
| value="#{clientList.lastFirstResult}"/>
| </s:link>
|
| </div>
|
|
| </ui:define>
|
| </ui:composition>
|
|
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4076479#4076479
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4076479
More information about the jboss-user
mailing list