When to load related database data in web request?

Question

I'm trying to determine when a web application should query a database for related data that may or may not be used in the current request.

As an example, consider a database that tracks assets for a company. There is an Assets table, an Owners table, and a Users table. Assets may be assigned to an OwnerID, and Asset records will track the UserIDs that created, updated, and deleted the Asset.

When doing web requests for the data, I see two main options:

1. Read all of the data in one request, with a join to Owners and multiple joins to Users (user created/updated/deleted). While this makes for a heavier query, it would reduce repeated calls to the database. It also simplifies the use of OOP within the application, as every entity will be fully populated and can be accessed easily, e.g.: Asset.Owner.Email, Asset.LastUpdatedBy.Name, etc.

2. Only read the data from the Owners table up front, and create separate database requests when the application requires any Owner or User information other than the ID. In about half of the HTTP requests, no extra info will be required, and in the other half of the HTTP requests, the application will need to query the name, email, etc. of the Owner and each of the users who created, updated, or deleted the asset. I like the idea of the lean initial request, but I worry about performance when doing an additional handful of requests to get the related data. It also complicates the OOP design in the application somewhat; instead of being able to simply retrieve Asset.Owner.Email, I'll have to first check if the Asset.Owner object has been populated, call Asset.LoadOwnerByID(Asset.OwnerID), then finally access the value. (Ignore the fact that Asset shouldn't have a LoadOwnerByID method - that's just an example.)

Now that I've written it out, I'm leaning towards Option 1 as being the most efficient and simple way to do things. But it gets a little less obvious when you add more recursive relations to the data, e.g.: Each user has a ReportsToUserID value pointing to another User, and each Owner has an AccountManagedByUserID field pointing to a User.

In these examples, what is your preferred way to do things, and what have you found to be the most efficient, both in terms of performance and programming complexity?

PS - I'm intentionally leaving out specifics on web framework and database type because I have had this question come up in all environments, from ASP.Net/MSSQL to Java/SQLite to PHP/MySQL, and I believe the principles will generally apply to all of those environments.

Answer 1

I'm trying to determine when a web application should query a database for related data that may or may not be used in the current request.

For such general questions it's hard to give a general answer.

You have to consider two points: convenience and performance.

Many things, which seem at first sight convenient are at hindsight performancekillers. Whether it's one or the other - or maybe neither, depends on the amount of data in the DB, which is releated to the lifecycle of an application.

If you start out with an application, you oftentimes have near to no data. So to minimize the roundtrips to the DB you catch as much data as you could with one request. You want to present rich data fast.

With small datasets the resultset is small, the queries - though complex - are fast enough, Joins seem cheap.

That changes with time. Tables will contain lots and lots of data. Big queries will become costly and you have to break up your concepts. Sometimes even it would be best to avoid database joins and do it in main memory.

From my perspective the question - as asked - is of a rather philosophical nature.

But nevertheless there are some rules of thumb one could give:

1) Always retrieve as less data as possibly needed. This is some kind of YAGNI (you ain't gonna need it). Even if it sounds nice to have the data at hand: if you (the user) do not need it, it's pointless to query for it

2) Measure your queries. Despite the previous point: if your application is fast enough, why worry? There are several measurepoints:

• UX - sometimes only psychological: Does you user think or perceives the app as fast
• How fast is the network between the user and the datacenter?
• Is your database delivering data really fast? Are queries best effort?

As long as your users feel snappyness, the connection is pleasant and your queries are fast there is no problem in querying as much data as you could.

Depending on this rudimentary metrics you could evaluate your two solutions:

If you have small datasets, a fast DB, a good to medium connection, you could query as much data as you can get, which would result in a super fast app. On the other hand. If that is the case, there would be no noticeable difference to the second option, to query data when needed.

But beware of changes:

• if the datasets grow big and the connection stays good, option 2 becomes more attractive.

• but suddenly your user is on mobile - which is today oftentimes a horrible experience - one big costly query could be more attractive, although all it's disadvantages: the user could perhaps not rely on a stable connection - and you are able to deliver a nice UX with all data received.

tl;dr

When to load related database data in web request?

It depends.

Answer 2

In your DAO layer, you should have a way to mark a relationship as required or optional (terms may vary based on whatever framework you use).

Required relationships mean that if A links to B and I query A, the web service must return B as well. Use this for data where the two objects are always used together.

Optional relationships mean that if A links to B and I query A, the web service will not also return B. If the consumer of the service wants B, it will send another query.

This should strike a good balance. Consumers that need little data get little data in a single query. Consumers that need more data still have to perform fewer queries due to some queries returning multiple records.

This really helps because once you have recursive relationships, object graphs can grow to obscenely large sizes even if you detect recursion and use the existing value (e.g. A -> B -> A just queries A and B and sets their links to each other rather that requerying A). I have seen services return 50+ objects for a single request, and most of them were not needed.

Bonus points if you put dead reference detection in. A links to B, but the service did not return B. That is, A has the foreign key ID of B, but no B object returned. When you get B from A, something at the framework level detects if it is absent and pulls B from the web service behind the scenes, making client code completely unaware of the two types of relationships.

Answer 3

You should have very specific, semantic requests.

For instance:

You want to pull information about a specific owner

example.com/owner/123

You want to pull information about a specific owner's assets

example.com/owner/123/assets

Now you want to pull all assets that belong to owner 123

example.com/ownerAndAssets/123

Or you want to pull a specific user:

example.com/user/345

You want to pull all user actions:

example.com/user/345/actions

You want to pull the most recent 10 user actions for a given user:

example.com/user/345/actions/limit/10

You want to pull a user and their 10 most recent actions:

example.com/userAndActions/345/limit/10

If you architect the database queries well in repositories, it should be simple to contain all the necessary functionality in simple classes.