Array's vs. DB Queries - Memory

[mithril]

I'm in the process of adapting a script which recurses through a directory structure, reads the ID3 tags from any MP3's it encounters, and inserts the info into a db. The catch is I'm going to be comparing the artist name, track title, and file path against values already in the database to prevent multiple entries.

My question involves which of the following methods is faster and more memory efficient, taking into account the following:

• the script can be interfacing with either mySQL or MS Access
• the number of records returned contained in the table could easily be in excess of several thousand.

Method 1)

Execute a simple select query for each file found using the artist name, track title, and file path as where clauses to verify that the file isn't already in the database.

Method 2)

Execute a broad select query which return all table rows into a 2-dimensional array and then iterate through the array for every file found, breaking out of the loop if a match is detected.

For a small number of files and records method 2 seems to me to be the best plan, however I'm wondering what the impact of comparing thousands of files against thousands of records in this fashion. Any thoughts?

TIA,

Geoff A. Virgo

[[deleted]]

Method 2 is definately a no-no, never do, don't, bad bad naughty bad, why use a database in the first place, bad naughty bad.
Yes it could be faster if your table is only a few rows, but you can't do this with 100k records. That alone means you can't use this method.

Method 1 does do a lot of queries, but they are very fast "will return only zero or one row" queries.
With proper indexes those queries shouldn't take more than a fraction of a second (in MySQL. MS-Access is... well let's not talk about MS-Access asif is were a database)

From a memory-usage point of view, Method 2 will use more memory than method one as soon as you have 2 rows in your table.
In PHP method 2 will store irrelevant data (every row that's in the table and is not found in the directories was in memory for no reason)

In the database the query to get the array definately uses more memory because it allways returns all rows where method one cannot return more than one row.

Note:
There is one thing you should allways remember, a database is there to manupulate data, to find rows that you are looking for.
Method 2 takes all that functionality away from the database and reduces it to glorified flat-file, which means it's just generating overhead.

[mithril]

Well, I'll take this as a resounding vote for Method 1🙂 Thanx for the input. The app this script will be added to is actually a stand-alone written with php-gtk, so MS Access support is included since MS Access is a much more common beast on WinDoze boxes. Ergo, MS Access support is enabled for those folks running Doze and without mySQL. Even though we're not going to talk about MS Access as if it were a db, do you have any idea what the default timeout for persistent connections opened through odbc_pconnect() is? Would be nice to write an event handler which re-creates the connection after a specified period of inactivity.

Cheers,

Geoff A. Virgo

[[deleted]]

pconnect through odbc? Never done that, look it up in the manual.
There's probably a setting in the php.ini

Don't try to keep the connection open manually, let PHP open/close it when it needs to.

[mithril]

Nope, neither bit of explanation in either place🙂 Not really a big deal though. The thing is, with php-gtk you can let php open the connection but php won't close the connection until the program is exited.

In php-gtk, php acts as a cgi process, but the first thing you need to do to get the program up and running is call the gtk::main() loop. Since php gives control over the program to gtk, the script never really finishes (until the proggie is closed of course). Thus you either need to manually call mysql_close(), odbc_close(), etc. or you're going to end up with lots of orphaned connections. It's much easier to open a persistent connection (which works since the script execution never ends) and then pass that link identifer into all your functions and classes. Since the app I've written is basically an mp3/dvd/cd catalogue, every operation requires the database connection.

Do you know if is_resource() will return false on a timed-out persisent connection?

Thanx!!

Cheers,

Geoff A. Virgo