If you are talking about huge data, you should consider using BTree data structure (or its variants). BTrees are used by most relational databases for indexing huge data.
You can check B-tree on Wikipedia for more details.
Based on the amount of data you have to deal with, your objective functions vary. For example, if you have to deal with very huge data, your objective function is to optimize (minimize) the number of disk reads(/seeks). In case you have to deal with small set of data (that can fit in your RAM), your objective function is to optimize (minimize) the number of CPU cycles.
is there a pattern of "prequering" data from database, putting it into memory?
Yes, it's commonly called "caching" and it's used for this purpose.
when the car has hundreds of orders ... this query must be performed many many times.
You are running into possibly the most common performance problem encountered by programmers using an Object-Relational Mapper like Entity Framework, called SELECT N+1. In short: this is when the number of queries your application executes grows linearly relative to the number of records in your database (or some subset of it).
The best way to avoid SELECT N+1 problems is to produce better SQL, either by directly writing a view or stored procedure in the database, or by changing how you're using the ORM so it generates better SQL.
In Entity Framework, navigation properties make it difficult to fully appreciate when the code is hitting the database. My personal preference is to never use navigation properties in Entity Framework. They introduce loads of issues and this is one of them. I want my ORM to be a way to think in SQL while writing in C#, but navigation properties seem specifically designed to hide away all that "scary" SQL and keep you thinking in C#, which makes it very hard to contain problems like SELECT N+1. Instead of using navigation properties, compose your IQueryable
objects as needed in LINQ.
If you are committed to navigation properties, then your next best bet is to use Include
to eagerly load related entities into properties you know you're about to traverse, as explained on MSDN.
What you are proposing is in-memory caching, which may or may not be a great solution depending on your constraints. The main issues to consider with caching are:
Keeping the data fresh. Make sure your cache is expiring more frequently than your data is changing, or you can end up using outdated data. If you're caching in preparation for one data processing cycle and then throwing the cache away, this is no problem. If your data is unlikely to change during application runtime (e.g. unit conversion rules), you can get away with a long expiry. If this is primary data that your users are continually editing, this is probably not the right solution.
Not querying more than you need. It's generally very hard to restrict the query that populates the cache to only the data you need, without directly tying it into the primary query (and then why not solve the problem with LINQ?), especially if you're trying to re-use the cache. If the amount of possible data you'll ever get at once is reasonably small compared to your memory, that is no problem, but make sure you aren't just guessing about that.
As long as you've considered the above points, using a cache is certainly better than tolerating SELECT N+1s. As common as they are, I've never seen a situation where a SELECT N+1 couldn't be refactored into some constant number of queries relative to the number of records in the database.
Best Answer
I'd vote for "leave it server side".
One advantage to storing the data server side is that you get the same favorites whether you're connecting from your phone or tablet.
When I used to program for mobile, I tended to treat the entire device as a Presentation layer. For example, if you had 1,000 "favorites", I might send only a couple hundred names of favorites in the initial HTTP call.
As you scrolled, I'd send down more names. I wouldn't send down the details until you either stopped scrolling for a second or you actually selected a single favorite to view. (If you stopped scrolling I might download the details for the dozen or so favorites currently visible on the screen).
Of course, this only works if you have a reliable network connection. If you have to function offline, then your only choice is to cache the data locally, probably in core data.