0

Let's say I have two tables: users and tweets. I want to display a full user profile, and latest tweets by id.

Is it better to fetch them in two separate, possibly concurrent/parallel calls? in JS it would be:

const result = await Promise.all(
    query(`SELECT * FROM users WHERE id = 1`),
    query(`SELECT * FROM tweets WHERE user_id = 1`)
);
const profile = result[0];
const tweets = result[1];

OR

make a one query, simple join and retrieve user data from let's say first element of the array? e.g.

// pseudo code
const tweets = await query(`
SELECT * FROM users
JOIN tweets
ON users.id = tweets.id
WHERE users.id = 1`)

if (tweets.length > 0) {
    const { username, avatar } = tweets[0]; // reading any column to get user data
}

Is second query just wasting bandwidth because the user data is copied over every column?

Somehow it stuck with me that it's bad practice to make multiple sql calls and even in such a simple scenario I don't know whether I am overcomplicating things.

1 Answer 1

0

Two queries to retrieve user profile and tweets, or join and get user data from first column?

Is it better to fetch them in two separate, possibly concurrent/parallel calls?

OR

make a one query, simple join...

If you planned to join the data together outside of the database anyway, then typically it's more efficient to let the database do the join as databases are rather good at determining the best way to process data manipulations.

Conversely, if you planned to keep the data separate on the application, then it may be faster (from the application's perspective) to run two queries in parallel. Especially if you designed the UI of the application to display the data asynchronously, so part of the page starts loading while waiting on the rest of the data.

Your users table likely doesn't have many and large data type fields, so the bandwidth is probably mostly negligible in this specific scenario.

It sounds like your data will be kept separate from each other on the User Profile / Tweets page, so the latter design might be slightly better, especially for a User with many Tweets. But if you start implementing other pages and functionality where you'd need to join the data together (such as a CSV export that repeats the User info with each Tweet row) then it may make sense from a performance perspective or even just a simplified code perspective to do the join in the database layer.

2
  • I've checked mastodon.social, and they retrieve tweets, and user data separately on the client side. I have also checked SQL queries made under the hood in "ghost" blogging platform, and on the author page they also retrieve user data, and posts written by that author separately (but they do it on the server side). However as you said my user table has very few fields such as username, and avatar url so I might just do a one join. Thanks for help :)
    – wefad12292
    Nov 11, 2023 at 19:56
  • @wefad12292 No problem! Yea it just depends, but most times your main performance concerns aren't going to depend on which of these two design patterns you choose. I generally like returning a single, well hydrated object, that can be used for multiple use cases in the application. I find it simplifies the code base and also makes it easier to bind to the same single object from multiple parts of the application such that if one property is changed in one part of the app, it's immediately reflected in all the other parts of the app that object is referenced in, without writing extra code.
    – J.D.
    Nov 12, 2023 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.