Boot.ini file: client

Showing posts with label client. Show all posts

Thursday, March 22, 2012

bug? outlook web access

Scenerio:
RS2000. Subscription delivers email with PDF attachment.
When the email is opened in Outlook client, the attachment is there and
working as expected.
Problem:
When the email is opened in the Outlook Web client (OWA), the paperclip is
there identifying the attachment, but when the email is opened, there is no
attachment.
It seems to be happening only with attachment coming from reporting
services. I can send an email with another PDF file and open it fine. The
PDF files are small in size so I do not think it is a size proble.I just tried this and also was not able to see the .pdf file. This appears
to be a bug to me. I would contact customer support to help get this issue
resolved.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"chicagoclone" <chicagoclone@.discussions.microsoft.com> wrote in message
news:B4C39492-DC1A-4FA0-8198-F1727A020B34@.microsoft.com...
> Scenerio:
> RS2000. Subscription delivers email with PDF attachment.
> When the email is opened in Outlook client, the attachment is there and
> working as expected.
> Problem:
> When the email is opened in the Outlook Web client (OWA), the paperclip is
> there identifying the attachment, but when the email is opened, there is
> no
> attachment.
> It seems to be happening only with attachment coming from reporting
> services. I can send an email with another PDF file and open it fine.
> The
> PDF files are small in size so I do not think it is a size proble.|||Daniel,
Thank you for the response.
"Daniel Reib [MSFT]" wrote:
> I just tried this and also was not able to see the .pdf file. This appears
> to be a bug to me. I would contact customer support to help get this issue
> resolved.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "chicagoclone" <chicagoclone@.discussions.microsoft.com> wrote in message
> news:B4C39492-DC1A-4FA0-8198-F1727A020B34@.microsoft.com...
> >
> > Scenerio:
> > RS2000. Subscription delivers email with PDF attachment.
> > When the email is opened in Outlook client, the attachment is there and
> > working as expected.
> >
> > Problem:
> > When the email is opened in the Outlook Web client (OWA), the paperclip is
> > there identifying the attachment, but when the email is opened, there is
> > no
> > attachment.
> >
> > It seems to be happening only with attachment coming from reporting
> > services. I can send an email with another PDF file and open it fine.
> > The
> > PDF files are small in size so I do not think it is a size proble.
>
>

Tuesday, March 20, 2012

BUG: using FOR XML AUTO

I have installed this software:
- Windows Server 2003, Enterprise Edition.
- SQL Server 2005, Client tools (Server is in an another machine)
- VirusScan Enterprise 8.0.0
When i execute the next query:
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE ContactID = 1 FOR XML AUTO
VirusScan report me this:
Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
Detected As: Exploit-ObscuredHtml
Detection Type: Trojan
Application: SqlWb.exe
And SQL Server Management Studio:
An error occurred while executing batch. Error message is: Invalid calling
sequence: file stream must be initialized first.
*****
I apreciate any sugestion !
Thank you.TURN OFF anti-virus software on the server!!
A properly configured SQL Server 'should' not need anti-virus software
installed on the server.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ubeimar Vergara" <Ubeimar Vergara@.discussions.microsoft.com> wrote in
message news:9CD7E5A0-C308-44C6-A3EA-A0B4CC2D346F@.microsoft.com...
>I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.|||"Ubeimar Vergara" <Ubeimar Vergara@.discussions.microsoft.com> wrote in
message news:9CD7E5A0-C308-44C6-A3EA-A0B4CC2D346F@.microsoft.com...
>I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
>
Sounds like a bug in your Antivirus program interfering with the operation
of Management Studio on your client computer.
David|||I turn off the antivirus and the query work,
I reported the problem to McAfee...
"Ubeimar Vergara" wrote:
> I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.sql

Monday, March 19, 2012

Bug regarding identity columns?

I'm seeing some weird behavior regarding identity columns in MSSQL 2000.

In a specific client database we have this table:

ID Name SecLevDG Flags
-- -- -- --
1029528 xxx 0 0
1029529 xxx 0 0
1049676 xxx 0 0

While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are

alter table Authority drop constraint apkAuthorityId
alter table Authority drop column SecLevDg
alter table Authority add new_id integer identity

This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:

ID Name Flags new_id
-- - -- --
1029528 xxx 0 16777220
1029529 xxx 0 16777220
1049676 xxx 0 16777220

Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?

DBCC CHECKIDENT returns:
Checking identity information: current identity value '1', current column value '1'.

DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included):
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
[...]
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).

Regards,
Oskar Berggren

WHen I'm creating a new identity column, I use this:

columnName int IDENTITY (1,1)

Perhaps it is balking because you aren't providing a seed value?

Or, if there are consistency errors, there's an issue with the table itself...

|||Also, although I can not find it now, I seem to remember reading that 2005 likes things to be specifically defined as NULL or NOT NULL.|||

I encountered an issue like this in SQL Server 2005 RTM (I think SP1 fixed it). I was able to work around it by setting Max Degree of Parallelism (MAXDOP) to 1 for the transaction and then back to 0 afterwards.

My theory on this is that multiple processors are working to set the identity and the processors somehow end up with the same value. By having it done with a single processor it might run a bit slower but it resolved the issue for me.

I've never seen this issue in 2000, though. I assume you have the latest service packs?

Regards,

Jared

Bug regarding identity columns?

alter table Authority drop constraint apkAuthorityId
alter table Authority drop column SecLevDg
alter table Authority add new_id integer identity

This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:

ID Name Flags new_id
-- - -- --
1029528 xxx 0 16777220
1029529 xxx 0 16777220
1049676 xxx 0 16777220

Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?

DBCC CHECKIDENT returns:
Checking identity information: current identity value '1', current column value '1'.

Regards,
Oskar Berggren

WHen I'm creating a new identity column, I use this:

columnName int IDENTITY (1,1)

Perhaps it is balking because you aren't providing a seed value?

Or, if there are consistency errors, there's an issue with the table itself...

|||Also, although I can not find it now, I seem to remember reading that 2005 likes things to be specifically defined as NULL or NOT NULL.|||

I've never seen this issue in 2000, though. I assume you have the latest service packs?

Regards,

Jared

Wednesday, March 7, 2012

Bug

I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a client that I wrote a script for them to change the clustered
> index
> > from the PK field (which is an identity) to another field. The new
> > clustering field happens to contain some nulls (which is not supposed to
> > happen but..). Anyway, they ran the script and the new table with the
new
> > clustered index was created without apparent errors. Then they ran a
> query
> > that looks like this:
> >
> > SELECT *
> > FROM myTable
> > WHERE clusterField is not null and someOtherField is null
> >
> > The return set returned records where the clusterField is null. I tried
> to
> > duplicate this on one of my servers and didn't have the problem. So I
> went
> > to their office and ran it and the problem persisted. After a couple of
> > hours of testing I found that the problem only existed above a certain
> > threshold of records in the table (about 1.1 million). After not being
> able
> > to find a solution I went home to do some more troubleshooting on some
of
> my
> > servers and found that on one server it had the problem but on two
others
> it
> > didn't have the problem. the one server that had the problem was using
> > service pack 3 while the two that worked were on SP3a. I thought I
found
> > the problem and told my client to make sure that their servers were on
> SP3a.
> > They had on that was on SP3 and one on 3a. They tested the script again
> on
> > the server with 3a and still had the problem. I don't know where to go
> > next. I'm going to post the exact script that I used to exhibit the
> problem
> > in hopes that someone can find a solution or verify that this is a bug
and
> > under what circumstances it occurs.
> >
> >
> >
> > CREATE TABLE [HIST_NEW] (
> > [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> > [LOANOID] [bigint] NULL ,
> > [USERFILEOID] [bigint] NULL ,
> > [LOANPURGEOID] [bigint] NULL ,
> > [SystemDate] [varchar] (8) DEFAULT (''),
> > [SystemDateSQL] [datetime] default getdate(),
> > [SystemTime] [varchar] (6) DEFAULT (''),
> > [SystemTImeSQL] [datetime] default getdate(),
> > [TransactionID] [varchar] (5) DEFAULT (''),
> > [ActionCode] [varchar] (1) DEFAULT (''),
> > [BatchDate] [varchar] (8) DEFAULT (''),
> > [BatchDateSQL] [datetime] default getdate(),
> > [CompanyAgent] [varchar] (20) DEFAULT (''),
> > [DocumentID] [varchar] (20) DEFAULT (''),
> > [EffDate] [varchar] (8) DEFAULT (''),
> > [EffDateSQL] [datetime] default getdate(),
> > [Entry] [int] NULL ,
> > [ExpDate] [varchar] (8) DEFAULT (''),
> > [ExpDateSQL] [datetime] default getdate(),
> > [HistoryNumber] [int] NULL ,
> > [LNUM] [varchar] (18) DEFAULT (''),
> > [MailDate] [varchar] (8) DEFAULT (''),
> > [MailDateSQL] [datetime] default getdate(),
> > [ModCount] [smallint] NULL ,
> > [Policy] [varchar] (15) DEFAULT (''),
> > [UserID] [varchar] (10) DEFAULT (''),
> > [lenderNumber] [char] (4) DEFAULT (''),
> > CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> > ) ON [PRIMARY]
> > GO
> >
> > --create a new clustered index on the LOANOID column
> > create clustered index cix_hist on hist_new (LOANOID) with fillfactor =90
> >
> >
> > declare @.counter
> > set @.counter = 1
> >
> > while @.counter < 3500000
> > BEGIN
> > INSERT HIST_NEW (LOANOID, LNUM)
> > SELECT case when @.counter %6 = 0 then null else @.counter end,
> > case when @.counter %500 = 0 then null else @.counter end,
> >
> > SET @.counter = @.counter + 1
> > END
> >
> >
> > --This query should not return nulls in the LOANOID field but is does in
> > some cases.
> > select lnum, LOANOID
> > from HIST_new
> > where LOANOID is not null
> > and lnum is null
> >
> >
> >
> > I appreciate any insight that can be provided on this issue. Also, I
ahve
> > been running this on SQL Server Standard Edition on Win 2K with SP4 in
all
> > cases and on various types of hardware.
> >
> >
>

Friday, February 24, 2012

browsin from excel

i can connect olap cubes from excel on any client with blank username and password, although i defined roles and users, a user who skips username and password fields can see all cubes wity any browsing tool ( Proclarity, excel etc )

thx in advance

The username and password field in Excel is only used if you have setup HTTP authentication. Otherwise windows integrated security is used. This means that you must have these users or a group that they are members of in a server or database role that has permission to browse the cubes.

Sunday, February 12, 2012

Bottleneck in client notification design

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
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
>
|||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.c om...
> 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>...[vbcol=seagreen]
> 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:
|||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.c om...
> 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>...[vbcol=seagreen]
> 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.c om...

Bottleneck in client notification design

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

Bottleneck in client notification design

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 othe
r
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-47
66-808B-7DB6B087933F@.microsoft.com>...[vbcol=seagreen]
> 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 t
he
> NoticeIndex - code it so that the new row isn't accessible until the
> NoticeIndex is updated. Maybe have a start and end NoticeIndex - update th
e
> 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:
>|||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>...
[vbcol=seagreen]
> 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 th
e
> 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...

Thursday, March 22, 2012

bug? outlook web access

Tuesday, March 20, 2012

BUG: using FOR XML AUTO

BUG: using FOR XML AUTO

Monday, March 19, 2012

Bug regarding identity columns?

Bug regarding identity columns?

Wednesday, March 7, 2012

Bug

Bug

Bug

Friday, February 24, 2012

browsin from excel

Sunday, February 12, 2012

Bottleneck in client notification design

Bottleneck in client notification design

Bottleneck in client notification design

Boot.ini file

Blog Archive

About Me