4

This question already has an answer here:

First, I'm still learning to do things in the best possible practices. I would like to know what would be a best practice when dealing with database queries. I've seen codes that query inside the loop to check whether that data exists or not. So let's say I have a set of codes shown below.

// assume that institutions are the list of institutionids
foreach(var institution in institutions)
{
    // get here the project for an institution.
    var projects = _projectRepository.GetForInstutition(institution);

    // do sets of commands here...
}

Why would it be better to do the query for all the data you need from the database before doing anything on the data? I would like to have a better understanding on this.

marked as duplicate by gnat, Andy Hunt, ptyx, Peter K., JeffO Mar 23 '18 at 13:58

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

This is a case of the memory hierarchy dictating the most efficient way to do things.

Computers usually have different types of storage; the largest ones are also the slowest ones. CPU registers are the fastest of all but also the smallest; on-chip cache is larger but slower, second-level caches are bigger still, RAM is even larger, mass storage (like hard drives) even larger, networked devices are essentially unlimited in size.

Usually, the difference in access speed is dramatic. A database can vary between being similar to a hard disk and being similar to a network access, but it is almost always way slower than moving data from and to RAM. Typically, even opening a connection and sending off a query takes so long that you could have performed billions of operations in memory in the same time.

Therefore, as long as all results are expected to fit into RAM, it's usually better to perform one database query to get all the data you need and then start operating on them than to query, process, query again, process again, etc. It dramatically decreases the overall time needed to finish the loop.

  • If all results don't fit into RAM, then you can start pulling in "pages" of results at a time. Unless each record is extremely large though, it's very likely that you'll have a "page size" greater than 1. On the other hand, even if all the records fit into memory, it can make sense to request the data a "page" at a time so processing can be overlapped with downloading. – Derek Elkins Mar 17 '18 at 20:32
  • I have found situations where using multiple queries was faster because I was better off using the first query to build a list of what I wanted and the second to obtain it--I never found a way to make the database calculate the intermediate only once even though it got used twice in the final product. I've also seen it when I need a group of parent records from table A and associated children from a few other tables. Never in a loop, though! – Loren Pechtel Mar 18 '18 at 2:17
  • The database may not even be on the same machine. This may be a network call. – Frank Hileman Mar 18 '18 at 16:44
1

This is the classic space-time tradeoff

Sure, you could suck everything up into your computer's memory like this:

var projectMap = _projectRepository.GetForInstutitions(institutions);
foreach(var institution in institutions)
{
    var projects = projectMap.get(institution);

    // do sets of commands here...

}

I call this slurping. You've already posted the alternative: chuncking. Slurping will reduce the number of requests to the database over chunking. But what does that do to your computer's memory? Well that depends on how big and how much you're sucking up into memory. Pull in enough and suddenly you're slamming the hard drive as the OS swaps memory to the page file because of cache misses.

You have to test stuff like this to see if it's really a problem. You can go wrong at either extreme. The key is to find a balance.

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