Looking for optimised method for upload

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Looking for optimised method for upload

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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);
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
Post Reply