Hello All,
I am looking for suggestions on the design and implementation
of a mechanism that notifies clients of specific changes in
our database. Our current implementation, while resolves the
problem, creates an explicit bottleneck, which has caused
contention problems under stress conditions.
In our current implementation:
* Our clients use ADO and connect to a SQL Server 2000 database.
Lets call it the "asset" database.
* Each client can "open" an asset from the database, in which case,
all information about that asset is copied into an object in
the client side.
* As assets are modified, created, and deleted, we add a row to
a Notification table, which is used as a circular buffer. Each
row in that table contains information regarding the nature of
the change.
* We also have another table, NoticeIndex, which contains one row
and one column and stores the number of the row in the Notification
table where the last notice was inserted.
* Every 10 seconds, the clients poll the Notification table to get
all the changes inserted since the "last time" they checked.
* Then, each client processes the new changes selecting the ones
that it cares about, e.g. updating the objects associated with
open assets, updating lists in the interface, etc. (this will
depend on the front-end exposed through the client).
As you can see, as a result of this design, we must lock the
Notification and NoticeIndex tables every time we need to write
or read notices, in order to avoid inconsistencies. We first must
read the value of the current row number from the NoticeIndex
table and use that value to read/write to the Notification table.
If we are adding a notification, we also must update the
NoticeIndex table.
This has proven to be a bottleneck under stress conditions. For
example, in a typical system with 8 clients, we might have
100 changes/second (i.e. 160 new columns added to the NOtification
table per second). In addition to that, every 10 seconds, the
clients poll for changes, basically reading 1000 rows from the
Notification table.
Every other solution that I could think would also have similar
restrictions with respect to locking, which would result in the
same latencies.
Is this possible to resolve this problem without creating such
intrinsic contention? Any suggestion would be greatly appreciated.
- CDSounds like you have a single row per change which is reflected in the
NoticeIndex table.
Shouldn't need to lock anything. insert the Notification row then update the
NoticeIndex - code it so that the new row isn't accessible until the
NoticeIndex is updated. Maybe have a start and end NoticeIndex - update the
start before the insert and the end after.
Another opion is to do away with NoticeIndex alogether - just do inserts
into the Notification table and have a scheduled task that deletes old
records. Use an identity to identify the order.
"crbd98@.yahoo.com" wrote:
> Hello All,
> I am looking for suggestions on the design and implementation
> of a mechanism that notifies clients of specific changes in
> our database. Our current implementation, while resolves the
> problem, creates an explicit bottleneck, which has caused
> contention problems under stress conditions.
> In our current implementation:
> * Our clients use ADO and connect to a SQL Server 2000 database.
> Lets call it the "asset" database.
> * Each client can "open" an asset from the database, in which case,
> all information about that asset is copied into an object in
> the client side.
> * As assets are modified, created, and deleted, we add a row to
> a Notification table, which is used as a circular buffer. Each
> row in that table contains information regarding the nature of
> the change.
> * We also have another table, NoticeIndex, which contains one row
> and one column and stores the number of the row in the Notification
> table where the last notice was inserted.
> * Every 10 seconds, the clients poll the Notification table to get
> all the changes inserted since the "last time" they checked.
> * Then, each client processes the new changes selecting the ones
> that it cares about, e.g. updating the objects associated with
> open assets, updating lists in the interface, etc. (this will
> depend on the front-end exposed through the client).
> As you can see, as a result of this design, we must lock the
> Notification and NoticeIndex tables every time we need to write
> or read notices, in order to avoid inconsistencies. We first must
> read the value of the current row number from the NoticeIndex
> table and use that value to read/write to the Notification table.
> If we are adding a notification, we also must update the
> NoticeIndex table.
> This has proven to be a bottleneck under stress conditions. For
> example, in a typical system with 8 clients, we might have
> 100 changes/second (i.e. 160 new columns added to the NOtification
> table per second). In addition to that, every 10 seconds, the
> clients poll for changes, basically reading 1000 rows from the
> Notification table.
> Every other solution that I could think would also have similar
> restrictions with respect to locking, which would result in the
> same latencies.
> Is this possible to resolve this problem without creating such
> intrinsic contention? Any suggestion would be greatly appreciated.
> - CD
>|||Hi,
I would say that you do everything properly. There is no simple way to get
notifications about changes in a database. Even in SQL Server 2005
notifications services have limitations. To improve performance I would
suggest only to review the code and design to see if you could re-design
some part of code to make it a little bit faster, but general design
probably would stay the same. Also try to run SQL Profiler to see where SQL
Server spends most of the time. In some cases you could improve situation
using some sort of table indexes. This time of application would also
require more powerful hardware
--
Val Mazur
Microsoft MVP
<crbd98@.yahoo.com> wrote in message
news:edf41e63.0410251252.6a694d35@.posting.google.com...
> Hello All,
> I am looking for suggestions on the design and implementation
> of a mechanism that notifies clients of specific changes in
> our database. Our current implementation, while resolves the
> problem, creates an explicit bottleneck, which has caused
> contention problems under stress conditions.
> In our current implementation:
> * Our clients use ADO and connect to a SQL Server 2000 database.
> Lets call it the "asset" database.
> * Each client can "open" an asset from the database, in which case,
> all information about that asset is copied into an object in
> the client side.
> * As assets are modified, created, and deleted, we add a row to
> a Notification table, which is used as a circular buffer. Each
> row in that table contains information regarding the nature of
> the change.
> * We also have another table, NoticeIndex, which contains one row
> and one column and stores the number of the row in the Notification
> table where the last notice was inserted.
> * Every 10 seconds, the clients poll the Notification table to get
> all the changes inserted since the "last time" they checked.
> * Then, each client processes the new changes selecting the ones
> that it cares about, e.g. updating the objects associated with
> open assets, updating lists in the interface, etc. (this will
> depend on the front-end exposed through the client).
> As you can see, as a result of this design, we must lock the
> Notification and NoticeIndex tables every time we need to write
> or read notices, in order to avoid inconsistencies. We first must
> read the value of the current row number from the NoticeIndex
> table and use that value to read/write to the Notification table.
> If we are adding a notification, we also must update the
> NoticeIndex table.
> This has proven to be a bottleneck under stress conditions. For
> example, in a typical system with 8 clients, we might have
> 100 changes/second (i.e. 160 new columns added to the NOtification
> table per second). In addition to that, every 10 seconds, the
> clients poll for changes, basically reading 1000 rows from the
> Notification table.
> Every other solution that I could think would also have similar
> restrictions with respect to locking, which would result in the
> same latencies.
> Is this possible to resolve this problem without creating such
> intrinsic contention? Any suggestion would be greatly appreciated.
> - CD|||Hello Nigel,
You are correct in the sense that each change corresponds to a new row
added to the Notification table. Nevertheless, I do not understand your
suggestion of dropping the locks altogether.
The Notification table is used as a circular buffer. The maximun number
of rows is set to a number large enough to support the rate that changes
are produced versus the rate the clients poll for changes. This is to
ensure that clients do not loose any updates. Since it is a circular buffer,
I do not need to delete the previous notices as I wrapp wround. They are
simply overwritten with new notices.
The basic steps of our InsertNotice stored procedure are included in
the end of the email. I am not sure how I could avoid locking while
still ensuring correct behavior. For instance, if a client attempted
to read changes while another client was in the middle of execiting the
InsertNotice stored procedure, the results would be wrong.
Please let me know what you think.
- CD
1. Declare and open a cursor for the NoticeIndex table. This blocks any other
access.
declare insertNotice_cursor cursor scroll_locks for
select lastNumber from NoticeIndex
for update
-- Open the cursor
open insertNotice_cursor
2. Fetch the lastNumber from the cursor
fetch next from insertNotice_cursor
into @.lastNumber
3. Increment to get the next sequence number. Wrapp aroud if
we have the maxNumber of changes. (@.maxNumber is defined elsewhere)
set @.lastNumber = @.lastNumber + 1
if @.lastNumber = @.maxNumber
begin
set @.lastNumber = 0
end
4.Insert/Update the notice to the table
update Notification set
...
where
sequenceNumber = @.lastNumber
5. Update NoticeIndex record
update NoticeIndex
set lastnumber = @.lastNumber
where current of insertNotice_cursor
6. Close and deallocate cursor
Nigel Rivett <NigelRivett@.discussions.microsoft.com> wrote in message news:<879E29A5-C833-4766-808B-7DB6B087933F@.microsoft.com>...
> Sounds like you have a single row per change which is reflected in the
> NoticeIndex table.
> Shouldn't need to lock anything. insert the Notification row then update the
> NoticeIndex - code it so that the new row isn't accessible until the
> NoticeIndex is updated. Maybe have a start and end NoticeIndex - update the
> start before the insert and the end after.
> Another opion is to do away with NoticeIndex alogether - just do inserts
> into the Notification table and have a scheduled task that deletes old
> records. Use an identity to identify the order.
>
> "crbd98@.yahoo.com" wrote:
> > Hello All,
> >
> > I am looking for suggestions on the design and implementation
> > of a mechanism that notifies clients of specific changes in
> > our database. Our current implementation, while resolves the
> > problem, creates an explicit bottleneck, which has caused
> > contention problems under stress conditions.
> >
> > In our current implementation:
> >
> > * Our clients use ADO and connect to a SQL Server 2000 database.
> > Lets call it the "asset" database.
> > * Each client can "open" an asset from the database, in which case,
> > all information about that asset is copied into an object in
> > the client side.
> > * As assets are modified, created, and deleted, we add a row to
> > a Notification table, which is used as a circular buffer. Each
> > row in that table contains information regarding the nature of
> > the change.
> > * We also have another table, NoticeIndex, which contains one row
> > and one column and stores the number of the row in the Notification
> > table where the last notice was inserted.
> > * Every 10 seconds, the clients poll the Notification table to get
> > all the changes inserted since the "last time" they checked.
> > * Then, each client processes the new changes selecting the ones
> > that it cares about, e.g. updating the objects associated with
> > open assets, updating lists in the interface, etc. (this will
> > depend on the front-end exposed through the client).
> >
> > As you can see, as a result of this design, we must lock the
> > Notification and NoticeIndex tables every time we need to write
> > or read notices, in order to avoid inconsistencies. We first must
> > read the value of the current row number from the NoticeIndex
> > table and use that value to read/write to the Notification table.
> > If we are adding a notification, we also must update the
> > NoticeIndex table.
> >
> > This has proven to be a bottleneck under stress conditions. For
> > example, in a typical system with 8 clients, we might have
> > 100 changes/second (i.e. 160 new columns added to the NOtification
> > table per second). In addition to that, every 10 seconds, the
> > clients poll for changes, basically reading 1000 rows from the
> > Notification table.
> >
> > Every other solution that I could think would also have similar
> > restrictions with respect to locking, which would result in the
> > same latencies.
> >
> > Is this possible to resolve this problem without creating such
> > intrinsic contention? Any suggestion would be greatly appreciated.
> >
> > - CD
> >|||CD,
I may be missing something, but if you are going out to poll the
Notification table and then make changes in the UI, wouldn't refreshing the
Record Sets that populate the lists, etc serve the same purpose? It would
obviate the need for the Notification table which simply notes the changes
that are reflected in the actual tables used by the UI.
CF
<crbd98@.yahoo.com> wrote in message
news:edf41e63.0410251252.6a694d35@.posting.google.com...
> Hello All,
> I am looking for suggestions on the design and implementation
> of a mechanism that notifies clients of specific changes in
> our database. Our current implementation, while resolves the
> problem, creates an explicit bottleneck, which has caused
> contention problems under stress conditions.
> In our current implementation:
> * Our clients use ADO and connect to a SQL Server 2000 database.
> Lets call it the "asset" database.
> * Each client can "open" an asset from the database, in which case,
> all information about that asset is copied into an object in
> the client side.
> * As assets are modified, created, and deleted, we add a row to
> a Notification table, which is used as a circular buffer. Each
> row in that table contains information regarding the nature of
> the change.
> * We also have another table, NoticeIndex, which contains one row
> and one column and stores the number of the row in the Notification
> table where the last notice was inserted.
> * Every 10 seconds, the clients poll the Notification table to get
> all the changes inserted since the "last time" they checked.
> * Then, each client processes the new changes selecting the ones
> that it cares about, e.g. updating the objects associated with
> open assets, updating lists in the interface, etc. (this will
> depend on the front-end exposed through the client).
> As you can see, as a result of this design, we must lock the
> Notification and NoticeIndex tables every time we need to write
> or read notices, in order to avoid inconsistencies. We first must
> read the value of the current row number from the NoticeIndex
> table and use that value to read/write to the Notification table.
> If we are adding a notification, we also must update the
> NoticeIndex table.
> This has proven to be a bottleneck under stress conditions. For
> example, in a typical system with 8 clients, we might have
> 100 changes/second (i.e. 160 new columns added to the NOtification
> table per second). In addition to that, every 10 seconds, the
> clients poll for changes, basically reading 1000 rows from the
> Notification table.
> Every other solution that I could think would also have similar
> restrictions with respect to locking, which would result in the
> same latencies.
> Is this possible to resolve this problem without creating such
> intrinsic contention? Any suggestion would be greatly appreciated.
> - CD|||Hello CF,
Not all applications that use our database interface expose a UI that
requires to enumerate all assets in the database. This explain our
need for polling for changes. It is not the application that is
polling for changes. It is the database access layer instantiated by
the application ( a COM server using ADO for DB access).
Makes sense'
- CD
"Wart" <nospamWart@.epix.net> wrote in message news:<eg5m5LGvEHA.940@.TK2MSFTNGP14.phx.gbl>...
> CD,
> I may be missing something, but if you are going out to poll the
> Notification table and then make changes in the UI, wouldn't refreshing the
> Record Sets that populate the lists, etc serve the same purpose? It would
> obviate the need for the Notification table which simply notes the changes
> that are reflected in the actual tables used by the UI.
> CF
> <crbd98@.yahoo.com> wrote in message
> news:edf41e63.0410251252.6a694d35@.posting.google.com...
> > Hello All,
> >
> > I am looking for suggestions on the design and implementation
> > of a mechanism that notifies clients of specific changes in
> > our database. Our current implementation, while resolves the
> > problem, creates an explicit bottleneck, which has caused
> > contention problems under stress conditions.
> >
> > In our current implementation:
> >
> > * Our clients use ADO and connect to a SQL Server 2000 database.
> > Lets call it the "asset" database.
> > * Each client can "open" an asset from the database, in which case,
> > all information about that asset is copied into an object in
> > the client side.
> > * As assets are modified, created, and deleted, we add a row to
> > a Notification table, which is used as a circular buffer. Each
> > row in that table contains information regarding the nature of
> > the change.
> > * We also have another table, NoticeIndex, which contains one row
> > and one column and stores the number of the row in the Notification
> > table where the last notice was inserted.
> > * Every 10 seconds, the clients poll the Notification table to get
> > all the changes inserted since the "last time" they checked.
> > * Then, each client processes the new changes selecting the ones
> > that it cares about, e.g. updating the objects associated with
> > open assets, updating lists in the interface, etc. (this will
> > depend on the front-end exposed through the client).
> >
> > As you can see, as a result of this design, we must lock the
> > Notification and NoticeIndex tables every time we need to write
> > or read notices, in order to avoid inconsistencies. We first must
> > read the value of the current row number from the NoticeIndex
> > table and use that value to read/write to the Notification table.
> > If we are adding a notification, we also must update the
> > NoticeIndex table.
> >
> > This has proven to be a bottleneck under stress conditions. For
> > example, in a typical system with 8 clients, we might have
> > 100 changes/second (i.e. 160 new columns added to the NOtification
> > table per second). In addition to that, every 10 seconds, the
> > clients poll for changes, basically reading 1000 rows from the
> > Notification table.
> >
> > Every other solution that I could think would also have similar
> > restrictions with respect to locking, which would result in the
> > same latencies.
> >
> > Is this possible to resolve this problem without creating such
> > intrinsic contention? Any suggestion would be greatly appreciated.
> >
> > - CD
