CODEWITHSUNDEEP
sqljoin
brook
brook

Reputation: 247

SQL: multiple queries vs joins (specific case)

This question seems to have been asked a lot and the answer seems to be "it depends on the details". so I am asking for my specific case: Is it better for me to have multiple queries or use joins?

The details are as follows:

I need the "tags" and "types" table to get the tag/type-id that is in the product table.

My gut says that if i join the tables i end up searching a much much larger set so its better to do multiple queries, but i am not really sure...

Thoughts?

Upvotes: 1

Views: 1455

Answers (5)

bw_üezi
bw_üezi

Reputation: 4564

If the database is not on localhost but accessed over a network it's better to send one request, let the database do the work and retrieve the data at once. This will give you less network delay. So joins are preferred.

Upvotes: 0

btilly
btilly

Reputation: 46389

Assuming that everything has indexes on the primary keys (you should be doing that), then joins will be very efficient. The only case where joins would be worse is if you had some kind of external caching of query results (as some ORMs will do for you), your products table was much bigger, and you were querying at a sufficient rate to keep the results of the two smaller queries (but not the third) in cache. In that scenario multiple queries becomes faster because you're only making one of the three queries. But the difference is going to be hard to measure.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

The amount of data is too small to demonstrate one over the other, but multiple separate queries will use more with respect to transferring over the wire than a single query. There is packet overhead, and separate data sets risks difference if the data set changes between queries if not in the same transaction.

JOINs specifically might not be necessary, EXISTS or IN can be used if the supporting tables don't expose columns in the resultset. A JOIN between tables that are parent & child, and there can be more than one child to a parent will inflate the rows searched -- not necessarily the rows returned.

Upvotes: 1

Dan J
Dan J

Reputation: 16708

Ask yourself what extra work would be involved in doing multiple queries... I don't know what you need this data for, but I assume you would, at some point, need to correlate the results - match Tags and Types to Products, wouldn't you? If you don't do that with a join, you just have to do it elsewhere with some other mechanism.

Further, your conception of this overlooks the fact that databases are designed for join scenarios. If you perform three isolated queries, the database has no opportunity to optimize its querying behavior across the results you're looking for. If you do it in one query with a join, it does have that opportunity.

Leave the problem of producing a resultset of ~2000 * 10 * 10 records and then filtering it up to the database, in my opinion - that's what it's good at. :)

Upvotes: 3

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

No, join will probably outperform multiple queries. Your tables are extremely small.

Upvotes: 3

Related Questions