Single database query with in-code iteration vs Multiple database queries

Question

Say there is a single database table containing information about two different types of animals: land animals and sea animals.

I want to create two lists of animals, one list for each type of animal.

Is it better practice to query all animals from the database, iterate over the returned list of animals, and place them in the appropriate list. e.g.:

land_animals = []
sea_animals = []

animals = database.query('SELECT * FROM animals;')

for animal in animals:
    if animal.type == 'land':
        land_animals.append(animal)
    else:
        sea_animals.append(animal)

Or, is it better practice to perform two separate queries on the database to retrieve the specific type of animals per list. e.g.:

land_animals = database.query('SELECT * FROM animals WHERE type="land";')
sea_animals = database.query('SELECT * FROM animals WHERE type="sea";')

I believe my question boils down to what is more expensive better practice:

1. An in-code iteration over all animals in the database.
2. Multiple queries to the database.

Answer 1

"Better" could have many meanings. This is a tricky question and the answer would require some assumptions. Anyway, I will throw in my 2 cents...

In your case, and index will not help. The optimizer will probably ignore it since you have 2 types only.

To execute the query on the server, you may consider 3 factors affecting the response speed:

1. Connection time - Can be addressed by having a large connection pool.

2. Query execution time - Can be addressed in many ways such as proper table design, creating good indexes, etc. But in this case, this won't help. Try not use SELECT * if you could and only get the columns required.

3. Communication time between the client and the server - Can be addressed by making the data transferred smaller when possible (overlaps with 2 also).

From the above, we can say that, to execute 2 separate queries, you will have to:

A - Do 2 connections from the client to the server.

B - Read the entire table 2 times, 1 for each query, since we assume the index will be ignored.

Assuming that:

1. The network will handle any size of data in a linear fashion, and

2. The network can handle the transfer of the big data, and

3. The database server does not drop the connection in the middle (big risks) and

4. The application can hold the entire returned result set in memory,

5. The time required to read the data from disk is less than the time required to insert the data in a program structure such as an array.

Then, I guess filtering the data on the client would be faster from a performance stand-point, because you would use 1 database connection and 1 read of the table from disk by the database, the second read will be from the memory only.

The difference may not be felt unless the table is very large though.

Having said that, I would still try to let the database do the filtering and issue two separate queries. This is because this operation is commonly considered as one of its responsibilities. Also, in some cases, you can do parallel processing, where you run 1 query and start using the returned data while the other query is still executing.

Answer 2

It is better to retrieve only what is required from the database rather than retrieve everything and then filter it down. If your table has millions of rows and land and sea only have a few hundred rows your query is going to return a million rows to be iterated over.

So changing your database query to WHERE type IN ('land', 'sea') and then iterating over it would likely be the most efficient.

Answer 3

How about option 3: Issue one query that will return two result sets. Something like:

database.query('SELECT * FROM animals WHERE type="land"; SELECT * FROM animals WHERE type="sea";')

I don't know what language you are using, but many of the ones I have used can handle multiple result sets from a single query. This way you let the database do what it's good at (filtering very fast) and you don't incur the overhead of two separate database connections.