-
1. Re: Too many selects (N+1) executed by JBOSS on finders
nraghuram Sep 23, 2003 3:44 AM (in response to aparaapara)you can use on-find read ahead strategy. It will load the fields along with the select query
-
2. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 23, 2003 8:22 AM (in response to 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_ -
3. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 23, 2003 8:31 AM (in response to 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_ -
4. Re: Too many selects (N+1) executed by JBOSS on finders
ioparra Sep 23, 2003 12:27 PM (in response to aparaapara)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= -
5. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 23, 2003 2:44 PM (in response to 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_ -
6. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 23, 2003 7:51 PM (in response to 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_ -
7. Re: Too many selects (N+1) executed by JBOSS on finders
raja05 Sep 23, 2003 9:25 PM (in response to aparaapara)How does ur EJB-QL look like? Is it a
SELECT OBJECT(a) from .... or is it something like
SELECT a.xxxx from ...?
-Raj -
8. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 24, 2003 6:30 AM (in response to 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_ -
9. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 24, 2003 7:28 AM (in response to 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> -
10. Re: Too many selects (N+1) executed by JBOSS on finders
raja05 Sep 24, 2003 7:31 AM (in response to aparaapara)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 -
11. Re: Too many selects (N+1) executed by JBOSS on finders
aparaapara Sep 24, 2003 11:42 PM (in response to 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_