问：打电话给所有大师 [英] Q: calling all gurus
问题描述
场景：
我们有一些数据可以分为三种状态。一个是保存状态，一个临时状态是
，一个完整状态。
完整状态是大部分数据。这个数据将在1％的时间内请求
，甚至可能更少。
保存状态和临时状态只有10-15条记录。
此数据将在99％的时间内使用，非常频繁。
总而言之，将会有10-15条记录将被使用所有
时间？并且很少会使用50,000-100,000。
与我合作的程序员想要组合表格。我希望
来保持表格的分离。
我的逻辑是，使用10-15条记录会快得多
比筛选投掷了50-100k，其中大部分都不需要99％的
请求。此外，
临时表（10-15条记录）中经常删除/修改。完整的表格不能有
修改或删除（50-100k记录）。这些表格的优化也是非常不同的。
他说，由于结构几乎相同，所以应该是
在同一个表中，我们应该只使用一个字段来表示
不同类型的记录。他坚持认为我的观点是错误的并且
我对数据库一无所知。
我可能不熟悉SQL，但我真的那么吃午饭？难道不是吗？b $ b将这些表分开是有意义的吗？
（感谢您花时间阅读本文）
星期六
在没有机会的情况下在网上提供设计建议总是很难
来分析具体情况详细。但是我的观点，基于给出的
信息，你肯定应该使用一张表。
总是从逻辑模型开始 - 你似乎有一个实体所以
你应该有一张桌子。添加一列以指示状态，并将其指定为b
。该表非常小，从
索引中检索相关行应该给SQLServer带来任何麻烦。更新甚至这个索引的
列应该比在
状态更改时在表之间复制数据更可取。
但是一如既往有性能问题：不要相信我所说的 - 测试它
然后看看。
-
David Portas
------------
请只回复新闻组
-
2003年10月29日07:59:10 -0800，没有****** @ hotmail .com （Saturnin Kepa）
写道：
情景：
我们有一些数据可以在三个州。一个是保存状态，一个临时状态和一个完整状态。
完整状态是大部分数据。这个数据将在1％的时间内被请求，甚至更少。
保存状态和临时状态只有10-15条记录。
这些数据将被使用99％的时间，非常频繁。
总结一下，将有10-15条记录将被用于所有的时间？并且很少使用50,000-100,000。
我工作的程序员想要组合表格。我希望
保持表格顺利。
我的逻辑是，使用10-15条记录要快得多，而不是筛选50-100k大部分都是不必要的99％的请求。此外，
临时表（10-15条记录）中经常有删除/修改。完整的表格不能进行修改或删除（50-100k记录）。这些表的优化也非常不同。
他说，由于结构几乎完全相同，所以应该在同一个表中，我们应该只使用一个字段表示不同类型的记录。他坚持认为我的观点是错误的，而且我对数据库一无所知。
我可能不熟悉SQL，但我真的那么出去吃午饭吗？将这些表分开是不是有意义？
我是SQL设计和管理的相对新手，但我相信
它可能不会有太大的不同。如果您使用聚簇索引（希望是一个整数）构建表格
，并使用额外的
字段来标记已保存，临时和临时。和完成，并索引
附加字段，扫描
索引几乎没有时间丢失，并抓住10-15行中的任何一个前两张桌子。
如果这些是大桌子，它可能会有所不同，但即使如此，也比你想象的要少
;标志字段上的索引基本上是
将表拆分为三个单独的表，只要您的查询
被写入以利用该索引。 （例如：如果你正在寻找具有某种独特属性的某些已保存的
，请确保在查询中指定它是否为
保存，所以SQL Server没有
对你正在寻找的属性进行表扫描。
（同样，作为附注：正确的数据库设计表明你没有
有一个字段允许单词saved，temporary和complete
你应该有一个允许一到三个整数的字段，以及一个
秒表，它等于1表示已保存，2表示临时和3
与完成（或任何顺序看起来最好）。比较整数比比较字符串要快得多，这样可以节省很多空间的b / b
允许更多要缓存的行。）
在没有机会的情况下在线提供设计建议总是很困难团结
详细分析特定情况。但是我的观点，基于给出的
信息，你肯定应该使用一张表。
总是从逻辑模型开始 - 你似乎有一个实体所以
你应该有一张桌子。添加一列以指示状态，并将其指定为b
。该表非常小，从
索引中检索相关行应该给SQLServer带来任何麻烦。更新甚至这个索引的
列应该比在
状态更改时在表之间复制数据更可取。
但是一如既往有性能问题：不要相信我所说的 - 测试它
然后看看。
-
David Portas
------------
请只回复新闻组
-
The Scenario:
We have some data that can be in three states. One is a saved state,
a temporary state, and a complete state.
The complete state is the bulk of the data. This data will be
requested 1% of the time, maybe even less.
The saved state and temporary state will only number 10-15 records.
This data will be used 99% of the time, very frequently.
So to summarize there will be 10-15 records that will be used ALL the
time?. And 50,000-100,000 that will be used very infrequently.
The programmer that I work with wants to combine the tables. I want
to keep the tables sepperate.
My logic is that it will be much faster to work with 10-15 records
than sift threw 50-100k most of which are unnecessary to 99% of the
requests. Also there are frequent deletions/modifications in the
temporary table(10-15 records). The complete table can NOT have
modifications or deletions (50-100k records). The optimization for
these tables is also very different.
He says that since the structures are almost identical they should be
in the same table, that we should just use a field to denote the
different types of records. He insists that my view is wrong and that
I know nothing about databases.
I may be new to SQL, but am I really that out to lunch? Would it not
make sense to keep these tables separate?
(Thank you for taking the time to read this)
Sat
It''s always difficult to give design advice online without the opportunity
to analyse a particular situation in detail. But my opinion, based on the
information given, is that you should definitely use a single table.
Always start with the logical model - you appear to have a single entity so
you should have a single table. Add a column to indicate the status and
index it. The table is so small that retrieving the relevant rows from the
index should give SQLServer no trouble at all. Updating even this indexed
column should be far preferable to copying data between tables when the
status changes.
But as always with performance questions: don''t believe what I say - test it
and see.
--
David Portas
------------
Please reply only to the newsgroup
--
On 29 Oct 2003 07:59:10 -0800, no******@hotmail.com (Saturnin Kepa)
wrote:
The Scenario:
We have some data that can be in three states. One is a saved state,
a temporary state, and a complete state.
The complete state is the bulk of the data. This data will be
requested 1% of the time, maybe even less.
The saved state and temporary state will only number 10-15 records.
This data will be used 99% of the time, very frequently.
So to summarize there will be 10-15 records that will be used ALL the
time?. And 50,000-100,000 that will be used very infrequently.
The programmer that I work with wants to combine the tables. I want
to keep the tables sepperate.
My logic is that it will be much faster to work with 10-15 records
than sift threw 50-100k most of which are unnecessary to 99% of the
requests. Also there are frequent deletions/modifications in the
temporary table(10-15 records). The complete table can NOT have
modifications or deletions (50-100k records). The optimization for
these tables is also very different.
He says that since the structures are almost identical they should be
in the same table, that we should just use a field to denote the
different types of records. He insists that my view is wrong and that
I know nothing about databases.
I may be new to SQL, but am I really that out to lunch? Would it not
make sense to keep these tables separate?
I''m a relative newbie at SQL design and administration, but I believe
that it probably won''t make much difference. If you build the table
with a clustered index (hopefully an integer), and with an additional
field to flag "saved", "temporary" and "complete", and index the
additional field, there will be nearly no time lost in scanning the
index, and grabbing the 10-15 rows in either of the first two tables.
If these were large tables, it might make a difference, but even then,
less than you might think; the index on the flag field essentially
splits the table into three separate tables, so long as your queries
are written to take advantage of that index. (e.g.: if you''re looking
for something "saved" that has a certain unique attribute, make sure
you specify in the query that it''s "saved", so SQL Server doesn''t have
to do a table scan for the attribute you''re looking for.
(Also, as a side note: proper database design suggests that you do not
have a field that allows the words "saved", "temporary" and "complete;
you should have a field that allows integers from one to three, and a
second table that equates 1 with "saved", 2 with "temporary" and 3
with "complete" (or whatever order seems best). Comparing integers is
much faster than comparing strings, and this saves a nice chunk of
space, allowing more rows to be cached.)
It''s always difficult to give design advice online without the opportunity
to analyse a particular situation in detail. But my opinion, based on the
information given, is that you should definitely use a single table.
Always start with the logical model - you appear to have a single entity so
you should have a single table. Add a column to indicate the status and
index it. The table is so small that retrieving the relevant rows from the
index should give SQLServer no trouble at all. Updating even this indexed
column should be far preferable to copying data between tables when the
status changes.
But as always with performance questions: don''t believe what I say - test it
and see.
--
David Portas
------------
Please reply only to the newsgroup
--
这篇关于问：打电话给所有大师的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
