I have an issue I am experiencing in an application that I am developing in spring boot with Spring Data Neo4j. This could probably be chalked up to wrong data modelling on my part (it’s sort of too late to change this now), but some behaviours I have been experiencing are not expected.

I deployed my application to our staging server for some testing and I received reports that a few of my endpoints take too long to return results, (the response times for these endpoints are in the 10s of seconds range).

I setup prometheus to track the response times of the endpoints and I noticed that my calls to save data in the database were taking 5+ seconds. If I make multiple save calls within that endpoint, the response time for the endpoint goes into the 10s of seconds.

I pulled the data from the staging server unto my local environment and did some performance profiling. I noticed that the entities that were slow (either read or write) had some relationships with Collection items. For example

@Node()
public class Game {

   @Id @GeneratedValue
   private String gameId;

   @Relationship(value = "GAME_ROUNDS")
   private Set<GameRounds> rounds = new HashSet();

   @Relationship(value = "GAME_PLAYERS")
   private Set<GamePlayers> players = new HashSet();

   // ... rest of entity omitted for brevity
}


@Node()
public class GameRound {
  
   @Id @GeneratedValue
   private String gameId;

    // ... rest of entity omitted for brevity
}

@Node()
public class GamePlayer {
  
   @Id @GeneratedValue
   private String playerId;

   private String name;

    // ... rest of entity omitted for brevity
}

(The relationship between the Game/GameRound/GamePlayer is bidirectional because at certain points, it was necessary to have relationship that way)

If I run a findAll query, it takes about 5 seconds to return the data (even paginated). Below is a sample from prometheus and in this case I set the page size to 10. I currently have only 42 games.

spring_data_repository_invocations_seconds_max{exception="None",method="findAll",repository="GameRepository",state="SUCCESS",} 5.035270583

I imagine this would probably grow as my data also grows. This case in particular isn’t really a big deal, I can write a query to load only the entities that I need and reduce the response time.

The main crux of the issue is, as I was trying to speed up one of the slow endpoints, I managed to do so. I got the response time from about 10s to sub 1s. I used a query that only fetched what I needed. For instance, the game entity would be retrieved but with only players. I could then go ahead and do whatever mutation I needed to do (say add a player to the list of players) and the save the player and Game.
It was in saving the game that I noticed something odd. Because I didn’t have the rounds in memory when I was persisting the game, the round data would all be lost when I saved the game. This is not desirable as without the rounds, the games cannot be played and if the rounds are lost whenever a new player is added, this is not a very good experience.

So my main question is, what is the correct way to go about this (if there is one)? If I have the full game (with all the other relationships) in memory at the time I mutate and persist the game, I don’t lose any data, but fetching the full game data is also slow because of the lists that need to be retrieved. Any help would be appreciated. For now, a workaround I’ve had to adopt is to offload some of the write operations I don’t need the response for to another thread so it doesn’t block the rest of the operations (the AsyncRunner is a utility class with a method wrapping around CompletableFuture)

A sample flow that results in a slow response time is as below. In the sample below, if I replace use the ValidationUtils.existsById(..), the response time grows from about 0.4s (now) to about close to 7 seconds. The ValidationUtils method isn’t anything special, it just calls findById in a service class and unwraps the optional value else throws a ResourceNotFoundException, basically what I have done with the game optional.

@PostMapping("{playerId}/games/{gameId}/start")
    @Timed(value = "start.game.endpoint", description = "Time taken to start a game")
    public ResponseEntity<SinglePlayerGameResponse> startGame(
            @PathVariable Long playerId,
            HttpServletRequest request,
            Authentication authentication,
            @PathVariable UUID gameId
    ) throws ApiValidationException {
        final var requestId = request.getSession().getId();
        log.info("[{}] Player {} wants to start game with id {}", requestId, playerId, gameId);

        log.info("[{}] checking if game exists", requestId);
        final var gameOptional = gameService.findGameById(gameId);
                //ValidationUtils.existsById(gameId, gameService, "Game");

        if(gameOptional.isEmpty()) {
            throw new ResourceNotFoundException("Game with id "+gameId+" not found");
        }

        final var game = gameOptional.get();

        if(game.isCycleComplete()) {
            throw new ApiValidationException("Error", Collections.singletonList(new ApiError("Complete", "The cycle for this game is complete so it can't be played")));
        }

        if(gameService.isPlayerOfGame(gameId, playerId)) {
            throw new ApiValidationException("Error", Collections.singletonList(new ApiError("Played", "This player is already registered for this game")));
        }

        log.info("[{}] checking if player exists", requestId);
        final var player = ValidationUtils.existsById(playerId, studentService, "Player");

        log.info("[{}] player and game both exist, about to add player and save", requestId);
        final var now = ZonedDateTime.now();
        final var playerGame = new PlayerGame();
        playerGame.setOwner(player);
        playerGame.setStartDate(now);
        playerGame.setComplete(false);
        playerGame.setTotalPoints(0L);
        playerGame.setTotalProductionCapacity(0L);
        playerGame.setEndDate(null);
//        playerGame.setGame(game);

        final var savedPlayerGame = playerGameService.save(playerGame);

        AsyncRunner.runAsync(requestId, () -> {
            final var gameToSave = gameService.findById(gameId).get();
            gameToSave.addPlayer(savedPlayerGame);

            playerGame.setGame(gameToSave);

            playerGameService.save(playerGame);

            final var savedGame = gameService.save(gameToSave, requestId);

            playerPublisher.publishJoinGame(player, savedGame, KafkaOperation.CREATE);
        }, taskExecutor);

        final var response = SinglePlayerGameResponse.builder();
        response.requestId(requestId)
                .data(savedPlayerGame.toShallowResponseData())
                .errors(Collections.emptyList())
                .message("Game Started")
                .code(HttpStatus.OK.value());

        return ResponseEntity.ok(response.build());
    }