Hi Suresh, I believe this is more of a bug in JPA or we are configuring it in the wrong way. If we have configured a ManyToOne relationship to a LAZY initialization [8], it should not load ExperimentEntity if we haven't invoked getExperiment().get.. methods. But in this case, it's automatically initializing ExperimentEntity and that's why we are getting this recursive object creation. What we need to do is to stop this recursive object creation in bidirectional relationships.
As a general observation and a reply to suresh's suggestion, In the long run, observing the whole database stack from the top level, I think we need to consider using some sophisticated framework like spring (spring boot) to handle JPA transactions. Currently we are coding very close to entity managers and that might cause unexpected issues if we haven't done it properly. That (Spring) might facilitate us to either go with GraphQL or Grpc based on the evaluations we get. [8] https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80 Thanks Dimuthu On Tue, Sep 22, 2020 at 12:15 PM Suresh Marru <sma...@apache.org> wrote: > Hi Dimuthu, > > I have been wondering if GraphQL will be worthwhile to explore for such > bulk retrieval’s? I think this will be a low hanging fruit for us to try to > integrating with something like Hasura[4] it is dominantly used for > Postgres but they have been actively supporting MySql as well [5] > > I am not sure if this will address you point out though, since GraphQL > spec doesn’t support recursive nested objects [6], simple one to many seems > to be supported [7] but not bi directional. > > Suresh > > [4] - https://github.com/hasura/graphql-engine > [5] - https://hasura.io/docs/1.0/graphql/core/guides/mysql-preview.html > [6] - https://github.com/graphql/graphql-spec/issues/91 > [7] - > https://hasura.io/docs/1.0/graphql/core/schema/table-relationships/database-modelling/one-to-many.html > > > On Sep 22, 2020, at 11:01 AM, DImuthu Upeksha <dimuthu.upeks...@gmail.com> > wrote: > > Hi Folks, > > I just noticed that the entity relations that have > bidirectional definitions (OneToMany and ManyToOne) between 2 tables cause > recursive object fetching and it is causing a significant performance drop > in database querying. For example [1] and [2]. When we fetch an > experiment output, I can see a stack of experiment -> experiment outputs -> > experiment .... > > Same issue was mentioned in [3] but the solution is for spring boot json > parsing. As we don't use spring boot, we might have to figure out some > other way to stop this recursive stack creation. > > [1] > https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentEntity.java#L88 > [2] > https://github.com/apache/airavata/blob/master/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/expcatalog/ExperimentOutputEntity.java#L80 > [3] > https://medium.com/@udith.indrakantha/issue-related-with-infinite-recursive-fetching-of-data-from-relationships-between-entity-classes-ffc5fac6c816 > > Thanks > Dimuthu > > >
