Posted by Erland Sommarskog on 10/23/05 12:43

heromull (heromull@gmail.com) writes:
> 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?

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]