Best performance for mass reading from text file

[GraemeD]

Hi there,
This is my first post here but I thought I'd register to ask for advice about a few aspects of a project I'm working on.
The issue I have at the moment is that every couple of days I need to read through a txt file datafeed (some 38,000 lines long) and check through it to see if there is anything new provided, if there is then the script will add the appropriate rows to our MySQL database.

I'm concerned about this simply because my first reaction (the way I've dealt with smaller datafeeds in the past) would be as follows:

1. Get entire contents of database
2. Place into multi-dimensional array for reference
3. Read in datafeed txt file line by line (or maybe all at once to keep the connection light)
4. Compare the line to what is already in the array
5. If it's new then add it to the array and to the database

This way it only performs the queries that are needed however I'm VERY concerned about the memory issues of reading such a massive file into memory and getting like 38,000 rows from one MySQL query and sticking them in an array.

Could anyone suggest the preferred method or handling this sort of operation?
Any help would be appreciated! 🙂

[sneakyimp]

The best way by far would be to empty out this text file whenever you copy files to the db. That way, the txt file will only contain records that must be added whenever you parse it. If you can't delete or empty the text file, you might be able to keep track of the 'last line processed' and only start with the next line when you have to process the file again.

For example, on day N I run my script. It goes to the DB or to some other text file where I have stored my 'last line processed' value. When my script retrieves this value, I can see that $last_line_processed is 38000. I know then that I need to process the text file again, but only starting with line 38001. So I would open the text file, reading each line and I would simply discard the first 38000 lines without even looking at them. I would start with line 38001 and could be sure that it didn't yet exist in the DB so I would simply add it without bothering to check the DB.

This of course doesn't work if, for some reason, you would not want to add these new lines in the text file based on some other db-related reason.