Vapor - Query Optimisations

The database used by a Vapor application in a production environment may not necessarily be on the same hardware as your application. It may be deployed on a separate database server, a different virtual machine/container or on a different disk in the connected storage area network (SAN). Hence each round trip to the database would have some additional latency and your network speed may be affected by other factors like network congestion etc. This becomes more prominent when you want to improve your application's performance especially as your application load grows.
With this in mind, we would like to keep our database calls to a minimum whenever possible.

Some such cases where we would like to exercise this optimisation are when:

Fetching Parent with Children

When fetching a collection of parent objects with all their children objects (one-to-many relationship) we want to avoid N+1 Selects (explained below).

Let's say we had Car objects (database rows) and each Car had a collection of Wheel objects (in a one-to-many relationship) and we wanted to get all the details of cars below a selected price along with the details of their wheels in a list

We could fetch each car and then its individual wheels as below

var carsWithWheels: [CarWithWheels] = []
return Car.query(on: database)
    .filter(\.$price < selectedPrice)
    .all()
    .map { cars in
        for car in cars {
            _ = car.$wheels.query(on: database)
                .all()
                .map { carWheels in
                    carsWithWheels.append(
                        CarWithWheels(car: car, wheels: carWheels))
                }
        }      
    }
    .map { _ in
        return carsWithWheels
    }

This would generate one database query for the Cars, and then N additional select queries in each iteration of the for loop to get the wheels of each car, where N is the total number of cars. This is known as the N+1 Selects issue

We can simply avoid these additional N roundtrips to the database by eager loading the wheels of each car along with the car using a join on it by adding with children at the time of fetching the data as below:

var carsWithWheels: [CarWithWheels] = []
return Car.query(on: database)
    .filter(\.$price < selectedPrice)
    .with(\.$wheels)    //The wheels child objects are eagerly loaded here
    .all()
    .flatMap { cars in
        for car in cars {
            carsWithWheels.append(CarWithWheels(car: car, wheels: car.wheels))
        }
        return req.eventLoop.future(carsWithWheels)
    }

This reduces the database calls to a single call with much cleaner looking code

Multiple Inserts and Updates

While performing multiple inserts and updates we would like to reduce the database calls by performing all of them together when possible.
Vapor allows up to do this using Fluent's Batching
If we wanted to create a list of cars in a single batch

let cars = [bmw, tesla, mercedes]
cars.create(on: database)

Large and Complex Data Operations

When dealing with large and complex data operations (like generating an annual report) we would like to perform data intensive operations as close to the database as possible. Stored procedures offer us this ability along other performance benefits and would be an ideal solution in such a case.
Using a stored procedure would also allow us to limit the number of database calls.
To call stored procedures with Fluent we would need to use the raw SQL calling capabilty by mapping the stored procedure's data to a Domain Transfer Object (DTO) and using the data from there.


References: The N+1 problem