1

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?

3
  • Fetching multiple records at once does not imply that all of them will be in memory at the same time – the database response will typically be a cursor that can advance to the next record, without all the overhead of a separate query. – amon Oct 9 '18 at 17:39
  • 2
    > I expect it to grow and don't want to have to refactor down the line... Refactoring should not be feared. It is what we do when we learn more about the users' needs, or learn a better way of doing something. It is hard to learn enough before writing any code, so, embrace refactoring! – Erik Eidt Oct 9 '18 at 18:10
  • Are users searching for this data? If so, implement paging as no application is going to give back 20,000 records at a time. If you NEED all 20,000 then by all means get them in 1 query. Otherwise, use a paging approach. – Jon Raynor Oct 9 '18 at 20:42
6

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

5
  • Thanks for the insight, the requirements are that I retrieve those records and break them into smaller batches (according to values in the records) and submit those batches to an external API. So your suggestion would be to rather return the batches formatted by SQL? – Daniel Oct 9 '18 at 17:09
  • I think you'll find there isn't much difference in performance between the two examples in your question (since you're going to pull all of the records over the wire anyway), except for the amount of memory used. The way you find out for sure is to measure. – Robert Harvey Oct 9 '18 at 17:52
  • Just because you want to minimize database calls doesn't mean you ignore any other possible issue. Even if you were to load everything in-memory, you'd be doing a call per wanted table. If you somehow merge-join tables to force a single call, then you're going to end up with massive duplication of data. You're effectively taking the statement to the extremes, assuming that anything that is not explicitly mentioned by the statement is equally acceptable even if it's extremely problematic in other ways - which is really stretching the intention of the statement. – Flater Oct 10 '18 at 6:50
  • Thanks again @RobertHarvey I would much rather handle it in smaller batches but will take your suggestion and measure both scenarios and see which performs better. – Daniel Oct 10 '18 at 7:44
  • @Flater it's not so much that I want to minimise the calls to the Database, it's that I want to know, in this scenario, if more or less is better. It appears to be a subjective issue and I will gladly test knowing that neither approach has significant advantages over the other. Thanks for the input – Daniel Oct 10 '18 at 7:46
1

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.

2
  • I think that I need to test this a bit more, my primary concern was that I may be going about this the wrong way altogether whereas feedback seems to suggest that each approach needs to be measured and then a decision should be taken based on the outcome of testing. I have implemented a repo structure although the SQL call in question is a simple select with a single parameter. I was unsure whether doing a preliminary call (to get unique customers for example) and then iterating through that would be better. That seems to similar to your repo approach. – Daniel Oct 10 '18 at 7:49
  • @Daniel I think you will find that robert's answer is plain wrong. Sure it's always good to measure, but taking SQL out of a loop is a standard performance optimisation. – Ewan Oct 10 '18 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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