Non-collocated SQL joins can create performance issues. We no longer need to use affinity keys to collocate the entries. In this section, we are going populate the player and club caches and use distributed SQL joins. The following are the steps:

1. Create a class, SQLQueryNonCollocatedTest:

     public class SQLQueryNonCollocatedTest {

     private static final String CLUB_SQL_CACHE = "club_sql_cache";
     private static final String PLAYER_SQL_CACHE = "player_sql_cache";

     public static void main(String[] args) {
     IgniteConfiguration cfg = new IgniteConfiguration();
     cfg.setClientMode(true);
     cfg.setPeerClassLoadingEnabled(true);

2. Create the player config with—key = player id and value= player:

      CacheConfiguration<Long, SoccerPlayer> playerCacheConfig = new    
      CacheConfiguration<>();
      playerCacheConfig.setName(PLAYER_SQL_CACHE);
      playerCacheConfig.setIndexedTypes(Long.class, 
      SoccerPlayer.class);
      playerCacheConfig.setCacheMode(CacheMode.PARTITIONED);

3. Create the club config:

      CacheConfiguration<Long, SoccerClub> clubCacheConfig = new  
      CacheConfiguration<>();
      clubCacheConfig.setName(CLUB_SQL_CACHE);
      clubCacheConfig.setIndexedTypes(Long.class, SoccerClub.class);
      clubCacheConfig.setCacheMode(CacheMode.REPLICATED);

4. Set the cache configs to ignite config:

     cfg.setCacheConfiguration(playerCacheConfig, clubCacheConfig);

5. Start an ignite instance, create or get the caches, and populate the player/club cache:

     try (Ignite ignite = Ignition.start(cfg)) {

     IgniteCache<Long, SoccerPlayer> playerCache =    
     Ignition.ignite().getOrCreateCache(PLAYER_SQL_CACHE);
     IgniteCache<Long, SoccerClub> clubCache =   
     Ignition.ignite().getOrCreateCache(CLUB_SQL_CACHE);

     SoccerClub barcelona = new SoccerClub(1l, "Barcelona");
     SoccerClub chelsea = new SoccerClub(2l, "Chelsea");

    clubCache.put(barcelona.getId(), barcelona);
    clubCache.put(chelsea.getId(), chelsea);

    long id = 1;
    SoccerPlayer suarez = new SoccerPlayer(id++, "Luis Suárez", 
    578699.00d, barcelona.getId());
    SoccerPlayer messi = new SoccerPlayer(id++, "Leo Messi", 
    200000.00d, barcelona.getId());
    SoccerPlayer hazard = new SoccerPlayer(id++, "Eden Hazard", 
    178999.00d, chelsea.getId());

    playerCache.put(suarez.getId(), suarez);
    playerCache.put(messi.getId(), messi);
    playerCache.put(hazard.getId(), hazard);

6. Now, apply a SQLquery join on the non-collocated cache to fetch the 'Barcelona' players:

    System.out.println("Find Barcelona players");
    SqlQuery<Long, SoccerPlayer> sqlQuery = new SqlQuery<>   
    (SoccerPlayer.class,
        "from SoccerPlayer, "" + CLUB_SQL_CACHE + "".SoccerClub "
           + "where SoccerPlayer.clubId = SoccerClub.id and 
        SoccerClub.name = ?");

     QueryCursor<Entry<Long, SoccerPlayer>> resultCursor =     
     playerCache.query(sqlQuery.setArgs("Barcelona"));

     resultCursor.forEach(e -> {
      System.out.println(e.getValue());
    });

7. Launch three ignite server instances and run the program; it will populate the remote nodes and generate the following output: