time out problem PLEASE HELP!

[lkruitwagen]

Hey, This is driving me nuts,

I've written programming which reads from a file and then inputs that data into a SQL database. I use a two dimentional array to hold all my data. the script works perfectly with small test sections of the file (about 9000 lines), but when i execute it with the entire file (almost 200,000 lines), I get a time-out error. I increased the size and time restrictions, so now instead of a time-out I get an internal server error after about 15 minutes. any ideas? Is the 2-D array slowing it down perhaps?

[ChaosXero]

15 minutes?! What are you uploading through a PHP script that is taking 15 minutes to process? 
Have you tried breaking it into smaller files and passing everything around? Or using functions? Or any number of things to make the script a LOT smaller?

[lkruitwagen]

unfortunately the file size cannot be reduced. the users will be people other than me, so they just need to upload their file and go
I'm trying to reprogram it so it doesn't use 2-d arrays. I've made some of the algorithms a lot simpler too. and I will write some functions in, those are just like procedures right?

Thanks a lot!!!

[mainewoods]

I assume the data uploaded contains multiple mysql records.  do you write all the records to mysql in one big sql statement, or do you loop and write a record at a time?

-

[mainewoods]

-The best way is to loop through the data uploaded, creating one huge sql string in the middle of the loop, and then when the loop terminates, submit the whole thing to mysql at once.  That will run way faster!

[lkruitwagen]

yeah well before i had it in a FOR loop but for some reason that didn't work. I've gone back and changed the FOR loop to a WHILE loop and it is working now. I think this is because in the FOR loop it was just trying to execute the whole loop, where as the WHILE loop requires it to continue and continue. I also made it print the increasing integer to the browser so the browser page is kept busy. It isn't fast, but time was never really my problem, I just needed it not to time out. I'm sending millions of data entries so I would expect it to take a while. I'm inputting the data one row at a time, if that's what you mean, in long Query statements.

Thanks for all the help!
Problem Solved!

[448191]

[quote author=mainewoods link=topic=101384.msg402535#msg402535 date=1153748828]
-The best way is to loop through the data uploaded, creating one huge sql string in the middle of the loop, and then when the loop terminates, submit the whole thing to mysql at once.  That will run way faster!
[/quote]

I had a similar thing a while ago. I had a file containing all zip codes in holland (over a 100.000 entries) and had to enter them into a table. Looping through the file, inserting every line, took several hours.

I didn't know you could do multiple inserts in one query.
[i]INSERT INTO beautiful (name, age)  VALUES  ('Helen', 24),  ('Katrina', 21),  ('Samia', 22),  ('Hui Ling', 25), ('Yumie', 29);[/i]
I'm not that sure it'll be that much faster though.

Another lesson learned.

[ryanlwh]

you could also write a file in a certain format, then have mysql read it with LOAD DATA INFILE and such. it's a very fast process. i've been using this method for awhile, and it can read 200k rows in two minutes.

[448191]

[quote author=ryanlwh link=topic=101384.msg404008#msg404008 date=1153933060]
you could also write a file in a certain format, then have mysql read it with LOAD DATA INFILE and such. it's a very fast process. i've been using this method for awhile, and it can read 200k rows in two minutes.
[/quote]

http://dev.mysql.com/doc/refman/5.0/en/load-data.html


It comes a bit late for me, but that is absolutaly great. Might come in handy again sometime.
Thanks for that! Very "cool". :P