Foreach vs set for Database mail stored proc. Is it possible to send many messages with 1 statement?

  • Hi all

    I am trying to implement a simple warning system using database email.

    I am monitoring an application table which I can't modify. (adding a trigger affects the application). So instead I have created a tracking table which I compare and insert new records to and update records that change.

    I have created a trigger on my tracking table which fires when rows are inserted or updated, and it works great for single record transactions.

    However my monitoring script/process may attempt to insert or update many rows into my tracking table. I know that I can rewrite the script so that it inserts or updates each record one at a time using a foreach loop or cursor, but i was wondering if there was a better way?

    (And ideally I would like to remove the trigger and place the logic with a single script that runs at a regular interval.)

    I frequently hear many people say you should use single statement rather than foreach, but I can't see how I could do that. My understanding is that a stored proc must be called or executed for each iteration/record. is this correct?

    So my question is: If I have a set of records can I send an email for each record using dbmail stored proc with a single statement without a foreach/cursor?.

    Any help or suggestions would be greatly appreciated.

    Thanks

    Pete

  • I know it's always a good idea to provide examples. So here is my current test script.

    edit: but looking at it I realise it's proabably a bit useless :ermm:

    -- 1. select query to identify records

    -- 2. for each record in my select statement do the following..

    DECLARE @id INT

    DECLARE @woid INT

    DECLARE @priority VARCHAR (20)

    DECLARE @descr VARCHAR (20)

    DECLARE @sendmailto VARCHAR (256)

    DECLARE @subj VARCHAR (256)

    DECLARE @stamp datetime

    Begin

    SET @stamp = GETDATE()

    SELECT @woid = 143

    SELECT @descr = 'test message'

    SELECT @priority = '1=Critical'

    SET @sendmailto = (select [sendmailto]

    from [dbo].[email_list]

    where priority = @priority)

    SET @subj = 'Test Subject priority: ' + @priority

    EXEC msdb.dbo.sp_send_dbmail @profile_name='db mail',

    @recipients=@sendmailto,

    @subject=@subj,

    @body='Test message body'

    End

    Unless there is a smarter way, I would select a set of records into a cursor and call msdb.dbo.sp_send_dbmail for each record.

    If there is a more efficient method of doing this I would really appreciate hearing about it.

    Thanks

    Peter

  • There is no guarantee this would be faster, but try a WHILE loop (using your records as a decrementing counter) and see if that has any better performance than a CURSOR.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • there's no way, as far as i know, to use dbmail in a set based mode;

    sending individual emails is one of the places I think a cursor is no problem. unless you want multiple recipients in the TO/CC/BCC fields, there's no way around it;

    When i do a mass mail, of around 1200 individiual emails in a cursor, the cursor itself takes about a second to run, and it takes maybe 10 minutes tops for every email from the cursor to actually get processed as SENT by the broker behind the scenes, as it connects to the SMTP server and does it's thing.

    How many emails are in your batches?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • you could build it into a xml auto format pass it into an exec statement if you wanted to do it set based, but it will eventually only come out as multiple sp_send_dbmail requests. There was a post (cant remember who from) in the last week which did this

    This is an example on what I mean, not actually the real thing as this gets DB file usage

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = REPLACE(

    CAST(

    (

    SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +

    'SELECT ' + CHAR(13) + CHAR(10) +

    'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +

    'a.FILEID, ' + CHAR(13) + CHAR(10) +

    '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +

    'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)

    FROM sys.databases

    FOR XML PATH('')

    )

    AS NVARCHAR(MAX)

    ),

    ' ',CHAR(13) + CHAR(10)

    )

    --SELECT @sql

    EXECUTE sp_executesql @sql

  • Loops internally but no cursor:

    DECLARE @sql NVARCHAR(MAX) = N'';

    DECLARE @priority VARCHAR(20)= '1=Critical';

    SELECT @sql = @sql + 'EXEC msdb.dbo.sp_send_dbmail

    @profile_name = ''db mail'',

    @recipients = ''' + REPLACE([sendmailto],'''','''''') + ''',

    @subject = ''Test Subject priority: ' + @priority + ''',

    @body = ''Test message body'';

    '

    FROM [dbo].[email_list]

    WHERE priority = @priority;

    PRINT @sql;

    --EXEC(@sql);

    Edit: add REPLACE to guard against embedded single-quotes in sendmailto, just in case

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Have u considered using service borker or an application layer to do the mail sending.

    If your looking for a scheduled process u can do all that you want using SSIS too.

    Jayanth Kurup[/url]

  • Jayanth_Kurup (6/1/2012)


    Have u considered using service borker...

    Why would one want to re-invent the wheel? Database Mail is built atop Service Broker.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Brandie Tarvin (5/31/2012)


    There is no guarantee this would be faster, but try a WHILE loop (using your records as a decrementing counter) and see if that has any better performance than a CURSOR.

    Actually, probably not. The only difference between using a Temp Table/ While loop combo and a nice read only forward only (fast forward) static cursor (also known as a "FireHose Cursor", are a couple of words of syntax. There's really no difference in performance, blocking, resource usage, etc. In fact, the cursor is easier to use than the While Loop for some people.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thepotplants (5/30/2012)


    Hi all

    I am trying to implement a simple warning system using database email.

    I am monitoring an application table which I can't modify. (adding a trigger affects the application). So instead I have created a tracking table which I compare and insert new records to and update records that change.

    I have created a trigger on my tracking table which fires when rows are inserted or updated, and it works great for single record transactions.

    However my monitoring script/process may attempt to insert or update many rows into my tracking table. I know that I can rewrite the script so that it inserts or updates each record one at a time using a foreach loop or cursor, but i was wondering if there was a better way?

    (And ideally I would like to remove the trigger and place the logic with a single script that runs at a regular interval.)

    I frequently hear many people say you should use single statement rather than foreach, but I can't see how I could do that. My understanding is that a stored proc must be called or executed for each iteration/record. is this correct?

    So my question is: If I have a set of records can I send an email for each record using dbmail stored proc with a single statement without a foreach/cursor?.

    Any help or suggestions would be greatly appreciated.

    Thanks

    Pete

    Do you want to send all of the rows to the same addresses or do you want to send each row to a different address based on content?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply