Hi Greg -
Sounds like you've really gotten into SSNS. That's great.
As for your question, SSNS was designed to process things in batches for
efficiency. Processing one batch of 1000 events/notifications is more
efficient than processing 1000 little batches of one event/notification.
That's probably why you're seeing some deterioration in performance.
So, using the SQL Server Event Provider is a preferred option from a
performance point of view.
Have you tweaked the distributor quantum and the generator quantum values
to make them occur more frequently?
SQL Server MVP
Get up to speed quickly with SQLNS
I support PASS, the Professional Association for SQL Server.
On Sun, 17 Dec 2006 17:52:58 -0800, gcook wrote:
> I have a fairly large app that is using notifications services ( in
> prototyping) . Basically i use notification services to deliver a
> notification via a custom delivery channel ( MSMQ). Everything works
> as required but i have some questions on performance.
> I've attempted to do this is two different ways.
> My current solution uses a Stored Proc which i call per insert of data
> ( this could also be converted to a trigger if needed). Basically it
> has this code..
> EXEC [Notifications].[NSEventBeginBatchNotificationAdded]
> @providerName, @eventBatchId OUTPUT
> EXEC [Notifications].[NSEventWriteNotificationAdded] @eventBatchId,
> @Message , @UserId, 'EventHistoryNotification'
> EXEC [Notifications].[NSEventFlushBatchNotificationAdded]
> @eventBatchId, 1
> So one event per call of the SP.
> The second method i used was to have the built in SQLProvider poll a
> table looking for some criteria and have that generate all the events
> for me ( i took advantage of a chronicle etc ). This also worked and
> was polling every 1 second ( Speed of notification generation is very
> important for me )..
> The problem was when i started using the current method the performance
> of delivery to the MSMQ was very slow? I wondered if this might be
> something to do with the number of batches i generate. Eg if i call
> the SP 100 times i get 100 batches, where as with the SQLProvider
> polling a table i might get less batches?
> So advice on which is the best method would be nice. Im currently
> using two books, one by Joe and one by Shyam, ive implemented the MSMQ
> example from the books, but wonder if there is a nicer or faster way to
> get data to an MSMQ or something like it?
> Hoep you can help