Which is more expensive? For loop or database call?

Question

In general, which is more expensive? A double-nested for loop and one call to a database or a call to a database for each of N items in only one for loop?

Not looking for an answer down to microseconds, just a general idea of which direction I should take.

TIA.

Answer 1

In general, the fewer times you hit the database, the better. There are a number of reasons for this, including:

• The database will be able to optimize better if it can try to fetch everything all at once
• You remove all of the overhead of communicating with the database multiple times, which can be quite significant

Answer 2

Just takin a shot: the DB calls will most likely be more expensive. At least this is what I experienced so far...

Answer 3

In general, the less you access the database the better.

Answer 4

In general, anything done in memory (for loop) is faster than the same thing done over a network (database call). However:

for i = 1 to num_users
    get user from database
end

will be slower than

get users 1 to num_users from database (in one query)

because it's the number of times you ask the database for something that really matters.

Answer 5

If you are going to process every item, just make one call unless it would use an outrageous amount of memory.

Answer 6

I think it depends upon what you are doing. There isn't enough information in your statement.

The above answers are true, the less you access the database the better (usually). So you should try to do a specific operation in as few database calls as possible. The only exception to this, I would think, would be in cases where your application is faster than the database, perhaps in doing complex data transformations, or in using a very inefficient database.

Usually, letting the database do data transformations in set form is usually faster than doing them programmatically using a cursor in a for-loop. If this isn't something your'e familiar doing, I suggest learning a bit more SQL or getting a good how-to book like SQL Cookbook (note: I'm not affiliated with O'Reilly, that book was just extremely helpful for me.)

Answer 7

This greatly depends.

A nested loop will be much faster than a database call if you just have a few hundred items. A database call involved usually a data transmission over lan or worse, internet. the query has to be parsed every time and so on.

But if you have thousands or millions of items which are searched through in one database query then the sql query will be lots faster, since database systems are highly optimized to handle huge amounts of data. But do not forget to create indices for your tables.

When in doubt, you should measure the time it takes for each method, and if it just gives you a better sense on how performance behaves.