What will be faster among SQL Query many times or getting all once in List and process in loop

Question

I have to process almost 300 records of data with records at a time. I want to know which option would be better.
Get all 300 records at a time in a custom object, for example, in C# and process them by in a set of three records based on some key.
OR
Get 3 records every time from database and do some processing, then fetch next 3 records.

Answer 1

It depends.

If you intend to execute one query for every 3 rows, this will probably be slower, but it depends on how much or little memory you have available. If the rows are huge, and you have minuscule amounts of memory available, then perhaps 3 and 3 will run faster, but I doubt you're in this situation.

In any case, my advice is this: Go with the simplest solution to implement. If it turns out to have performance problems (and is the topmost item on your list of things that takes time in this operation), then and only then do you look at how to get it to run faster.

With the class libraries I have, retrieving all 300 into a list would be the absolute simplest thing to do, so that's what I would do.

Note: If the absolutely simplest solution to implement is one that you know have performance problems, step up to the next, slightly more difficult one to implement, and so on. For instance, if I am to sort a huge amount of elements in an array, and had to write my own sorting routine, I would never go for bubble sort, even though it is extremely easy to implement.

Answer 2

Bring all the records and process them is better because this will reduce the time consumed by getting Data Objects.

Answer 3

Or do not fetch the records at all, and do all the processing on the database. This is an old argument about whether the database should be treated as a dumb data store, or whether it is OK to push business logic down into the database (which can give major performance benefits).