what is more efficient: repeated call to database or array

cretaceous

I'm sure this has been asked before but can't find answer

I will be adding items to a database from another (access) database on a weekly basis via a script

On insert I store the access id as a foreign id

On following weeks the entire database will be re-exported and new items imported
To avoid items being added again I check foreign id existence

so what is more efficient for the server : check foreign id existence on each item using SQL
or get all foreign ids first, put them in an array (6,000 items) and check the array each time?

I have much the same thing later when I need to check if postcodes are in London - so again I can check db or put them in an array (210 postcodes)

Thanks!

johanafm

If you create your array with the foreign ids as keys, rather than values, you only need to check isset(), which I believe will be the most efficient way of doing this. I.e.

$fids = array();
while ($row = $foreign_ids->fetch())
{
	$fids[$row['fid']] = true;
}

while ($row = $access->fetch())
{
	if (isset($fids[$row['id']]))
		# update
	else
		#insert
}

But if you have a unique index on the foreign id field and use the same prepared statement, this is also efficient. You will however get queries that fail and then you have to check if the error is due to this reason and issue an update query instead.
Or if you use MySQL, you could use INSERT ... ON DUPLICATE KEY UPDATE ...

To be certain which is more efficient, you should profile both ways of doing it. If PHP server and DB server aren't the same, it's also a question about which machine you want to put the load on.

cretaceous

thanks for that - I'll go with foreign key as the index on the array - good tip!

(and now I've discovered the imported file has duplicates, same person but a few details different - aargh.. bashes head against wall...)