N+1+caching is ok?

Hibernate and iBATIS and other similar tools have documentation with recommendations for avoiding the "N+1 select" problem. The problem being that if you wanted to retrieve a set of widgets from a table, one query would be used to to retrieve all the ids of the matching widgets (select widget_id from widget where ...) and then for each id, another select is used to retrieve the details of that widget (select * from widget where widget_id = ?). If you have 100 widgets, it requires 101 queries to get the details of them all.

I can see why this is bad, but what if you're doing entity caching? i.e. If you run the first query to get your list of ids, and then for each widget you retrive it from the cache. Surely in that case, N+1(+caching) is good? Assuming of course that there is a high probability of all of the matching entities being in the cache.

I may be asking a daft question here - one whose answer is obviously implied by the large scalable mechanisms for storing data that are in use these days.

Comments

[Igor Lobanov]

In my experience, N+1 with caching is okay as long as your cache hit ratio for your "widget" table is good enough. It is even preferred compared to Hibernate's ingenious outer-join fetching machinery.

Moreover, sometimes it is worthwhile to perform "cache warm up" at startup by issuing "select * from widget" to make even the very first query fast enough.