Multiple DB lookups VS fetch all + array search, what's more efficient

Question

I'm sure this has been discussed many times before, but for whatever reason I didn't find anything (could be no coffee).

As a design question here's the idea.

One (remote) database looks something like this id|timestamp|log_entry|task_id

These are fetched to a PHP/Codeigniter portal and mapped to a local database task_id|name|...

Now, parsing through the remote data I need to (among other things) get the name associated with each row. The simple way would be to loop through the result, and in each iteration do a lookup (id -> name). But this will generate a lot of DB calls. The other way I see it is to pre-fetch all id-name pairs into an array, and then use that for lookup.

For this specific project I'm not expecting performance to be an issue either way. But which would be the most efficient way to do this?

EDIT: Pseudo code

<?php
// ---------- Multiple queries example ------
$log_result = mysql_query('SELECT id, task_id, log_entry, timestamp FROM remote_db WHERE date=X');

foreach ($log_result as $log_row)
{
    // Get task name for row
    $task_name = mysql_query("SELECT name FROM local_db WHERE id={$log_row['task_id']}");

    // Process this row ...
    echo "Proccesed {$task_name} which occured at {$log_row['timestamp']}";
}

// ---------- Array example -----------------
$task_lookup = mysql_query('SELECT id, name FROM local_db');
$log_result = mysql_query('SELECT id, task_id, log_entry, timestamp FROM remote_db WHERE date=X');

foreach ($log_result as $log_row)
{
    // Get task name for row
    // assume task_lookup[] = array(id => name)
    $task_name = $task_lookup[$log_row['task_id']];

    // Process this row ...
    echo "Proccesed {$task_name} which occured at {$log_row['timestamp']}";
}
?>

Jim · Accepted Answer · 2011-08-02 12:21:10Z

1

If you need all the information anyway then certainly selecting it once and looping over what you need (particularly since the database is remote and the latency of many calls will add up).

Edit: Looking at the pseudo code: You could use the array of ids from the remote DB to narrow the results grabbed from the local db. Something like:

$log_result = mysql_query('SELECT id, task_id, log_entry, timestamp FROM remote_db WHERE date=X');
$task_lookup = mysql_query('SELECT id, name FROM local_db WHERE id IN taskIdsFromLogQuery');

edited Aug 2, 2011 at 12:21

answered Aug 2, 2011 at 8:45

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

danneth Over a year ago

I was leaning towards that myself, but it's good to get a 2nd opinion. A slight miss though. The remote database would be quried only once, then those results would be parsed and matched against a local DB. However there's no guarantee I will need all task -> name rows. Probably I will only use a small subset each time. In the extreme case all log entries will be of the same type. But of course this would mean selecting the same row for each iteration, and that just seems stupid. Thanx for your input.

Jim Over a year ago

I'm not sure I follow. Can't you add a where or group to the initial query so that you are only grabbing rows you will use?

danneth Over a year ago

I will not know which ones I need before I've parse the result from the remote DB. I added some pseudo code to the question

danneth Over a year ago

Great idea using the IN, for whatever reason I have a hard time remembering that option is available

Collectives™ on Stack Overflow

Multiple DB lookups VS fetch all + array search, what's more efficient

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related