37

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.

benjy
  • 4,664
  • 7
  • 38
  • 43

7 Answers7

67

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
Adam Batkin
  • 51,711
  • 9
  • 123
  • 115
  • 2
    As others have stated, "it depends," but in general, Adam is right. If you have any doubts **and** if your application is not performant, profile it. That will show you the bottlenecks, so you don't have to guess. You can always try it both ways and benchmark them (again, if and only if your application does not meet performance specifications). – TrueWill Sep 07 '09 at 22:18
  • 3
    Transactions play a large role in this. If you can turn 5 transactions into 1 you save a lot of time. NoSQL databases get basically all their mileage from exploiting this. But, yeah, when it comes to network comm, prefer chunky over chatty. – micahhoover Jan 15 '15 at 14:06
13

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.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
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.

codymanix
  • 28,510
  • 21
  • 92
  • 151
5

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

Dawie Strauss
  • 3,706
  • 3
  • 23
  • 26
4

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

DigitalRoss
  • 143,651
  • 25
  • 248
  • 329
3

DB calls will most likely be more expensive, at least this is what I experienced so far.

Manu mathew
  • 859
  • 8
  • 25
KB22
  • 6,899
  • 9
  • 43
  • 52
1

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

J. Polfer
  • 12,251
  • 10
  • 54
  • 83