Jump to content


Photo

time out problem PLEASE HELP!


  • Please log in to reply
8 replies to this topic

#1 lkruitwagen

lkruitwagen
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 21 July 2006 - 02:03 PM

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?

#2 ChaosXero

ChaosXero
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 21 July 2006 - 02:17 PM

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?

#3 lkruitwagen

lkruitwagen
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 24 July 2006 - 01:11 PM

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!!!

#4 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 24 July 2006 - 01:44 PM

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?

-

#5 mainewoods

mainewoods
  • Members
  • PipPipPip
  • Advanced Member
  • 685 posts
  • LocationMaine

Posted 24 July 2006 - 01:47 PM

-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!

#6 lkruitwagen

lkruitwagen
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 26 July 2006 - 03:58 PM

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!



#7 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 26 July 2006 - 04:52 PM

-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!


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.
INSERT INTO beautiful (name, age)  VALUES  ('Helen', 24),  ('Katrina', 21),  ('Samia', 22),  ('Hui Ling', 25), ('Yumie', 29);
I'm not that sure it'll be that much faster though.

Another lesson learned.

#8 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 26 July 2006 - 04:57 PM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#9 448191

448191
  • Staff Alumni
  • Advanced Member
  • 3,545 posts
  • LocationNetherlands

Posted 26 July 2006 - 06:40 PM

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.


http://dev.mysql.com.../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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users