Faced with some larger than usual for me data requirements, I thought I would ask some others what they think about my logic.
MySql 4 In short, I will have a file upload on a web server that will digest a file, I will be able to dictate the format of this file. There may be a few formats, the main one will be: data\tdata\tdata\r So, that is some data, a tab, some more data, another tab, then a return. My trouble is that the data file could be 100,000 lines in length, I have a few options: Iterate through the file, one line at a time, using insert delayed I can put the data into a table with no trouble, this is the simplest method, but perhaps has performance issues. In any language, repeating 100,000 times will take some time, of course, it will happen in the background, but it still will take some time. There is also the trouble with a crashing server, I would have a incomplete set of data inserts, and no real simple way to deal with this. I was thinking, perhaps MySql is up to this task, what if I were to insert the entire file into one field in mysql, then I have all the data in mysql, this insert should happen much faster, as it is just data, it could be a few MB's in size, but still should not take too long to get it into MySql. It is also only one operation, so the chance of a server crash interfering is less. Is it then possible to have a SQL statement that would somehow take the lump field data, select it, chop it up, and insert 100,000 rows, in this case, three fields per row? I was thiking I could easily do some simple string replaces on the data and get it to one large INSERT statement, this is not all that atractive to me as I am not 100% certain each line in the file would be in the correct format, someone on accident may have put in 5 tabs on one line. In order to check for this I am back to repeating through each line server side and testing the line for integrity. Any ideas and thoughts? MySql 4 -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
