sql join or calling another model?

Question

I have two tables (item and category, I think they speak for themselves) and two associated model objects. I'm facing a design decisions in the function that fetches 1 item from the database. I need this method to also return the category (name, not just id) of the item.

I have two options:

• In the item model, use an SQL join to get the data faster.
• In the item model, call the category model to get the data, for better code.

Which should I do? I need a category model in any case, because I'll also be showing a list of categories, etc.

Answer 1

Without knowing ALOT more detail, I would tend to lean towards the first option if this is a speed critical portion of the application. Just make sure the code is well commented. If you have the speed to play with and there isn't too much difference in the time between the two executions, I'd do the second. Because when you come back to it in two years, it'll make a lot less sense why the item model is pulling category info otherwise.

Answer 2

Question i've faced too... In general I've found that if you're looking for performance you should go for the join. If speed is not an issue, go for the second one.

Answer 3

if you are going to get the categories anyway, then why not save/cache it and use the same object to lookup the category names instead of an sql join. in that case the argument that the sql join would be faster is now negligible to a degree

unless it is a huge amount of data where speed is critical, then i would probably go with whatever method you are most comfortable maintaining.

Answer 4

You can always optimize later if you need to. In many cases, the overhead of the second select will be negligible.

Your time isn't so negligible. Nor is the time of whomever (you? someone else?) maintains the code.

Code it cleanly, call the category, and if it turns out to be "too slow", then change it.

Premature optimization is the root of all evil.