给你的一个问题：

a）用户将一条记录插入到父控股表中

b）触发器将相同的记录插入到子更新表中

c）用户删除他刚输入父控股表的记录


如何通知您的应用程序该记录已被删除？

>

2005年2月10日星期四14:17:40 -0000，Eric J. Holtman写道：


（剪辑）

正如我所说在介绍中，我觉得这应该是一个完全解决的问题。我有一个正确的解决方案，但我只是想知道什么是正确的，最有效的解决方案是。


你好Eric，


我不知道正确，也不知道大多数高效，但不是最简单（也非常有效）的解决方案就是抓住即将在屏幕上显示的数据

？如果用户显示有关Wilkinson位置的详细信息，则无需检查Burroughs的

数据是否已更改，因为它赢了'无论如何都要显示。

如果您的要求实际上只显示最近更改的数据

，我会说

在时间戳字段上有一个索引（我认为）是自杀的。

是非常不真实的 - 如果你需要检索最近的行

改变了，改变的索引实际上是非常有益的。


（注意 - 如果你希望每秒有数百个更新，这个索引可能会>
妨碍了性能，但在这种情况下，显示自上次屏幕刷新以来所有行已经改变了，这些行本身就是愚蠢的。即使结束了

用户每隔30秒会刷新一次，他会面对数千行

进行检查，我怀疑他是否能够o在接下来的30秒刷新间隔之前消化所有信息

...


Best，Hugo

-


（删除_NO_和_SPAM_以获取我的电子邮件地址）

Hugo Kornelis< hugo@pe_NO_rFact.in_SPAM_fo>写在

新闻：1r ******************************** @ 4ax.com：

我不知道正确，也不知道大多数。高效，但不是最简单（也非常有效）的解决方案就是抓住即将在屏幕上显示的数据？如果用户显示有关Wilkinson位置的详细信息，则无需检查Burroughs的数据是否已更改，因为无论如何都不会显示。


我希望。嘿。


显示应用程序非常向下钻取导向，实际上

每个正在观看*的人都需要看到所有的位置。

当然，他们并没有直接查看5,000个股票仓位，但他们确实看到总计等等，因此需要获得通知
每次更改都需要
。

非常不真实 - 如果您需要检索最近更改过的行，那么更改后的索引实际上会非常有用。


我想我可以试试这个。我刚刚假设，根据阅读，

时间戳的指数是不鼓励的，因为它们往往会影响性能。我想权衡的是，如果服务器花费了很多时间更新索引，我不需要花费时间来检查表格的20个用户应用程序。


要检查的行，我怀疑他能够在下一个30秒的刷新间隔之前消化所有的信息......）

感谢您的投入，但同样，由于我们开展业务的方式，

每个人都需要了解所有职位。另外

到总计这样，GUI会做其他有趣的事情

喜欢检查*所有*位置，但只显示那些对于用户来说很有趣的b $ b可配置的有趣值。

显然，在这种情况下，我需要保持所有控股更新，

以便我可以决定用户基础，哪些是有趣的。

I feel like the answer to this should be blinding obvious. I also
feel like it''s probably an exercise in an undergraduate database
design course. I''m off to google for an answer, but I figure I''d
throw it out here to see if there''s a quick and dirty solution.

I have in my database a table of every position we have here called
Holdings. It would be equivalent to an Inventory table in every
basic design course.

There are many users who could update that table, and also many users
whose display programs wish to have correct values. Here''s what
I currently do:

There''s a timestamp column in that file. My display apps keep track
of what the max timestamp value they''ve seen is. Every time they
want to do a sweep of the database, they go and "get everything
that''s newer than timestamp X", update their quantities, and display.

Now, the procedure "get everything newer than timestamp X" is where
my question lies.....

It requires (of course) a scan of the entire Holdings table, since
having an index on a timestamp field would be (I think) suicidal.
There''s got to be a better way to do that.

I''ve played around with another solution, which is to have a trigger
on Holdings for insert/update, and in that trigger, I save into
another table the PK of the Holdings which change. That "updated"
table also has an identity field, which is the PK of "updated".

Then, instead of keeping track of the max timestamp and scanning
the Holdings table, I keep track of the max identity in "updated",
and just grab the new rows from there, which are usually only a few
(and are easy to find using the PK clustered index), and then grab
individual rows from Holdings using the stored PK.

As I said in the intro, I feel this should be a completely solved
problem. I have a "correct" solution, but I''m just wondering what
the "correct, and most efficient" solution is.

Thanks,

Eric.

A question for you:
a) user inserts a record into parent Holdings table
b) trigger inserts same record into child Updates table
c) user deletes the record he just entered into parent Holdings table

How do you inform your app that record has been deleted?

On Thu, 10 Feb 2005 14:17:40 -0000, Eric J. Holtman wrote:

(snip)

As I said in the intro, I feel this should be a completely solved
problem. I have a "correct" solution, but I''m just wondering what
the "correct, and most efficient" solution is.
Hi Eric,

I don''t know about "correct", nor about "most" efficient, but wouldn''t the
easiest (and also pretty efficient) solution be to just grab the data
that''s about to be displayed on the screen? If a user is displaying
details about the position for Wilkinson, there''s no need to check if the
data for Burroughs has been changed, as it won''t be displayed anyway.
And in case that your requirement is actually to display only the data
that has recently been changed, I''d say that
having an index on a timestamp field would be (I think) suicidal.

is quite untrue - if you need to retrieve the rows with the most recent
changedate, an index on changedate would actually be very beneficial.

(Note - if you expect hundreds of updates per second, this index might
hamper performance, but in that case, displaying all rows that were
changed since the last screen refresh is silly in itself. Even if the end
user would refresh every 30 seconds, he''d be faced with thousands of rows
to inspect and I doubt he''d be able to digest all that information before
the next 30-second refresh interval...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in
news:1r********************************@4ax.com:

I don''t know about "correct", nor about "most" efficient, but wouldn''t
the easiest (and also pretty efficient) solution be to just grab the
data that''s about to be displayed on the screen? If a user is
displaying details about the position for Wilkinson, there''s no need
to check if the data for Burroughs has been changed, as it won''t be
displayed anyway.

I wish. Heh.

The display app is very much "drill down" oriented, and actually
everyone that''s watching *does* need to see all the positions. Of
course, they''re not looking directly at 5,000 stock positions, but
they do see overall totals and such, and therefore need to be informed
about every change.

is quite untrue - if you need to retrieve the rows with the most
recent changedate, an index on changedate would actually be very
beneficial.

I suppose I can try this. I had just assumed, based on reading,
that indices on timestamps were discouraged because they tend to
impact performance. I guess the tradeoff is if the server spends
a little bit of time updating the index, I don''t need to have 20
user apps spending time inspecting the table.

of rows to inspect and I doubt he''d be able to digest all that
information before the next 30-second refresh interval...)

Thanks for the input, but again, because of the way we do business,
everyone does need to know about all the positions. In addition
to "overall totals" and such, the GUI does other interesting things
like "examine *all* positions, but only display ones that are
''interesting''", for user configurable values of "interesting".
Obviously, in that case, I need to keep all the Holdings up to date,
so that I can decide on a per-user basis, which ones are interesting.