Hello Dan, Thank you for the answer and welcome back.
I'm reviewing your alternatives: 1. live with it, is not alternative to me, because the performance problem, if the query return 100 items and I have 8 relationship with another entities then I will have 800 addtional queries (anybody report it? or may be in wicket viewer not happen it?). 2. consider using the simplified representation [2] provided by the RO viewer, I really like this alternative, but at this time already I use it. That's how I do it: add this headear in any call: Accept:application/json;profile="urn:org.apache.isis/v1";suppress="true", but it produce the same result. I need add anything else? 3. provide a custom ContentNegotiatorService, I´m working in this alternative too, I'm first analyzing ContentNegotiationServiceOrgApacheIsisV1.java and trying understand why and where does the other queries (I do not see where this representation returns the titles of the other objects). I think that should make other queries just if x-ro-follow-links query string is send by the client, in another case if the realtionship is null, it should not append the them. What do you think? Thanks, 2016-08-30 2:28 GMT-05:00 Dan Haywood <[email protected]>: > Hi Paul, > > sorry for the delay in replying (have been on vacation). > > What's happening here is that the default representation of the Solicitud > class: > > public class Solicitud implements Comparable<Solicitud> { > ... > private Sucursal sucursal; > private NivelEscalamiento nivelEscalamiento; > private Compania compania; > private Canal canal; > } > > needs to resolve all four of those properties, because it returns the title > of each of those associated objects. Your initial query results in both > the Solicitud entity and its associated Sucursal entity to be obtained in a > single query, but for the remaining 3 properties (NivelEscalamiento, > Compania and Canal) the act of generating the representation means that DN > will load those properties via separate queries. > > To fix this, you have some choices: > > 1. live with it > 2. extend your initial query to also eagerly load those additional > properties > 3. create a view model [1] that more precisely returns just the data that > you need, and return this instead. > 4. consider using the simplified representation [2] provided by the RO > viewer > 5. provide a custom ContentMappingService [3] and your own media types to > take control of how the representations are generated > 6. provide a custom ContentNegotiatorService [4] or RepresentationService > [5] for even more control > > HTH > Dan > > [1] > http://isis.apache.org/guides/ugbtb.html#_ugbtb_view-models_use-cases_dtos > [2] > http://isis.apache.org/guides/ugvro.html#_ugvro_simplified-representations > [3] > http://isis.apache.org/guides/rgsvc.html#_rgsvc_spi_ContentMappingService > [4] > http://isis.apache.org/guides/rgfis.html#_rgfis_spi_ > ContentNegotiationService > [5] > http://isis.apache.org/guides/rgfis.html#_rgfis_spi_RepresentationService > > > > On 11 August 2016 at 21:58, Paul Escobar <[email protected]> > wrote: > > > Hi Everyone, > > > > I have a N+1 query problem using a method in a Domain Service. The method > > (invoked from postman): > > > > *public List<Solicitud> listarSolicitudesTramite(final > > @ParameterLayout(named="Tramite") String tramite) {* > > > > * PersistenceManager persistenceManager = > > isisJdoSupport.getJdoPersistenceManager();* > > * persistenceManager.getFetchPlan().addGroup("sucursales");* > > * final Query query = persistenceManager.newQuery(Solicitud.class);* > > * query.setFilter( inTramites( tramite ) );* > > * query.setOrdering("fechaCierre DESC");* > > * List<Solicitud> solicitudesFiltradas = (List<Solicitud>) > > query.execute();* > > * return solicitudesFiltradas;* > > * }* > > > > > > The Solicitud Class: > > > > *@FetchGroup(name="sucursales", members={@Persistent(name="sucursal")})* > > *@DatastoreIdentity(strategy=IdGeneratorStrategy.IDENTITY, > > column="idSucursal")* > > *public class Solicitud implements Comparable<Solicitud> {* > > *.* > > *.* > > *.* > > > > * private Sucursal sucursal;* > > * private NivelEscalamiento nivelEscalamiento;* > > * private Compania compania;* > > * private Canal canal;* > > *...* > > *}* > > > > The line: List<Solicitud> solicitudesFiltradas = (List<Solicitud>) > > query.execute(); execute this query: > > > > *SELECT * > > *DISTINCT 'domainapp.dom.mesaAyudaPrestadores.Solicitud' AS > NUCLEUS_TYPE,* > > *A0.correoContacto,* > > *A0.descripcion,* > > *A0.extension,* > > *A0.fechaCierre,* > > *A0.fechaCreacion,* > > *A0.fechaInicioCreacion,* > > *A0.nombreBeneficiario,* > > *A0.nombreSolicitante,* > > *A0.numDocBeneficiario,* > > *A0.numDocSolicitante,* > > *C0.descSucursal,* > > *C0.idSucursal,* > > *A0.telefonoContacto,* > > *A0.usuario,* > > *A0.usuarioAsignado* > > *,A0.idSolicitud,* > > *A0.fechaCierre AS NUCORDER0 * > > *FROM TBL_SOLICITUD A0 * > > *LEFT OUTER JOIN TBL_SUCURSAL B0 ON A0.sucursal_idSucursal_OID = > > B0.idSucursal * > > *LEFT OUTER JOIN TBL_SUCURSAL C0 ON A0.sucursal_idSucursal_OID = > > C0.idSucursal * > > *WHERE * > > *B0.descSucursal = 'Principal Antioquia' ORDER BY NUCORDER0 DESC* > > > > It is fine!, because I need just Solicitud data plus Sucursal data (just > > this one related entity, not the other relationships), but after method > > return this other queries are run to fill the another relationships (N+1 > > problem): > > > > > > *SELECT B0.descNivel,B0.idNivelEscalamiento FROM TBL_SOLICITUD A0 LEFT > > OUTER JOIN TBL_NIVEL_ESCALAMIENTO B0 ON > > A0.nivelEscalamiento_idNivelEscalamiento_OID = B0.idNivelEscalamiento > > WHERE > > A0.idSolicitud = <1>* > > > > > > *SELECT B0.descCanal,B0.idCanal FROM TBL_SOLICITUD A0 LEFT OUTER JOIN > > TBL_CANAL B0 ON A0.canal_idCanal_OID = B0.idCanal WHERE A0.idSolicitud = > > <1>* > > > > *SELECT B0.descCompania,B0.idCompania FROM TBL_SOLICITUD A0 LEFT OUTER > JOIN > > TBL_COMPANIA B0 ON A0.compania_idCompania_OID = B0.idCompania WHERE* > > * A0.idSolicitud = <1>* > > > > > > How can I avoid this N+1 problem? I thought that using FetchPlan and > Groups > > was enough to do it. > > > > > > PD: isis.viewer.restfulobjects.honorUiHints is false and no > > x-ro-follow-links query parameter is used. > > > > > > Thanks, > > > > > > -- > > Paul Escobar Mossos > > skype: paulescom > > telefono: +57 1 3006815404 > > > -- Paul Escobar Mossos skype: paulescom telefono: +57 1 3006815404
