查找表会提高性能吗？ [英] Will a lookup table improve performance?

问题描述

假设我有一张名为tblPeople的桌子，我想要一个字段来说明每个人是喜欢猫还是狗的
。我可以用以下三种方式之一。


1.纯文本字段

在tblPeople中创建一个名为PreferredPet的文本字段。


2.存储文本值的查找字段。

在tblPeople中创建一个名为PreferredPetID的文本字段，并用它来查找

一个相同的文本字段tblPreferredPets。


3.存储整数的查找字段。

在tblPeople中创建一个名为PreferredPetID的字节字段并用它来查找

tblPreferredPets中的相同字段。在tblPreferredPets中还会有一个名为PetType的额外

，它将存储实际值

（猫，狗，鼠标等）。这样只有字节值将存储在

tblPeople而不是文本字符串中。如果在
tblPeople中有成千上万的行，我认为这种方法可以获得性能。


虽然选项三，使表格过滤，报告和查询设计稍微多一点涉及的是正确的存储固定范围

值的方法？


Paul

Suppose I have a table called tblPeople and I want a field to illustrate
whether each person prefers cats or dogs. I could do it one of three ways.

1. A plain text field
Create a text field in tblPeople called PreferredPet.

2. A lookup field that stores text values.
Create a text field in tblPeople called PreferredPetID and use it to lookup
an identical text field in tblPreferredPets.

3. A lookup field that stores an integer.
Create a byte field in tblPeople called PreferredPetID and use it to lookup
an identical field in tblPreferredPets. There will also be an additional
filed in tblPreferredPets called PetType that will store the actual values
(Cat, Dog, Mouse, etc..). This way only byte values will be stored in
tblPeople instead of text strings. This method I would have thought would
benefit performance if there were hundreds of thousands of rows in
tblPeople.

Although option three makes form filters, reports and query design a little
more involved is that the "correct" method for storing a fixed range of
values?

Paul

推荐答案

Paul H写道：

Paul H wrote:

假设我有一个名为tblPeople的表，我想要一个字段来表示每个人是否喜欢猫或狗。我可以这样做

三种方式之一。

1.纯文本字段

在tblPeople中创建一个名为PreferredPet的文本字段。 />

2.存储文本值的查找字段。

在tblPeople中创建一个名为PreferredPetID的文本字段，并将其用于

lookup an tblPreferredPets中的相同文本字段。


3.存储整数的查找字段。

在tblPeople中创建一个名为PreferredPetID的字节字段并将其用于

在tblPreferredPets中查找相同的字段。在tblPreferredPets中还会有一个名为PetType的附加文件，它将存储实际值（Cat，Dog，Mouse等等）的
。这样只有字节值

将存储在tblPeople而不是文本字符串中。如果在tblPeople中有成百上千的行数，那么这种方法我认为会有利于性能。


虽然选项三，使表格过滤，报告和查询设计更多涉及的是正确的存储固定的

范围值的方法？

Suppose I have a table called tblPeople and I want a field to
illustrate whether each person prefers cats or dogs. I could do it
one of three ways.
1. A plain text field
Create a text field in tblPeople called PreferredPet.

2. A lookup field that stores text values.
Create a text field in tblPeople called PreferredPetID and use it to
lookup an identical text field in tblPreferredPets.

3. A lookup field that stores an integer.
Create a byte field in tblPeople called PreferredPetID and use it to
lookup an identical field in tblPreferredPets. There will also be an
additional filed in tblPreferredPets called PetType that will store
the actual values (Cat, Dog, Mouse, etc..). This way only byte values
will be stored in tblPeople instead of text strings. This method I
would have thought would benefit performance if there were hundreds
of thousands of rows in tblPeople.

Although option three makes form filters, reports and query design a
little more involved is that the "correct" method for storing a fixed
range of values?

方法3将是更优选的随着文本条目变得越来越长，更多的是b $ b b，他们可能会在路上改变。当小而稳定的存储

文本没问题。现代

硬件上的速度和空间差异不明显。


-

Rick Brandt，Microsoft Access MVP

电子邮件（视情况而定）至...

在Hunter dot的RBrandt com

Method 3 would be "more preferred" as the text entries get longer and the more
likely they are to be changing down the road. When small and stable storing the
text is fine. The speed and space differences would not be noticeable on modern
hardware.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

在消息< hw * *****************@newssvr27.news.prodigy.net> ;,瑞克

Brandt< ri ********* @ hotmail.comwrites

In message <hw******************@newssvr27.news.prodigy.net >, Rick
Brandt <ri*********@hotmail.comwrites

>虽然选项三使表单过滤器，报表和查询设计更加复杂，但是那个正确的存储固定值范围的方法？

>Although option three makes form filters, reports and query design a
little more involved is that the "correct" method for storing a fixed
range of values?

方法3将是更优选的。随着文本条目越来越长，它们可能会越来越多地改变。小而稳定
存储
文本很好。现代硬件上的速度和空间差异不明显。

Method 3 would be "more preferred" as the text entries get longer and the more
likely they are to be changing down the road. When small and stable
storing the
text is fine. The speed and space differences would not be noticeable
on modern
hardware.

只是为了添加更多细节。什么是正确的方法取决于上下文中的
。最重要的是它取决于数据，这就是为什么通用

答案必须用警告和例外来对冲。


一般来说，如果你改变了数据库的结构远离数据固有的逻辑数据结构，将有一个价格为b
支付。如果您了解数据的结构，并且您知道设计的其他限制，那么您可以评估任何特定设计的成本和收益

，并选择最好的。任何非平凡的

数据库应用程序通常可以通过多种不同的方式实现。


一些因素你可能需要考虑的是：


- 这是一次使用 - 快速修复（如果是这样，可能无法证明很多设计的合理性）/ b $ b b） >
- 用户训练有多好

- 实体之间关系的基数是什么

- 基数是否随时间而变化

- 系统将保留多少记录

- 将有多少插入/更新

- 需要多快更新

- 需要同时访问的用户数量

- 数据库是分布式还是单个位置

- 数据有多干净

- 设计师对数据的理解程度如何？

- 设计师有多好？

我确定我错过了很多。



-

Bernard Peek

返回寻找知识分子

Just to add a little more detail. What the "correct" method is depends
on context. Most of all it depends on the data, which is why generic
answers have to be hedged around with caveats and exceptions.

In general if you alter the structure of a database away from the
logical data structure inherent in the data there will be a price to
pay. If you understand the structure of the data and you know the other
constraints on the design then you can assess the costs and the benefits
of any particular design, and choose the best one. Any non-trivial
database application can usually be implemented in a number of different
ways.

Some of the factors you might need to consider are:

- Is this a use-once quick fix (if so it may not justify lots of design
effort)
- How well-trained are the users
- What is the cardinality of the relationships between entities
- Does the cardinality change over time
- How many records will the system hold
- How many inserts/updates will there be
- How fast do updates need to be made
- How many users will need simultaneous access
- Is the database distributed or single location
- How clean is the data
- How well does the designer understand the data
- How good is the designer

I''m sure I''ve missed out lots here.


--
Bernard Peek
back in search of cognoscenti

Per Paul H：

Per Paul H:

>
虽然选项三形式过滤器，报告和查询设计稍微涉及的是正确的。存储固定范围的值的方法？

>
Although option three makes form filters, reports and query design a little
more involved is that the "correct" method for storing a fixed range of
values?

我不是在讨论技术问题，但对我来说，选项3几乎是

总是很容易。我在最近的经历中可以想到的唯一例外是美国州代码。即使在那里，我有一个tlkpState - 但PK是两个字母的

州代码，所以'是存储在父记录中的内容。


我觉得有一个论点是尽可能在

父表中提供尽可能多的人可读数据 - 所以可以采用州代码方法并应用

更广泛地说 - 使用存储的任何短名称作为查询

表'的PK但最终在我看来你需要控制使用的是什么

和什么不是，查找表和适当的关系似乎是

的逻辑工具。

-

PeteCresswell

I''m not up to discussing the technicalities, but for me option 3 is almost
always the no-brainer. Only exception I can think of in recent experience is
USA state codes. Even there, I have a tlkpState - but the PK is the two-letter
state code so that''s what gets stored in the parent record.

I guess there''s an argument for having as much person-readable data in the
parent table as possible - so one might take the state code approach and apply
it more broadly - using the short name of whatever is being stored as the lookup
table''s PK but in the end it seems to me like you need to control what''s used
and what isn''t and the lookup table and the appropriate relationship seem like
the logical tools for that.
--
PeteCresswell

这篇关于查找表会提高性能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！