Which is more efficient (speed / memory): concatenation or multiple allocation
I have the following tables:
users
userId|name
details
itemId|userId|description
What I want to achieve: I want to read from the database all users and their items (a user can have multiple items). I want to store all this data in a structure as follows:
User {
id
name
array<Item>
}
where Item
Item {
itemId
userId
description
}
My first option would be to call SELECT * from users
, partially fill the array with users, and then make SELECT * from items where userId=wantedId
and populate an array of elements for each user .
Is this approach correct, or should I use a join for this?
The reason I don't want to use a connection is because I have a lot of redundant data:
userId1|name1|ItemId11|description11
userId1|name1|ItemId12|description12
userId1|name1|ItemId13|description13
userId1|name1|ItemId14|description14
userId2|name2|ItemId21|description21
userId2|name2|ItemId22|description22
userId2|name2|ItemId23|description23
userId2|name2|ItemId24|description24
redundant I mean: userId1,name1
anduserId2,name2
Is my reason justified?
LATER EDIT: I added speed or memory to the name when talking about efficiency
source to share
You are trading outside the network for bytes on wire and in RAM. Network latency is usually a big problem as memory is cheap and networks are getting faster. This gets worse as the size of the first result set grows - Google for (n + 1) query problem .
I would prefer JOIN. Don't write with SELECT *
; which is a bad idea in almost every case. You should be clear about which columns you need.
source to share
Answer: it depends.
Multiple SELECT:
- If you end up with a lot of queries to complete the description, you should take into account that you end up with many rounds in the database.
Using JOIN:
- Yes, you will be returning more data, but you only have one round trip.
You mentioned that you will partially populate the array with users. Do you know how many users you want to populate in advance, because in this case I would use the following (I am using Oracle here):
select *
from item a,
(select * from
(select *
from user
order by user_id)
where rownum < 10) b
where a.user_id = b.user_id
order by a.user_id
This will return all items for only the first 10 users (so most of the work is done in the database itself, instead of returning all users, discarding everything but the top ten ...)
source to share