ColdFusion server crashes at 20k emails

Thanks iSaid for your reply,

I'm sure the memory problems I'm having are due to the repeated inserts into the SQL Server database that I'm trying to do for each mail sent. ColdFusion must be caching the queries. On Monday I'll look into setting some options in the CF Administrator which might prevent ColdFusion from caching (Maximum Pooled Statements, query caching, etc.) However, I think this might be a limitation in ColdFusion 8.0 with repeated trips to the database within the same process. This is all happening within a CFFunction so I'll try to run this from a URL request to see if I have the same issues. I have a feeling that such a large amount of SQL queries will crash ther server regardless.

I've looked into sending a simple command to a stored procedure in SQL Server to do the inserts after the CF process is finished. This will do fine for logging purposes if the process completes OK, however, if the process fails for whatever reason, I won't know what to log because I won't know how far along in the process the failure occured. Also, part of the requirement for this project is to allow for real time monitoring of the emails sent. This way the user can watch how many mails are going out, as they are being sent.

Of course we can always change our requirements for the project and try a different logging solution, however, it would be nice if I could get a real time logging process in place, as each email is sent.

Thanks for any further replies.

Peter

OK, i took out the logging query and it made it up to 50K before crashing.

I knew that taking out the insert query would speed things up a bit, which it did, but what would that have to do with a memory leak?

Does ColdFusion store the text of each query into memory?

Thanks for your help,

Peter

P.S. Also considering a scheduler solution.

You can increase the heap size, but that's more masking the problem rather than fixing it.

Reality is that 60,000 emails is a *lot* to create in one loop, and I'd be surprised if a system ever really worked like that successfully. What I'd suggest you implement is something like we have here - you do one initial database query which creates rows in a messages table. A separate process then goes through said table and processes rows one at a time, changing the row status from unsent to send, or similar. That way it doesn't matter that they don't all send in one loop, it'll just pick up where it left off.

Essentially you need to look into some way of batching the emails rather than sending that many at once. What if someone in your database has an invalid email address which crashes out CF on email 59,999? You now have no choice but to send the whole lot again, which is pretty weak.