CODEWITHSUNDEEP
sqlpostgresqlperformance
ibanezn04
ibanezn04

Reputation: 518

Join or multiple sql queries

I'm wondering what is the most effective(fast and less work for database) way to retrieve data. I have 2 tables:

users

ID | NAME 
1  | John
2  | Mike
3  | Jack

data

USER_ID | DATA
2       | some_data_1
3       | some_data_2

I need to get value by user name and as i understand i have two options:

  1. select id from users where name ='some_name' and then select from data table by id.

or

  1. select id from users join data on users.id = data.user_id where user.name = 'some_name'

Also, i guess it's important to note: those are example of real tables with thousands of rows and few more columns, there is an index on user_id column in data table, I'm using jdbc driver under and it's a network call, and it's postgreSQL if it matters.

upd. Also, any links to researches or any kind of papers/docs/speeches would be very interesting to read

Upvotes: 1

Views: 1535

Answers (2)

Popeye
Popeye

Reputation: 35900

Join is better option as it will give you data in one shot.

If your application is calling the two queries then it will be the waste of the resources twice (your application's resources, network time, DB parses, and other stuff) which can easily be avoided using a single query. Resources will be used only once.

So it is better to use one query rather than two queries to achieve the same purpose.

Go with JOIN:

select id 
  from users 
  join data on users.id = data.user_id 
 where user.name = 'some_name'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

In general, you want to let the database do as much work as possible. In general, databases run on more sophisticated servers and there have been zillions of person-years dedicated to the optimizers and to making the code run effectively.

In addition, there is overhead for each query that you send to the database. The query needs to be parsed and compiled before it can be run. Each query also has back-and-forth communication. So, one query is typically better than two.

The generic answer to your question is to use a single query, the one with the join.

There are some circumstances where multiple queries have better performance than one query. But in your case, you are looking for only one row or a handful of rows. And the database is well-designed for the join. So, use the single query.

Upvotes: 1

Related Questions