August 20, 2011 at 7:01 am
I need to retrieve some data and send emails to about 10,000 people.
So ..
Select CustomerID, CustomerName, CustomerEmail, EmailSent FROM tblCustomers WHERE EmailSent = 0
... that gets me the approx 10,000 customers.
I now need to loop through this list and send an email to the customer in each row using sp_send_dbmail and run an update statement to update EmailSent to 1.
What's the best way to do this?
If I follow the set based method and use SELECT Min(CustomerID) etc. in a while loop - I'll execute at least 10,000 select statements as I go through the loop.
If I select into a TempTable and use a cursor .... I figure it will take very little time to populate the temptable and then the cursor can just chug away looping through the temptable calling sp_send_dbmail and running an update statement to set EmailSent = 1
Any thoughts? I intend to run this as a job in the middle of the night - worst case will be 100k emails going out.
August 20, 2011 at 8:51 am
Might actually be the case where cursor is the better choice.
Looping is looping, the realy slow part here is sending the message...
If this is not a 1 off insert, I'd copy the ids into a temp table and once the loop is done I'd go back and do a join between the temp table and base table to update the status.
Now make sure you check the status in the msdb mail tables to see if the message was succesfully sent.
August 20, 2011 at 10:06 am
I agree with Ninja...a loop here is one of those cases where it would be appropriate. You could build a dynamic string that includes all of the sp_send_dbmail calls, but I think that is more work than is really necessary.
And, to clarify...a WHILE loop is not a set based approach. A loop is a loop regardless of whether it is done using WHILE or CURSOR. In fact, if the cursor is built correctly it will function just as fast as a while loop and is better managed. I think one of the biggest issues with cursors is that most of the time the defaults are used which will create a dynamic cursor. By using a static, forward only cursor - that overhead is limited and will generally perform just as well as the while loop.
I am not sure I would use a temp table here though - I think it all depends on the query that is getting the id's and how well that is built. If that query only takes a few seconds - then you could easily use that in the cursor, loop over the results - send the email and update the status in one loop.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2011 at 10:38 am
My point with temp table was if this is a daily process and that the table gets inserts during the process, then I treat it as a queue and I make sure I don't accidentally update the wrong records.
The point with the temp is that it does only 1 update in the base table vs 10K. In that regard it'll be faster and won't hurt contention.
But I agree that I might be splitting hairs here. Sorry but that's who I am :-D.
August 20, 2011 at 10:54 am
Remi - I don't disagree with you here. Just saying it may not be necessary and may be more overhead than needed.
It all really depends on the query that is getting the data and as you pointed out, how that table is used in the application.
I just had to respond to the statement about using the 'set based' approach with a while loop. My personal opinion is that if you have to loop (and there are situations where this is required - which this appears to be one) then use the tool that is designed for doing that. Using while to build your own cursor is a lot of overhead and management code that a well designed cursor will do for you.
FWIW - I only ever use STATIC, FORWARD_ONLY type cursors. If I find myself in a situation where I even could think of using a dynamic cursor - I pack up my bags and take the long drive home to seriously think about the problem...because that is just not going to happen...:)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2011 at 9:30 pm
IIRC, a STATIC cursor builds its own Temp Table behind the scenes, anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2011 at 2:58 am
Ninja's_RGR'us (8/20/2011)
Might actually be the case where cursor is the better choice.Looping is looping, the realy slow part here is sending the message...
If this is not a 1 off insert, I'd copy the ids into a temp table and once the loop is done I'd go back and do a join between the temp table and base table to update the status.
Now make sure you check the status in the msdb mail tables to see if the message was succesfully sent.
Thank you for your reply. I'll take your advice and join to the temp table afterwards to do the update.
When you say 'check the status in the msdb mail tables' ... I assumed that Sql Server just sent the email to the mail server and forgot about it. You're saying whether the email was sent or not is stored somewhere?
August 21, 2011 at 3:08 am
Jeffrey Williams-493691 (8/20/2011)
It all really depends on the query that is getting the data and as you pointed out, how that table is used in the application.I just had to respond to the statement about using the 'set based' approach with a while loop. My personal opinion is that if you have to loop (and there are situations where this is required - which this appears to be one) then use the tool that is designed for doing that. Using while to build your own cursor is a lot of overhead and management code that a well designed cursor will do for you.
I've read various posts on this issue before where the general consensus seems to be 'avoid cursors like the plague and use a set based approach'. A typical response being to show someone how they might, for example, not use a cursor by using SELECT INTO
If some sort of action needs to be taken to every row in a dataset, I have also seen suggested a number of times that it is better to use a While loop and, within the loop, effectively Select a Row at a time from the dataset by using MIN or MAX and a loop counter. Is this what you would describe as a 'dynamic' cursor - or is that something else? I get the impression that some people describe doing this sort of thing - as opposed to using a cursor as a 'set based' approach?
I really just wanted to know whether executing 10,000 select statements in a While loop was likely to be better or worse than one Select statement and a cursor.
August 21, 2011 at 3:10 am
Thank you for all the replies. I have done this sort of 'run a job at night to send some emails' but never more than a few emails at a time.
Has anyone here got experience of sending high volumnes of emails like this. Does suddenly sending 10000 emails to a mail server overwhelm it? Should I do it in batches of 100 at a time and run a job every 5 minutes?
August 21, 2011 at 6:48 am
sku370870 (8/21/2011)
Ninja's_RGR'us (8/20/2011)
Might actually be the case where cursor is the better choice.Looping is looping, the realy slow part here is sending the message...
If this is not a 1 off insert, I'd copy the ids into a temp table and once the loop is done I'd go back and do a join between the temp table and base table to update the status.
Now make sure you check the status in the msdb mail tables to see if the message was succesfully sent.
Thank you for your reply. I'll take your advice and join to the temp table afterwards to do the update.
When you say 'check the status in the msdb mail tables' ... I assumed that Sql Server just sent the email to the mail server and forgot about it. You're saying whether the email was sent or not is stored somewhere?
Yes but it can still fail for various reasons...
in msdb => sysmail_faileditems
August 21, 2011 at 6:49 am
sku370870 (8/21/2011)
Thank you for all the replies. I have done this sort of 'run a job at night to send some emails' but never more than a few emails at a time.Has anyone here got experience of sending high volumnes of emails like this. Does suddenly sending 10000 emails to a mail server overwhelm it? Should I do it in batches of 100 at a time and run a job every 5 minutes?
Pinged in Steve...
August 21, 2011 at 7:01 am
sku370870 (8/21/2011)
Jeffrey Williams-493691 (8/20/2011)
It all really depends on the query that is getting the data and as you pointed out, how that table is used in the application.I just had to respond to the statement about using the 'set based' approach with a while loop. My personal opinion is that if you have to loop (and there are situations where this is required - which this appears to be one) then use the tool that is designed for doing that. Using while to build your own cursor is a lot of overhead and management code that a well designed cursor will do for you.
I've read various posts on this issue before where the general consensus seems to be 'avoid cursors like the plague and use a set based approach'. A typical response being to show someone how they might, for example, not use a cursor by using SELECT INTO
If some sort of action needs to be taken to every row in a dataset, I have also seen suggested a number of times that it is better to use a While loop and, within the loop, effectively Select a Row at a time from the dataset by using MIN or MAX and a loop counter. Is this what you would describe as a 'dynamic' cursor - or is that something else? I get the impression that some people describe doing this sort of thing - as opposed to using a cursor as a 'set based' approach?
I really just wanted to know whether executing 10,000 select statements in a While loop was likely to be better or worse than one Select statement and a cursor.
The central issue with cursors is that most programmers coming from "outside" sql server training will write something like usp_updadeSkuPrice.
Then they'll write a select to figure out what needs to be updated and then loop through that list and call that sp 10 000 times.
That is slow as hell in Sql Server. The correct way is to either change or maje another version of the sp that can handle all the items at once.
Here's something I wrote on the subject that puts it in perspective => http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
In your particular case you canNOT amend the sp_sendmail proc so you have to call it n times. With that perspective you just have to use a loop (or maybe another technology).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply