Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-27 : 08:52:18
|
| Apollo writes "Is there a limit to the length of recipients you can have with Sqlmail? We want to develop a mailing system that will be capable of mailing user lists of up to 500,000. Would SqlMail bethe best way to do a large mailing? What kind of performance could be expected from a single P3 500 / 256MB RAM running Windows 200 Advanced Server and Sql Server 2000 for a mailing of 100,000 users. Are there any reference points for projects of this scope?Thank You Very Much In Advance!" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-27 : 09:59:09
|
| Unfortunately I can't think of the best solution for performing this kind of activity, but I can tell you one thing: SQL Mail is THE WORST method you could use for this. It will almost certainly hang your SQL Server. I would even recommend against a mail program that queries your SQL Server directly. You should export the mailing list to another file and have the mail program use it. The reason is that a direct query to your SQL Server could create a lot of locks that won't get released if the mail server dies.Merkin has some articles on mail queues and other mail methods:http://www.sqlteam.com/item.asp?ItemID=5908http://www.sqlteam.com/item.asp?ItemID=5003HTH |
 |
|
|
apollo
Starting Member
1 Post |
Posted - 2001-11-27 : 10:46:48
|
| Thanks for the info. Do you think it would make sense to do a large mailing in chunks? Writing 100,000 records to a file and manipulating it would put a lot of strain on resources. A test file I did for 100,000 was over 9MB. Blocks of 1 or 5 thousand might be a little more reasonable to manipulate from a systems' perspective. A queue can be set up in SQL Server to manage the process. Is there any place that addresses the issues of large mailings or using SQL Server with large result sets? Most of the articles that I've come across deal in very small scale. Thanks Again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-27 : 11:12:36
|
| Other than the ones I linked, I don't know of any others. You might find something on [url]http://www.swynk.com[/url], they have a lot of esoteric SQL Server stuff. I'm pretty sure that anyone who has tried using SQL Mail at this level either went insane, killed themselves, or gave up. All I know is a lot of people don't use it because it causes too many problems just trying to send a few emails. Anyone who can get it to do 100's of thousands of emails is simply God. AFAIK even Microsoft doesn't use SQL Mail for something like this.Breaking them up into chunks not only makes sense from this perspective, but also for the sake of properly loading the mail server. 10,000 messages at a time will certainly cause it to slow down a lot. You can schedule each batch to go out at certain times to balance the activity too.SQL Server has no problem manipulating even millions of rows; again, the problem lies in the interface between it and the mail program. You will certainly have problems if the mail program SELECTs the rows you need directly; it will create locks and use SQL resources during the process, and if it fails your SQL server could lock up (will definitely slow to a crawl). Export the data to another source (text file, etc.) and use that instead. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2001-11-28 : 11:11:22
|
| Oh the days of SPAM .. I remember doing those dirty tasks ... never felt good about it but it had to be done... anyhow you would be better off writing the client to process one entry at a time and not offload the sending to the email server ... I.E. by specifying multiple recipients in the TO,CC, or BCC fiels the email server still has to send one copy to each address. It would be better to send one copy to only one person and have your application spawn multiple threads... I think 128-255 threads would be reasonable if your mail server can keep up... and have them all pull one entry and move it into the sending queue and then once done move it to the sent queue ... at least this is the approach I was going to take ... and XML can be very helpful for formating the messages :-p Had a database full of user information such as occupation, city of residence, name, birth, etc... and I wrote a XML document for each entry and had that transformed into the TEXT the company wanted to send to each personalized person... Happy hacking!- Onamuji |
|
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2001-11-28 : 16:59:12
|
| Another problem with SQLMail is that it is multi-thread blocking. For example, if you use xp_sendmail and pass a query to it:xp_sendmail @Recipients = 'some@user.com', @query = 'Waitfor delay ''00:10:00'''(the query doesn't matter, as long as it takes a non-trivial amount of time)Any other process running on the SQL Server that attempts to use SQLMail will be blocked by this xp_sendmail for the duration of the @Query execution (10 minutes in that case). They all queue up nicely and wait, and when one finishes, the next one starts.I'm thinking you probably want to invest in a bulk email program, or just use command-line sendmail, which is available in the unix utilities pack. It's "reasonably" speedy.-rs |
|
|
|
|
|
|
|
|
SQLmail performance and limitations