using msdb.dbo.sp_send_dbmail to many email addresses 3

[NuJoizey]

I have view_120DayEmailList which I want to set up as a job that every day returns a recordset of accounts whose expiration days are 120 days away.

Included in the recordset are the 3 account team members' email addresses. - so the view returns a recordset looking something like:

Account Broker AcctManager TechAssist
------- ------ ----------- ----------
ABCCorp Sally@x.com John@x.com Fred@x.com
XYZInc Bill@x.com Linda@x.com Carol@x.com
.
.
.
n....


Each day, if the recordset is not NULL, I need to be able to send a simple email alert to each of the Broker AcctManager and TechAssist email addresses for each retuned records

I am trying to somehow use:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLServerDatabaseMailProfile',
    @recipients = [COLOR=red] ALL OF THE RETURNED ADDRESSES[/color]
    @body = 'ABC company is scheduled to be renewed in 120 days - GET TO WORK!',
    @subject = 'ABC Company ALERT!' ;

but i am having trouble understanding how to manipulate the sets in TSQL to allow this to happen since there is no looping - Am I taking the right approach?

 

[philhege]

declare @compname varchar(64),
@recipients varchar(256)

SELECT (whatever's in your recordset), 0 as processed
INTO #maillist
FROM Yourtables

WHILE (select count(*) from #maillist where processed = 0) > 0

BEGIN
SELECT TOP 1 @compname = account,
@recipients = isnull(broker,'')+';'+isnull(acctmanager,'')+';'+isnull(techassist,'')
FROM #maillist
WHERE processed = 0

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServerDatabaseMailProfile',
@recipients = @recipients @body = @compname + ' is scheduled to be renewed in 120 days - GET TO WORK!',
@subject = @compname + ' ALERT!'


UPDATE #maillist set processed = 1 where account = @compname
END

DROP TABLE #maillist

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[NuJoizey]

thanks for this - i had deduced so far that i do in fact need to use some type of TSQL loop, which I am reading up on. I noticed you didn't seem to use a TSQL cursor though, which I have read about - Instead you seem to have used #maillist as a temporary table - any particular reason?

 

[philhege]

This approach avoids a cursor. Notice the WHILE condition; it checks for records remaining to process in the work table.

You could do this with a table variable also, depending on your version.

 

[NuJoizey]

just for kicks, do you (or anyone) know how you might approach this with using a cursor method?

 

[philhege]

Cursors bad. Very bad. Very, very bad. Avoid at all costs, because, well, they're just evil.

 

[NuJoizey]

so i've been told. But what do you make of this then?

http://technet.microsoft.com/en-us/library/ms180152.aspx

i'm pretty close to a solution that is derivative of example b. - seems simple enough - why not use it?

I'm not trying to be difficult, i'm just trying to learn...

 

[ca8msm]

Cursors are generally bad because they are slow. They are slow because they process one record at a time, instead of in a set (which is what sql server is good at). If they can be avoided and you can process all the records at once, then you should. If not, then a cursor could be a valid method.

However, in this case, a while loop is still in essence doing the same thing by processing one record at a time, so in this case it may not actually be that different (although you should test this to make sure).


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

The problem with cursors (and while loops) is that they are slow. It is technically possible to write a lot of code that uses cursors, but performance will not be as good. Cursors were created because there is a small (very small) subset of solutions that cursors are better for. For example, if you want to create a running total of something (think check book), then cursors are faster.

Most problems have a set based solution and that set based solution is usually many (many, many, many) times faster. Does this mean we should never use a cursor? No. But it does mean that a cursor solution should be rare.

And for the record... a while loop performance is comparable to a cursor's performance. In fact, it's really just a syntax difference.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

George, I had been told that cursors do nasty things to SQL Server's memory, hence the proscription. Any truth to this?

 

[gmmastros]

Phil,

I'm not aware of any memory leaks at all with SQL Server. Denis once pointed me to an article where the performance of a 'rolling count' was compared to that of a set based solution for the same problem. I'll probably never find that page again.

Performance with SQL Server is a tricky beast, that's for sure. Sometimes, what works in one situation doesn't perform that well in others. My best advice, where performance is an issue, is to write the query different ways and test the performance.

For example, I once had a query that gave better performance if I first dumped the data in to a table variable. Normally, you will get better performance from a derived table, but this particular query performed better with a table variable. I spend DAYS messing with it, all to squeeze out a couple hundred MILLI-seconds of performance. It was an often used query, so performance was very important.

I'm afraid I didn't really answer your question. Truth is... you will not find a single cursor in any of my code. The last cursor I wrote was probably back in 2002, and has since been replaced. I will occasionally use a while loop. While the performance of a while loop is no better than the performance of a cursor, I find the syntax easier to understand.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NuJoizey]

I find the syntax easier to understand

that's about where I am right now - if i can get that far, i'm pretty happy for the time-being <grin>

but i am finding this discussion helpful, so thank you

 

[NuJoizey]

for a cursor, if you know that you will never be looping thru more than just a few records, I'm talking under 50, do you think the performance hit would be a factor? - I suspect it wouldn't, but then what would you suspect is the transition point in terms of # of records when choosing one over the other?

The assumption is that in this situation can you make a case for using cursors for simplicity/understandability of code

 

[ca8msm]

for a cursor, if you know that you will never be looping thru more than just a few records, I'm talking under 50, do you think the performance hit would be a factor? - I suspect it wouldn't, but then what would you suspect is the transition point in terms of # of records when choosing one over the other?

Without trying to be too vague, it depends! You will have to test each scenario and, as George said above, try different methods to accomplish the same result.

But in general, if you avoid cursors, you will have less performance issues than if you use them. If you come across a situation where you need to use them, and there are no alternatives, then go ahead and use one.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

NuJoizey,

Along with comments from Mark... you need to consider how your database is going to grow.

10 years ago, I started working on my application. Truthfully, I didn't know a whole lot about programming and even less about databases. I worked solid, round the clock, until I got my first sale a couple months later. I was ecstatic. Shortly after the install, my customer calls to complain about performance. You see... I tested with a small database, and performance was great. At the time of install, they loaded a lot more data than I tested with, and performance was abysmal.

So, I learned from that experience. What I learned was that you should treat each query as though it were important, and often used. You really do need to performance tune everything. Everything! If you have a query that takes 10 seconds to run, make it quicker. If you have a query that takes 1 second to run... make it faster. If you have a query that takes 0.1 seconds to run... again... make it faster.

So, if you have a cursor that loops over 50 records, the performance may be acceptable now, but will it continue to be acceptable in a couple months when there are 500 records? 5000 records? Don't be fooled into thinking it's good enough.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NuJoizey]

message understood - thank you for the anecdote of your experiences.

I have been working on the premise of iteration - i.e. just get the thing to work first, even if it's ugly and slow, and then go back and steadily improve as I learn more.

The breadth of what needs to be done on my system is so large, that I'm feeling like it forces me into that model

 

[gmmastros]

Understood. And it's not a bad way to approach it.

I'm going to guess that you are more comfortable with a 'front end' language, like VB, VB.net, C#.net, etc...

It's pretty typical for front end developers to think in terms of loops. Unfortunately, SQL Server will give you much better performance if you start thinking in terms of sets. This is not always an easy barrier to overcome, and often takes a long time to 'get it'.

As I hinted in my previous post, there is usually a way to improve performance for most queries. And, given that time is always a constraint, there is an impulse to 'just get it to work'. And believe me, I understand that approach. However, cursors are usually MANY MANY times slower. I think it was SQLSister that once commented that she re-wrote cursor based query to set based and the execution time went from hours to just seconds.

My best advice to you is... forget about cursors and while loops. If you get stuck on a query that you just can't seem to figure out, then post a question here. Most cursor based queries can be re-written to set based queries, and the performance will get better.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NuJoizey]

thanks george, I'm sure glad you guys are here, b/c studying examples and trying them out is the best way for me.

I'm not at the level yet where I can always tell exactly what something is doing just by looking at it, and there are a lot of things I have yet to explore, like how to measure performance of a query in SQL Server...

You guessed right, I have done and continue to do a decent amount of UI stuff, but I also now do a lot of this SQL server programming, which I had never done until June of 2007, so you know where I'm coming from.

In terms of a set-based approach, I kind of get it probably to an intermediate level, its the advanced stuff I'm getting stuck on these days.

I like to tell people that I know enough about this to be dangerous!

 

[ESquared]

Cursors bad. Very bad. Very, very bad. Avoid at all costs, because, well, they're just evil."

A cursor is bad precisely because it runs the query once for every loop.

The example given above also runs the query once for every loop. In that respect, they are identical. I personally find the non-cursor looping method faster to write and easier to maintain, since I'm using syntax I use all the time and I don't have to memorize complicated cursor syntax which I almost never use.

If you have to run an SP once for each row of something, then you're pretty much stuck with looping and running a query each time.