3:28 p.m.
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#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...