| Author |
Topic |
|
attackmonkey
Starting Member
19 Posts |
 Posted - 2003-01-28 : 09:51:10
|
| We're looking at using SQL server with sp_OACreate to call the CDOSYS component and send a bunch of emails (30,000+). Now I'm not sure if this is a good idea on a production server where there will be several large databases running as well. Has anyone had any experience of doing this on this scale? I'd be interested to know if my gut feeling (that this is a BAD idea) is correct, or if I am worrying needlessly.Thanks in advance for any help and advice.:)Cheers,Tim. |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-28 : 09:55:00
|
| Depends on frequency of emails and size of emails and time required to create these emails. |
 |
|
|
attackmonkey
Starting Member
19 Posts |
Posted - 2003-01-28 : 10:01:47
|
| You'd be looking at 100K+ multipart html/plain text emails going once a week or so, maybe more. Testing in the office, it maxed the sql server at 100% CPU for a protracted amount of time and slowed down all the sites that were using the database server.Tim. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-28 : 10:04:29
|
| What is the process for creating each email and how long does it take for 1 email? |
|
|
|
attackmonkey
Starting Member
19 Posts |
Posted - 2003-01-28 : 10:14:32
|
| Each email is a multipart text/html email that has a lot of customisation it (personalised greetings and stuff). The emails could potentially be 100K+ a piece, as they contain news articles for several days worth of content.At the moment we are toying with having two tasks, one which generates all of the emails and saves then into a staging table, and then a second that could run as a DTS scehuled task to send the actual emails. Would this be viable?Tests in the office sending 50,000 emails fromone of our dev boxes have shown the server maxing out CPU usage, and have substantially slowed down all the sites that use the database, even with minimal office traffic.:( |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-01-28 : 12:39:36
|
| have you've looked at...http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9283....???which would help you schedule the intensive tasks for off-peak times...there are several topics discussing this issue...search for "mail" in "articles"....there are several that may help you move along. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-01-28 : 15:06:19
|
I would recommend approaching this slightly differently -- create a table for outgoing email, and write a small external app to periodically check that table and send the email that needs to be sent. This will benefit you a few ways:- You can scale out the email sending app by going multi-threaded, running it on multiple machines, etc
- Your SQL server won't be using sp_OACreate, sp_OADestroy, etc
We did the sp_OACreate thing on an NT4 box (CDONTS, not CDOSYS) and found that it led to stability problems -- after a few hundred thousand emails, the server would crash with the dreaded "unable to start thread process_login" (or something similar; it's been a while). Disabling the sp_OA stuff fixed the problem, re-enabling it caused the crashes again.If you go with an external app for the actual sending of mail, I think you'll be much happier.Cheers-b |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-28 : 15:51:16
|
| I've implimented a solution very similar to what aiken suggested, and it's been working great for years.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
|
|
attackmonkey
Starting Member
19 Posts |
Posted - 2003-02-03 : 05:48:00
|
| Thanks for all the help and advice guys, I've decided to try and persuade our lot to do something like what aiken has suggested. That way we can spread the sending load through several of our servers and stagger the sending tasks so that we have a couple of servers checking the mail quue at different points in time.Thanks again guys!:)Tim. |
|
|
|
Gabriellerdx
Starting Member
1 Post |
Posted - 2013-06-13 : 07:44:28
|
| unspammed |
|
|
|
|
using sp_OACreate to send emails