upload csv extract to database - server timeout

Posted on 2011-05-11
Last Modified: 2012-05-11
Hi All

I have a system where the client needs to upload a csv to their website (approx 4mb) the contents of which are extracted and placed into a mysql database.

I have created a script to process the csv data  and separate it into 3 separate tables.

The script and the upload is working fine, the problem I have is that when the files is c. 4MB it is taking a long time to process (c. 4000 rows in the csv converted into 1 table with 3960 rows, another with 40 rows and the 3rd with 200,000+)  and the browser is timing out.....

Clearly the big problem I have is the 200,000+ records... I am inserting each one individually.

INSERT INTO table(field1,field2,field3) VALUES(value1,value2,value3);

Is there a better way to do this?


Question by:cycledude
    LVL 18

    Assisted Solution


    Author Comment


    Thanks, I have though of using that but the problem is the CSV file is 4040 rows by 130 columns

    the data is spilt into 3 tables

    table 1 has 4 cols and c. 4000 rows
    table2 has 4 cols and c. 40 rows
    table3 has 4 cols and 200,000+ rows

    Table3 contains 120 rows for each entry in table1 and table2, the data coming from 120 cols of the csv.

    LVL 18

    Expert Comment

    First load the data into a single table, then move it around to the needed tables using SQL statements.  If you need something more complex some programming will be required.

    Author Comment

    so it will be faster to manipulate the database than csv?


    Author Comment

    how about something like

    INSERT INTO tablename VALUES ( 'Bill', 29, 'Ford' ), ( 'Mike', 16, 'Beetle' ), ( 'Alisa', 36, 'Van' )");

    how many 'values' can you put into a single sql statement?


    Author Comment

    I will need approx 120 sets of values.... can it be done this way?

    Accepted Solution

    welll I tried it, and my word did it make a difference!

    I juust created a variable to store all of the ( 'Bill', 29, 'Ford' ) values for each insert, so 120 per insert...

    massively quicker than my original single insert per value.

    LVL 82

    Assisted Solution

    >>it is taking a long time to process
    You are probably doing an insert per record. You can try multiple rows per insert.

    >>...and the browser is timing out
    at the beginning of your script put set_time_limit(0)

    >>the data is spilt into 3 tables
    OK, then create a txt file for each of your tables then do:
    LOAD DATA INFILE 'tableX.txt' INTO TABLE testX

    for each of the files

    >>how many 'values' can you put into a single sql statement?
    I have not seen any imposed limit on this. However, you need to take into consideration your system resources.  If you are inserting "small" amounts of data (like age, status, firstName,lastName as opposed to paragraphs and paragraphs of info per record) then somewhere between 100-200 per insert might speed things up.

    Author Closing Comment

    I was able to solve this one by merging the numerous inserts into a single insert, which massively reduced the time taken to perform the update.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now