How slow is a call to a local database?

In general, say you have a (<16mb) table in a database running on the same machine as your server. If you need to do lots of querying into this table (%100 reads), is it better to:

  1. Get the entire table, and do all the searching/querying/ in the server code.
  2. Make lots of queries into the local database.

If the database is local, can I take advantage of the dbms's highly-efficient internal data structures for querying, or is the delay such that it's faster to map the tables returned by the database into my own data structures.

Thanks.

This is going to depend heavily on what kind of searches you're doing.

  • If your data is all ID lookups, it's probably faster to have it in RAM.
  • If your data is all full scans (no indexes), it's probably faster to have it in RAM.
  • If your data uses indexes, it's probably faster to have it in the DB.

Of course, much of the appeal of a database is indexes and a common query interface, so you have to weigh how valuable those are versus raw speed.

There's no way to really answer this without knowing exactly the nature of the data and queries to be done on it. Over-the-wire time has its cost, as does BSON <-> native marshalling, but indexed searches can be O(log n) as opposed to a dumb O(n) (or worse) search over a simple in-memory data structure.

Have you tried benchmarking?