EntityQuery (entity-query) General Performance Questions

[jfrankman]

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>

[jfrankman]

After more research I found most of the performance problems have to do with the generated DB2 SQL and the way DB2 handles the query. DB2 does not handle case insensitive searches by default. When a query is run like:

Select * from nametable where lower(lastname) like 'doe%'

A DB2 will use a tablescan even if there is an index on the lastname column. DB2 will not be able to use the index since the "lower" function can potentially contradict the order of the data contained in the index. Because of this I could find twp different ways to improve performance:

Option 1: Remove the "lower" from the HQL completely. This assumes that the column you are searching on is only one case, or you don't mind case-sensitive searches in your application.

Instead of this:

lower(fbclient.search) like concat(lower(#{fbclientList.fbclient.search}),'%')"

Use This:

fbclient.search like concat(#{fbclientList.fbclient.search},'%')"

Notice that I have removed the lower function from the right side of the predicate (like concat(lower(#{fbclientList.fbclient.search})). This is due to a bug in Hibernate with regards to DB2 where it generates invalid SQL. This is not to solve the performance problem. The left side of the predicate is what is important here.

Option 2: Where a case insensitive search is important and the data in the table is mixed case, I had to create a generated column in the table that would convert the mixed case column into a lower case column. This is a workaround in DB2 for case insensitive searches other databases would handle this differently. (see http://www.ibm.com/developerworks/db2/library/techarticle/0203adamache/0203adamache.html)

So, first I had to create the column in DB2:

ALTER TABLE FBNEXUS.FBCLIENT ADD COLUMN
SEARCH_LOWER VARCHAR (100) NOT NULL GENERATED ALWAYS AS (lower(search));

Then create an index over the generated column:

CREATE INDEX idx_xyz ON fbclient (search_lower)

Once this is finished, the SQL generated from the HQL will not cause any performance problems. DB2 will be able to use the idx_xyz to perform the search. Note that you do not have to map generated column "SEARCH_LOWER" to your entity class (or anywhere else in your code). DB2 will automatically use the index (idx_xyz) on the search_lower column when ever you specify a Lower(search) in the where clause. DB2 is at least smart enought to figure that out. Therefore, the generated SQL from this HQL:

lower(fbclient.search) like concat(#{fbclientList.fbclient.search},'%')"

will not result in a table scan (wich is the source of the poor performance) but instead will use the index idx_xyz.