[jboss-user] [JBoss Seam] - Re: EntityQuery (entity-query) General Performance Questions

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/02...) 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. View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4077459#... Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...