SQL Select + Cron Duplicating

[identiti]

Hey Everyone!

I always check back here for help, really appreciating all your work, and now have a question of my own!

 

I have a PHP cron job that does the following every hour (this gets a bit confusing but I'll try and keep it straight forward):

[*]Selects all the data from an SQL that has not been checked in 2 hours (timestamped at last check), ordered by the time since it was last checked (so oldest get checked first next time)

[*]Then runs a check to an external server of a different value from the SQL, to see if there has been any updates, if so, sends an update to the user

[*]If it has not got an update, simply updates the LastChecked timestamp and moves to the next one

 

Now my problem is this: This takes a damn long time, because that external server takes a few seconds per query.... And this is around 6000 users... So this cron takes each time, over an hour. If it takes over an hour, then it is still running, when the cron for the next hour kicks in. So the same script is then running twice simultaenously. And obviously this gets worse and worse over time.

 

In step one, when it selects all the data, I have a problem because if the previous cron updates the values AFTER the second cron starts and SELECTs, the second cron does not notice the change and simply thinks that the values are the old ones. This causes a problem because it means my users get multiple updates, because when the external server is queried, the local server doesn't realise it's already been checked (due to the values being "old")...

 

I know this is hideously confusing, but what I'm wondering is: Do I have the logic of a SELECT right? Is it banking the values in a temp storage and then going through those, or for each entry is it actually referring to the SQL table?

 

Is there a workaround for this? I've tried set_time_limit(less than an hour) but that doesn't seem to stop the script at all... It carries on through that barrier.

 

Thanks a lot,

Any questions just let me know because I'm sure that nobody understands what on earth I'm talking about!

[cunoodle2]

The cron aspect of opening another server and such has quite a bit of do with connecting times, initializing processes etc..  How about this.  Can you save a list of a series of updates to be performed on a remote server and then open up the remote server less often?

 

Is is possible for you to write all the queries simply to a text file and then send the file to the remote server for a single batch run of sql queries?

 

If there continue to be overlapping issues with cron jobs could you do the following instead of selecting ALL jobs at the same time?..

 

Divide the number of jobs into 10 parts and loop through selecting only 1/10th at a time, doing the processing and then looping back through?  This would essentially take the same amount of item with the exception that you would just have to execute the query 10 times instead of 1.  Most likely the same amount of processing would be required each time. 

 

I love strange challenges like this so let me know your thoughts on what I put down and I'll do a little more brain storming for you.