How to efficiently manage the processing of thousands of db records in as little time as possible?

Currently I have thousands of records in a mysql database. Each record takes a few minutes to process but even 3 minutes multiplied by 10,000 records could take almost a month to process (i.e. 3 minutes x 10,000 records = 30,000 minutes / 60 minutes = 500 hours / 24 hours = ~21 days) and more records are coming.

In an Amazon EC2 LAMP environment, how can I accomplish this task at a much faster rate?

I could throw more instances at it but that seems inefficient and would drive up cost. I could have more scripts (or browser tabs?) processing the database but with a single database as the source I'm sure to overlap processes? Even if I split the data across databases, I'd still eventually run in to overlap the processes on that database? So maybe I need software to queue up database for each script? Anyway, what do you experts think?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ramromconsultant Commented:
Please describe the processing. Perhaps there is a way to make if more efficient.
Dave BaldwinFixer of ProblemsCommented:
I've never had a process on a row take even a second.  What kind of processing are you doing?
aristanobleAuthor Commented:
Well the length of the process is because of the API call related to that record. The call is for a large amount of date that when returned gets processed also and then on to the next record.
Webinar: Cyber Crime Becomes Big Business

The rising threat of malware-as-a-service is not one to be overlooked. Malware-as-a-service is growing and easily purchased from a full-service cyber-criminal store in a “Virus Depot” fashion. Join us in our upcoming webinar as we discuss how to best defend against these attacks!

Dave BaldwinFixer of ProblemsCommented:
Details are important in analyzing a problem.  But you're not giving us any so i don't know how we can help you.  We're usually pretty good with code but terrible at guessing.
You have to analyse the process.
You have to break it down in steps and assess the duration of each step, and its dependencies.
Only then you will be able to know what you can do about it.
There will be things you can speed up, others you can do simultaneously, and others you can't.

If a woman takes 9 months to give birth to 1 child, that doesn't mean 2 women can do it in 4.5 months...
aristanobleAuthor Commented:
Sorry I'm just holding back from being too talkative but here's how it works: each user currently reside in the users table.  As each record is selected, the user id is used in the API call and returns hundreds of records. Each record is then saved in a separate table and then on to the next record.
API call to what?
How much time does it take, that API call?
How is the data from the API call formated, and how are you parsing it?
aristanobleAuthor Commented:
I got a chance to work on all of your suggestions and I was reminded of some things I knew all along; as goes the case at times. I was inserting each record rather than inserting batches of records. Things are speeding up now but I need more.

To answer your questions, I'm pulling date from the Facebook API; by iteration we obtain about 5,000 records in a php array per user and I "was" iterating over the data inserting each record but now I iterate over the data concatenating it into a variable and then I'd insert the data in chunks.
Shalom CarmelCTOCommented:
To truly scale your operation and to be limited by budget (and not by technology) you should change your computing method to that of parallel/grid computing.

I see that you are already familiar with AWS, take a look at EMR, which is Amazon's implementation of Hadoop.

Look here  and return with questions :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
It would sure help us help you if you could post the CREATE TABLE statements for all of the DB tables in question, post the code that interacts with the API and the data base, and post some of the data that your scripts fetch from the API.  Armed with some specific information we may be able to provide some immediately useful suggestions.

One suggestion may be to make the fetch-and-update into an asynchronous process.  I have used this technique when I need to provide rapid response, but also need to get information from an API.  To do this, your script will need to make a POST method request (using CURL or FSockOpen) to the asynchronous script.  You can start the process and disconnect without waiting for it to complete.  If the async tasks takes three minutes, who cares?  You can just start up a lot of them and let them run in the background.
Ray PaseurCommented:
Going forward, please consider the EE grading guidelines.  If you're not going to interact with the experts who are trying to help you, please don't give a marked-down grade.  We need some dialog in order to be able to understand what you're dealing with -- this back-and-forth dialog requires interaction between the participants.  

Thanks, ~Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.