0 Replies Latest reply on Jun 15, 2016 5:15 PM by Avihai Marchiano

    Efficient load plan for many-to-many when you have large cartesian product

    Avihai Marchiano Novice

      I have the following relations:

      Provider(1:m)Campaigns

      Campaigns(m:m)Locations

      In few cases each campaign has relation to almost all locations. So in case we have 10K locations (for provider) and 300 campaign (for the same provider) we will have 3M associations (for the provider).

      Locations are lazy loaded and map as sub-select fetch:

      @ManyToMany(fetch = FetchType.LAZY, targetEntity = Location.class)

      @JoinTable(name = "campaign_locations", joinColumns = @JoinColumn(name = "campaign_id"), inverseJoinColumns = @JoinColumn(name = "point_id"))

      @Fetch(FetchMode.SUBSELECT)

      public List<Location> locations = Lists.newLinkedList();

      So, when campaign.locations (we call to provider.campaigns before so all campaigns were loaded) it will trigger load of all locations, but it loaded them by join with the association table so it mean that the result list is 3M locations + association instead of just load 10K locations and the associations.

      The best plan will be to load the associations and than load locations. Is there a way to do this?