许多数据输入表 - 哪些持有记录？ [英] Many data entry tables - which ones hold records?
问题描述
我们有一个asp.net应用程序，包含大约200个数据输入表单。客户可以
将数据输入任意数量的表格。每个表单的数据都保存在
对应的sql表中。数据输入完成后，需要处理
。这就是问题的起点。
我们如何轻松确定客户在哪些表中拥有数据以及如何最好地选择该数据？
我们并不反对将所有数据放在一个表中。这个
表最终会有大约1500万条记录并且不断地同时对多达5000个用户进行CRUD
操作。
随着足够的硬件，这对于db来说太过分了吗？
We have an asp.net app with about 200 data entry forms. Customers may
enter data into any number of forms. Each form''s data is persisted in
a corresponding sql table. When data entry is complete, it needs to be
processed. Here''s where the questions start.
How can we easily determine in which tables a customer has data and how
best to select that data?
We''re not opposed to putting all the data in a single table. This
table would wind up having ~15 million records and constantly have CRUD
operations performed against it by up to 5000 users simultaneously.
With sufficient hardware, is this too much to ask of the db?
推荐答案
heromull（他是@gmail。 com）写道：
heromull (he******@gmail.com) writes:
我们有一个asp.net应用程序，有大约200个数据输入表单。客户可以将数据输入任意数量的表格。每个表单的数据都保存在相应的sql表中。数据输入完成后，需要进行处理。这就是问题的起点。
我们如何轻松确定客户在哪些表中有数据以及如何最好地选择数据？
我们''并不反对将所有数据放在一个表中。这个
表最终会有大约1500万条记录并且不断地同时对多达5000个用户进行CRUD
操作。
有足够的硬件，这对于db来说太过分了？
We have an asp.net app with about 200 data entry forms. Customers may
enter data into any number of forms. Each form''s data is persisted in
a corresponding sql table. When data entry is complete, it needs to be
processed. Here''s where the questions start.
How can we easily determine in which tables a customer has data and how
best to select that data?
We''re not opposed to putting all the data in a single table. This
table would wind up having ~15 million records and constantly have CRUD
operations performed against it by up to 5000 users simultaneously.
With sufficient hardware, is this too much to ask of the db?
无论200表应该是1,10,74还是200，都不可能在没有了解''的情况下知道
在他们身上。
但是从表现的角度来看，这是不是真的很重要
它是一两百张桌子。提供，即单表
具有正确的索引。
有几种方法可以找到要处理的数据：
1）时间戳列。时间戳列由SQL自动更新
服务器具有单调增加的数据库唯一值。
（二进制，与日期和时间完全无关）。查看数据的过程将跟踪每个表的最新时间戳，并且
检索具有更高时间戳值的行。如果进程本身更新了
行，则需要将查找与状态列组合在一起。这个解决方案的另一个缺点是时间戳列必须被索引，
并且因为每次更新行时它都会更新，所以会有很多
在该指数中徘徊。
2）IDENTITY列。所有表都有一个标识列，然后
该进程将跟踪最近处理的值。使用
这个解决方案你只能处理插入，而不是用户更新现有的
数据。
3）触发器输入有关要处理的行的数据到
表中。同样，您可能需要一种机制来区分用户输入的
更改和处理更改。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
适用于SQL Server SP3的联机丛书
http ：//www.microsoft.com/sql/techinf...2000/books.asp
Whether the 200 tables should be 1, 10, 74, or 200 is impossible to tell
from without knowledge about what''s in them.
But from a performance point of view, it would not really matter whether
it''s one or two hundred tables. Provided, that is, the the single table
has proper indexing.
There are a couple a ways of finding to find data to process:
1) Timestamp column. A timestamp column is automatically updated by SQL
Server with a database-unique value that is monotonically increasing.
(Binary, completely unrelated to date and time). The process that looks
for data would keep track of the most recent timestamp per table, and
retrieve the rows with higher timestamp value. If the process updates the
rows itself, it needs to combine the lookup with a status column. The
drawback with this solution is that the timestamp column must be indexed,
and since it''s updated each time the row is updated, there will be a lot
of shuffling around in that index.
2) IDENTITY column. All tables would have an identity column, and then
the process would keep track of the most recently processed value. With
this solution you can only handle inserts, not if users update existing
data.
3) Having triggers on the that enters data about rows to process into a
table. Again, you may need a mechanism to differentiate between user-entered
changes and changes from your processing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
谢谢，要选择的数据处理不是基于时间的，它基于客户在处理时有数据的任何表格基于
，由客户确定的
。因此，选项三最好是
，与我们当前的流程类似。此外，数据不是由处理更新的b $ b，只是选中。即使有一个表告诉我们哪些表有数据，我们如何构建select语句
只包含那些包含数据的表？
Thanks, the data to select for processing is not time based, it''s based
on whatever tables a customer has data in at the time of processing,
which is determined by the customer. So, option three would be the
best and is similar to our current process. Also, the data is not
updated by the processing, only selected. Even with a table that tells
us which tables have data, how would we build the select statement(s)
to only include those tables with data?
heromull（he******@gmail.com）写道：
heromull (he******@gmail.com) writes:
谢谢，选择处理的数据不是时间基于，它基于客户在处理时拥有数据的任何表格，
由客户决定。因此，选项三将是最好的，并且类似于我们当前的流程。
不是看那个时间与它有关。我确实说过时间戳，但
timestamp数据类型与时间无关。然后再次，如果你已经有一个类似于选项3的过程，那么请继续使用。
此外，数据不会被处理更新，只会被选中。即使
有一个表告诉我们哪些表有数据，我们如何构建select语句只包含那些带有数据的表？
Thanks, the data to select for processing is not time based, it''s based
on whatever tables a customer has data in at the time of processing,
which is determined by the customer. So, option three would be the
best and is similar to our current process.
Not that see what time has to do with it. I did say timestamp, but
the timestamp datatype has nothing to do with time. Then again, if you
already have a process similar to option three, then go with that.
Also, the data is not updated by the processing, only selected. Even
with a table that tells us which tables have data, how would we build
the select statement(s) to only include those tables with data?
我不能这么说。我不知道你的桌子。我不知道在哪种情况下
这个过程运行等等。我会假设因为有200个表，
你将有一个存储过程或SELECT对于那个
表的陈述，我假设所有表通常都有不同的
列。如果它们都具有相同的架构，那么有一个强烈的
表示你应该有一张单独的桌子。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
书籍在线获取SQL Server SP3
http： //www.microsoft.com/sql/techinf...2000/books.asp
I can not say that. I don''t know your tables. I don''t know in which context
this process runs etc. I would assume that since there are 200 tables,
that you would have a stored procedure or a SELECT statement for that
table, as I would assume that all tables would generally have a different
set of columns. If they all have the same schema, then there is a strong
indication of that you should have one single table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于许多数据输入表 - 哪些持有记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
