I'm having some issues with performance with a certain cron job I have running on my system. The site is a holiday rental site and I have over 290,000 images saved in a database for each rental which I am copying over to my server. What I am trying to do is when I copy over the image to my server I update the DB to say whether it has been copied succussfully or not. Problem is this is causing some memory issues. I'm using stored procedures for security reasons and have tried three approaches now. To call a procedure one by one as I copy each image Try a transaction and create a list of procedure calls and then execute when all the images for one rental are copied to try and keep the amount of connections down Call one procedure for each rental with a delimeted string of images and then seperate within the procedure and update that way All three of these are taking for too long though. All the procedure is doing is checking to see if the record exists and then updating it if it does. Can any one suggest a way that will update the table quickly?
Scratch that, I worked it out. I needed to put an index on the table for the field I was using in the WHERE clause.