Too many selects (N+1) executed by JBOSS on finders

[aparaapara]

I am using JBOSS 3.2.2RC3, and it seems that when a finder is executing it first selects the keys and then proceeds to execute a SELECT statement for EVERY row returned.

So, if I return 100 elements in the finder, JBOSS executes 100 SELECT statements to find the data. So in total I get N+1 select statements. This is very slow and quite expensive on the database side.

I am sure this has been asked before, but what is a way around this?

Thanks!

[nraghuram]

you can use on-find read ahead strategy. It will load the fields along with the select query

[aparaapara]

I have the following in my jbosscmp-jdbc.xml descriptor:


java:/MyProfilesDS
<datasource-mapping>PostgreSQL 7.2</datasource-mapping>
<read-ahead>
on-find
<page-size>4096</page-size>
<eager-load-group>*</eager-load-group>
</read-ahead>
<list-cache-max>5</list-cache-max>



But it still does not appear to work any better. I am running on JBOSS 3.2.2RC3. Is there anything else that I need to do ?

Thanks.
-AP_

[aparaapara]

Here is an example, where I see [findByOwner] executed first to get the keys of folders for a particular member, and then a buch of selects to get individual folder details. Why wouldn't the data be cached from the original [findByOwner] call?

08:04:16,081 DEBUG [findByOwner] Executing SQL: SELECT t0_cf.id, t0_cf.name, t0_cf.description, t0_cf.note, t0_cf.componentCount, t0_cf.createDate, t0_cf.ownerid FROM componentfolder t0_cf, member t1_cf_owner WHERE ((t1_cf_owner.id=?)) AND (t0_cf.o
wnerid=t1_cf_owner.id)
08:04:16,189 DEBUG [ComponentFolder] Executing SQL: SELECT name, description, note, componentCount, createDate, ownerid FROM componentfolder WHERE (id=?)
08:04:16,209 DEBUG [ComponentFolder] Executing SQL: SELECT name, description, note, componentCount, createDate, ownerid FROM componentfolder WHERE (id=?)
08:04:16,212 DEBUG [ComponentFolder] Executing SQL: SELECT name, description, note, componentCount, createDate, ownerid FROM componentfolder WHERE (id=?)

Thanks.
-AP_

[ioparra]

I too have had an a few interesting situations with CMP and N+1. Are you sure your running each call under a single transaction? If you aren't, then the only thing loaded that is valid between each call is the PK. If you are ....

For JBoss3.2.1, I found that findByPK worked correctly, findAll did not. Try modifying your code to findByPrimaryKey and see if the N+1 becomes 1. I'm curious to hear the results. If it doesn't go away, then it's either a bug or some sort of configuration error.

-Ivan

http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=

[aparaapara]

Well, I am executing a finder from a session facade who's transaction attribute is "Required". I am not sure what I will be able to demonstrate if I simply do my own findByPrimaryKey (hard, since I don't have the primary key to begin with).

-AP_

[aparaapara]

I am really hoping that there is a solution to this problem (which should not be having to re-write my application without using Entity Beans). At this point, it's a real showstopper, the kind of performance that I am getting is terrible.

If anyone has had an experience with this, please chime in.

Thanks.
-AP_

[raja05]

How does ur EJB-QL look like? Is it a
SELECT OBJECT(a) from .... or is it something like
SELECT a.xxxx from ...?

-Raj

[aparaapara]

All my ejb-ql look like select OBJECT(x) from table x where ..., here is an example:


<query-method>
<method-name>findNRecentlyUpdatedPublicSearchableProfiles</method-name>
<method-params>
<method-param>int</method-param>
</method-params>
</query-method>
<ejb-ql>
select object(p) from profile p where p.privateProfileValue = false AND p.searchableValue = true
</ejb-ql>


-AP_

[aparaapara]

Here is the definition of the actual findByOwner query which produced the results at the beginning of this thread:


<query-method>
<method-name>findByOwner</method-name>
<method-params>
<method-param>com.myprofiles.model.member.MemberEntity</method-param>
</method-params>
</query-method>
<ejb-ql>
select object(cf) from componentFolder cf where cf.owner = ?1
</ejb-ql>

[raja05]

I was looking at the source code andit looks like it will look into loading "eager-load-groups" only when the "select " clause returns an Entity and not a field. So ur case seems fine.
Can you specify the load-groups specific to a particular entity rather than the "defaults" ?

-Raj

[aparaapara]

I was so sure that everything was running under the same transaction, however, after a bit more digging it turned out that that was not the case. I am converting from a different server which forced me to be rather creative with my TX attribute assignments. After adding a proper TX attribute things are loading much faster.

The problem is fixed, thanks to all that helped me out on this issue.

-AP_