CODEWITHSUNDEEP
postgresql
Mariusz
Mariusz

Reputation: 1985

Multiple joins or multiple selects which has got better performance

Picture below shows my schema.

enter image description here

In my java jdbc application I need select some rows from T1 table. To do that I use where, limit and offset clauses. For each selected row I select row from T2 and row from T3. I consider two possibiliteis:

  1. Use Java to make multiple queries

    select * form T1 where whereField=5 order by whereField limit 5 offset 5
//in java, for each selected row from T1:
select * from T2 where id=?
select * from T3 where id=?

  2. One query to db:

    select * from T1 join T2 on T1.t2=T2.id join T3 on T2.t3=T3.id 
where T1.whereField=5 order by T1.whereField limit 5 offset 5;

I would like to know which way is more efficient. Maybe there is a better way?

Upvotes: 1

Views: 678

Answers (3)

Kuberchaun
Kuberchaun

Reputation: 30324

Ideally one SQL statement would be the best. This requires just one trip to the database and one trip back with the data. Assuming you have proper indexes on your three tables I don't see a three table join posing a problem for any relation database. If you showed me a one hundred table join maybe things would change and multiple queries would be the better approach.

Best Answer: It depends, test both approaches under your setup and see which one yields the best results and go with it.

Upvotes: 1

ndpu
ndpu

Reputation: 22561

3 selects and join by java most likely slower than one select with all joins made in db! SQL databases are optimized for that kind of work.

similar question: Is it faster to programmatic join tables or use SQL Join statements when one table is much smaller?

http://java.dzone.com/articles/myth-slow-sql-join-operations

Upvotes: 1

Kyle Burton
Kyle Burton

Reputation: 27528

The best way for you to determine which is better for your database is almost certainly to use Postgresql's Explain. This PDF is a set of slides that is a great walk-through of EXPLAIN.

The performance will depend on the data in your tables (how many rows have a given key), where you have indexes, etc. Using EXPLAIN will help you understand what the database is doing, how many rows are affected and give you insight into what you can do to improve performance (Postgres configuration, indexes, operator classes, etc).

Upvotes: 1

Related Questions