I have these classes
class Match
{
int MatchID,
int Team1ID, //used to reference Team
int Team2ID,
... other fields
}
Note: Match actually have 2 teams which means 2 TeamID
class Team
{
int TeamID,
string TeamName
}
In my view I need to display List<Match> showing the TeamName. So I added another field
class Match
{
int MatchID,
int TeamID, //used to reference Team
... other fields
string TeamName;
}
I can now do
Match m = getMatch(id);
m.TeamName = getTeamName(m.TeamId); //get name from database
But for a List<Match>, getTeamName(TeamId) will go to the database to fetch TeamName for each TeamID.
For a page of 10 Matches per page, that could be (10x2Teams)=20 trip to database. Example:
for(1..10)
{
Match m[i] = getMatch(i);
//the team are never going to be repeated, 20 teams spread over ten matches
m[i].Team1Name = getTeamName(m.Team2Id);
m[i].Team2Name = getTeamName(m.Team2Id);
}
To avoid this, I had the idea of loading everything once, store it in memory and only lookup the TeamName in memory. This made me have a rethink that what if the records are 5000 or more.
What pattern is used to solve this and how?
First off… this sounds like you are trying to solve a problem you dont know you’ll have. Unless you know for a fact that hitting the database 20 times per page (which is nothing) will be a problem, stick with what you have for now and address it when it actually becomes an issue.
That said… a couple of approaches:
In SQL, you can do something like this:
select name from teams where id in (10, 15,....)
Even if you do it once per match, thats 10 trips to the db, not 20. That just cut it in half.
Alternatively – and this is where abstractions can bite you – rethink what you are doing. Rather than loading objects and querying their team names, make a single query that generates all/most of the information you’d need to list everything on the page you are displaying (at least as far as the match information is concerned). With some nifty SQL, you can probably reduce the whole thing down to a single query that returns 10 rows (one per match).
3
There are several ways to optimize for what you are doing:
- Query for many, don’t do a
select * from teams where id = ?, do aselect * from teams where id in (?). Your default method’s implementation receives a List and does the later, the overloaded method for a single id turns the id into a List of size 1 and calls the method that takes the List as parameter. From the way I see it, you are doing the inverse. - You might never need ALL of it, you said it yourself, you use pagination, so the max possible is defined by whatever max page size you have.
- But… if you need to request for that many consider taking a look a the Flyweight pattern, where you can store the data in a random data structure of your choosing, and use a single object to access the data with the same interface as you would with N objects.
- Use a model, remember that Model in MVC (if you are using MVC at all) doesn’t stand for database, but just data. A model can act as a facade for your most complex queries and avoid you unnecessary pain in the Controllers (see “fat model skinny controller” principle)
1
I think your problem is not to be solved by a specific pattern, but by the the software design itself.
There are many things that can be done, if your are connecting directly to a relational database, you could fetch the data in a single query, using joins, and in code bind the data where you want. You could also do it in two trips, and look up the objects already loaded in memory. Or also use something like NHibernate, it all depends of your software design and architecture.
About the many records problem, where are you to use these data? It could probably be paged or filtered, and you could load it in chunks from the data store.
In situations like this you should look overall your project and see how situations like this were handled, and try to follow the same pattern, hence improving code maintainability
1