Suggestions for perf. gain when sending mass e-mails with CDO

[jasonsalas]

Hi everyone,

I've been working with CDOMail and I like it, however, it's been widely debated on its limitations for sending out massive amounts of mail.

Specifically, I'm using it to manage a newsletter system to news headlines, which has about 2,500 recipients to start out with, and grows fairly consistently. I've had past scripts "crash" after working through a few hundred records, wherein the ASP.NET page generates an error of type
SqlException saying the script took too long to process. This leads me to believe because the script it going through so many records, IIS thinks it's
out by doing nothing and eventually times out.

I normally have e-mail addresses stored in a database and loop through them, calling the MailMessage class' Send() method, to send out custom e-mails to each recipient individually. Due to budget constraints, I also can't afford to buy a custom component. BUT, I'm wondering now what would be the best way to work?

In SQL directly, breaking the recordset up into smaller logical chunks:
SELECT * FROM Newsletters WHERE LastName IN (A,B,C,D,E)
// repeat with blocks of letters for last names

...or, send to the entire recordset at once, but use the System.Threading namespace's Sleep() method to delay processing so the script won't appear to IIS to be timing out (maybe add 5 milliseconds for each set of 200 records
in the recordset).

...or, I was thinking of creating a pointer within the recordset, so that it would call/send 200 messages at a time. Thus, it would send to records 1-200 the first pass, 201-400 the second pass, etc. Sort of like custom
paging within a recursive function.

Anyone have any luck with any of these above...or have any better
suggestions?



Thanks,
Jas

 

[dragonwell]

Jas,

I think the problem is that you are staying connected to the database while looping through the records and sending the emails. Essentially you are opening a cursor (datareader?) and keeping it open until you are done. Am I correct?

If that is the case, you should use a disconnected model. Instead of using a DataReader (connected cursor), use a DataSet or DataTable. This is done by opening the connection to the database, running your query and putting your records into memory (the DataTable) and then closing the database connection. Now your connection to the database is closed and you are free to iterate through the DataTable, sending out the email for each record without worrying about anything timing out.

Make sense?

Regards,
David

 

[jasonsalas]

That's a good idea, Dave! I was thinking of something similar to this, by way of using an XML data structure as the product of a DB query, and then reading from the e-mail addresses in the XML file, so the entire process is disconnected.

I actually used a similar approach when I wrote a Web app to covr election night results...having XML data feed a page rather than a live DB hit. We got an enormous amounf of traffic in a very short time, and it worked great.

Thanks for the tip!

Jas