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