List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:May 21 2004 1:21am
Subject:Repeat loops in mysql, large data issue, suggestions wanted
View as plain text  
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
scott@stripped                            Novato, CA U.S.A.


Thread
Repeat loops in mysql, large data issue, suggestions wantedScott Haneda21 May
  • Re: Repeat loops in mysql, large data issue, suggestions wantedDavid Griffiths21 May
    • Re: Repeat loops in mysql, large data issue, suggestions wantedScott Haneda21 May
      • Re: Repeat loops in mysql, large data issue, suggestions wantedDavid Griffiths21 May
        • Re: Repeat loops in mysql, large data issue, suggestions wantedScott Haneda21 May
Re: Repeat loops in mysql, large data issue, suggestions wantedbeacker21 May
  • Large sample data sets for testingGreg Willits21 May
    • Re: Large sample data sets for testingDan Nelson21 May
    • R: Large sample data sets for testingLeonardo Francalanci22 May
      • Re: R: Large sample data sets for testingmos22 May