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.
New contributor
bovus is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 6
    What does your profiler tell you is better? With code and db configuration, possible latency or load concerns, and the possibility of having auto-scaling services or something... it's not set in stone on what's "more expensive". It's also not worth optimizing little details like this for performance unless you've profiled and know it's a big pain point. Stay consistent about it for readability and such (write standards), but we can't give you a determined answer - it could go either way. – Delioth Nov 7 at 22:07
  • @Delioth that'd make a good answer :) – Morgen Nov 11 at 4:15
up vote 2 down vote accepted

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

  • Great answer, thanks! I really like the point "let it do what it was designed for". Though I'm not sure about your assuming #5, that's not a typo? (less->more) – bovus Nov 8 at 1:58
  • As for number 5, data is read from disk very fast when the entire table is being read because it is read in blocks. When you insert entries into an array, the array cells may not always be contiguous, it depends on how the array memory is allocated in the specific programming language. – NoChance Nov 8 at 2:08
  • Thanks for clarifying. I'm curious, what if the animal data was coming from another data source such as a web API, would you still make two requests? – bovus Nov 8 at 2:50
  • In case of web API you have more chance to perform parallel processing. The concept of letting layers do their work reduces program complexity regardless of the component. Now the fact that you are using a web API does not change the 5 risk factors listed above since we don't know how the web API is getting the result. Chances are there is a slight extra overhead in invoking the API twice, which may not be a big deal but depends on the configuration of the environment because unlike db calls, there would be no pooled connections to the API. – NoChance Nov 8 at 2:56

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.

  • 1
    In the example, there are only two types and it is required to retrieve the entire table – bovus Nov 8 at 2:05

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.

  • This is a good possibility Multiple Result Sets but this would probably be equivalent to 1 large query in terms of the amount of data returned to the client and hence may not be all that different. – NoChance Nov 8 at 16:15
  • This is basically like a UNION query. The only thing you are saving is the second round trip back-and-forth to the database. In the .NET stack, most database drivers thrown an exception when attempting to run more than one command. – Greg Burghardt Nov 8 at 16:27
  • @GregBurghardt Hmmm. I do this all the time in .Net. Never had a problem with it. – Becuzz Nov 8 at 17:21
  • @NoChance The total data returned should be the same size. The difference is that I'm going to bet that the DB engine is better optimized for sorting or filtering those queries than anything I write. – Becuzz Nov 8 at 17:23

Your Answer

bovus is a new contributor. Be nice, and check out our Code of Conduct.
 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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