Is it faster to query a List<T> or database?

Question

I have recently had several situations where I need different data from the same table. One example is where I would loop through each "delivery driver" and generate a printable PDF file for each customer they are to deliver to.

In this situation, I pulled all customers and stored them into

List<Customer> AllCustomersList = customers.GetAllCustomers();

As I looped through the delivery drivers, I'd do something like this:

List<Customer> DeliveryCustomers = AllCustomersList.Where(a => a.DeliveryDriverID == DriverID);

My question: Is the way I'm doing it by querying the List object faster than querying the database each time for customer records associated with the delivery driver?

Answer 1

There isn't an accurate number for amount of rows that if you pass it you should query the DB instead in in-memory List<T>

But the rule of thumb is, DB are designed to work with large amount of data and they have optimization "mechanisms" while in in-memory there aren't such things.

So you will need to benchmark it to see if the round-trip to DB is worth it for that amount of rows for each time it's important to you

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil"

Answer 2

Avoiding round trips to DB is one of the major rules regarding database performance tuning, especially when the DB is located on network and has multiple users accessing it.

From the other point of view bringing large result sets into memory like you customers data looks to be, is not efficient and probably not faster than traveling to DB when you need them.

A good use of in memory collections to avoid round trips is for your look up tables (i.e. customer categories, customer regions, etc), which don't change often. That way you avoid joins in your main customer select query making it even faster.