3 Replies Latest reply on Sep 3, 2004 2:11 PM by godboles

    read-ahead prefetch does not seem to work

    godboles

      Helloo all,

      I have a zip code table (mysql) and I have written a CMP bean (read only) to retrieve zip codes. The primary key of this table is an auto increment field called id.

      I have defined a custom finder method and I have the following xdoclet atributes for this finder method:

      read-ahead="true"
      strategy="on-load"
      eager-load-group="*"
      page-size="500"

      When I call the finder method, the following SQL gets executed:
      DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.UsaZipCode#findZipcodeForCityExact] Executing SQL: SELECT t0_z.id, t0_z.zipcode, t0_z.zipcodetype, t0_z.city, t0_z.citytype, t0_z.county, t0_z.state, t0_z.statecode, t0_z.latitude, t0_z.longitude FROM UsaZipCode t0_z WHERE (t0_z.city = ? AND t0_z.statecode = ? AND t0_z.zipcodetype = 'S')

      This finder method returns a collection of CMP beans. I iterate over this collection and try to retrive information such as the zip code, name of the city etc. For every iteration another SQL gets executed as follows:

      DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.UsaZipCode] Executing SQL: SELECT zipcode, zipcodetype, city, citytype, county, state, statecode, latitude, longitude FROM UsaZipCode WHERE (id=?)

      My question are as follows:
      1) Why is the second SQL executed for every iteration?
      2) Should the read-ahead method have pre-fetched these entity beans in the first query itself?

      Any help would be appreciated.

      Thanks.

      Hari G