Single database call and iterate in memory vs multiple database calls and multiple smaller iterations

Question

I'm designing a new application that is very basic however I expect it to grow and don't want to have to refactor down the line. My challenge is that in one method, I either need to retrieve thousands (e.g. 20,000) of records from the database and iterate through them in memory or, make multiple calls in a loop (e.g. 200 requests for 100 records each).

I saw this similar question that leans on the "the less database calls, the better" line of thinking. Is there an upper limit to how many items one should / should not iterate through in a Windows Application? Could this approach be prone to memory issues that may negate the performance benefits?

This is not limited to a specific scenario and I've had this thought before and have chosen the latter option (multiple requests for smaller datasets).

Am I overthinking this and should I just get to it?

Answer 1

"The less database calls the better" is demonstrably false. Were that the case, the preferred technique would be to load vast sections of the database into the application when the application starts and do most of your processing in-memory, but most applications are not designed that way.

So it comes down to finding the optimum path. In most cases, the way you do that is to measure your performance. Find out what approach is most optimal (in terms of speed, network latency and memory usage), and do that.

For your particular example, you might avoid most of the network traffic and latency by doing your processing on the database server, if that is an option. There are plenty of scenarios where you can write a SQL statement and get your result back without transferring all of those records over the wire for processing.

Answer 2

Generally putting sql calls inside a loop is one of the slowest things you can do.

It's always faster to get all the data first and then loop over it. The obvious limitation being the available memory of the computer running your app.

This can normally be achieved by simply tweaking the SQL into a couple of larger selects. eg

before

select from parent where x
foreach parent
    select from child where parentid=y
next

after

select from parent left join child where x
foreach row
    if parentid != last parentId
          new parent
    new child
next

or a variant with two selects before the loop putting all the children in a hashmap for quick lookup. Which is arguably better if you are caching the results.

I would advocate the use of a repository like so..

var parents = repo.GetParentsWhereX(x)
var children = repo.GetChildernForParentsWithX(x)
foreach parent....

Which gives you most of the performance while avoiding the combined table object.

If you find these kind of selects returning too much data to work with, then you can move to a paged query. But generally I find that a micro optimisation and only use it where you have stuff like bulk data exports.