Page 1 of 1

Looking for optimised method for upload

Posted: Sun Feb 04, 2007 8:17 am
by amir
Hi all

I am trying to do a bulk upload of a CSV file which contains somewhere around 80,000 records.

So optimized programming is very important here.

The data in the records need to go to two tables. - Table A and Table B.

At first,the user selects the file and upload it to the server using move_uploaded_file() . A sample of the file will be shown
in a grid . Above each column I place a drop down box which contains all the column headings of the table and the user needs to select which column in the grid goes to which column in the database.

Once he clicks the upload button, the data needs to go for insertion. I was able to do the insertion correctly without any issues. But the problem is with the performance. To upload a file of 15000, it took lot of time (after 300 sec, it still only inserted 3000 records). The code I have written is as shown below. The program tries to read each line from the file and then dynamically created the query string and then tries to insert them.

Code: Select all

$dbcon = & new DBConnection();  // DB connection is a class written by me
$dbcon->connectdb('localhost','3306','root','1234,0);
$dbcon->selectdb('emaildb',0);

while( false != ( $cell = $reader->next() ) )
{

if(!$dbcon->insertRecord($insertTable1))
  echo    "<td>". mysql_error(). "</td>\n";
       
else
  { // Now insert the record to second table
    $query = insert into table2(mysql_insert_id(),....);
    if(!$dbcon->insertRecord($query))
    echo    "<td>". mysql_error(). "</td>\n";
             
    else
    {
     echo    "<td bgcolor='".( ( $line % 2 ) ==0 ? '#efefef' : '#ffffff'  )."'>Insertion success</td>\n";
     echo "</tr>\n";
    }
          
  }
mysql_close($dbcon);
}  // End of while loop
My question is, is there any problem with the above code which cause the performance issue? I read about LOAD DATA LOCAL INFILE, but I dont know whether I can use it here, since I need to process the file before I upload them (user may choose not to upload some columns). How about using stored procedures? will it be useful for me in this situation?

I will be running the web server in Fedora Linux OS.

Thanks in advance

Posted: Sun Feb 04, 2007 9:56 am
by onion2k
Lock the table first, use MySQL's extended insert syntax* maybe with DELAYED if the time the records are inserted at isn't important, and don't output anything to the browser until it's finished.

* INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Posted: Sun Feb 04, 2007 10:54 am
by Ollie Saunders
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Yeah if you aren't doing that already you really should.
The only issue is that can cause the formation of very very large queries so limit the number of rows per query to 1000 or so.