Which is more efficient, perform multiple queries or use JOIN?

Asked

Viewed 999 times

3

I have a table X that has my user data, and I need to return to the client the data related to that user in the table Y and Z. I can do it using JOIN or:

SELECT attr1, attr2 FROM Y WHERE X_id = id
SELECT attr1, attr2 FROM Z WHERE X_id = id

Which is more efficient?

sql database query performance join

  • I’ve done a consultation of these in a very specific scenario that got faster being multiple selections. He was doing a data structure to optimize the desired rescue, he did not know how to pass to the bank engine the tips for him to do the query quickly (Litebase at the time). After about 3, 4 years, even switching to Sqlite was more efficient separate, but the maintenance was very complicated to prone to bugs. So I put it all together in one consultation.

2 answers

10


Depends on the database, depends on the data in the tables, depends on the table configuration, depends on other specific factors.

It may be the same, it may be that the JOIN be faster because it is better able to optimize, but it may end up doing some operations by having a relationship that would not be done in the separate query.

There is no way to answer that other than to say that you have to measure for your specific case with the current data. And it may change in the future. A database is a mechanism full of optimizations for case.

If I had to kick JOIN will be faster, but I don’t trust kicks.

3

I think the best solution would be to group everything into a single query:

SELECT Y.*, Z.*
FROM X
JOIN Y on Y.id = X.id 
JOIN Z on Z.attr1 = Y.attr1
WHERE X.id = @id

You could remove the table X of Join and use of a subselect, but I believe that the above solution has better performance, since it does not repeat selects for the comparison of X.id.

Browser other questions tagged sql database query performance join

You are not signed in. Login or sign up in order to post.