Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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).

share|improve this answer
    
Your approach looks promising. I could loop through the List<Match>, get the 20 TeamIDs and query the database once for just 20 records using WHERE IN (......). Storing 20 records in memory won't be much. Thanks. –  tunmise fasipe Aug 28 '12 at 11:18
    
I updated my question. There are 2 teams per match which are never the same for the whole 10 records. That is 2x10=20 –  tunmise fasipe Aug 28 '12 at 11:34
    
Like I said, its really not something you want to worry about until you know its going to be a problem. I do exactly this on one of my sites (a fairly high traffic one, at that), and simply doing 20 individual db queries has virtually no impact on performance. –  GrandmasterB Aug 28 '12 at 19:15
add comment

There are several ways to optimize for what you are doing:

  • Query for many, don't do a select * from teams where id = ?, do a select * 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)
share|improve this answer
    
Thanks for your answer. I have updated my question. Paging is not the problem, updating the TeamName for each team in the Match collection is the issue. I used to fetch all records from database but it isn't easy to use a class with fixed attributes as they will change every time I join another table to accommodate more values from DB. Am using Asp.Net MVC, I only want to decorate my view model with the TeamName and let the TeamID be in the Entity class –  tunmise fasipe Aug 28 '12 at 10:55
add comment

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

share|improve this answer
    
Thanks for your answer. I used to fetch all records from database but it isn't easy to use a class with fixed attributes as they will change every time I join another table to accommodate more values from DB. Am using Asp.Net MVC, I only want to decorate my view model with the TeamName and let the TeamID be in the Entity class –  tunmise fasipe Aug 28 '12 at 10:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.