UNION中主键的重要性 [英] Importance of primary keys in UNION
问题描述
大家好。
正如最近提到的那样，我有一个前端数据库，它只包含一些查询和一堆（相当大的）链接表。由于（非常差）性能的原因，我不得不做一些摆弄，让我的VBA代码构建查询，分别查询每个表，然后UNION结果。做一个UNION查询产生的性能我可能用铅笔和纸张更好。
这个论坛中提到的东西（我认为是NeoPa或mmccarthy）激励我去做一些实验（由于时间压力，我之前一直在推迟）。这导致得出的结论是，如果涉及的表具有主键，则UNION查询的性能将大大提高。这对你来说可能不会让你感到意外，但我没有时间查看它。
我的大桌子没有主键，因为 a 没有空间（他们正好迎接2GB的限制）而 b 一次只能处理一张桌子是不是必须的？表现完全可以接受。我只是转向拆分设计，尝试将所有内容整合到一个界面中。
我想知道的是，我填写了测试，还是我在对一堆工会进行简单查询时，我真的可能会看到极大的速度提升？通过向表添加主键来表？ （我添加的主键与我的测试查询或我将要使用的实际查询完全无关。）
Hi all.
As has been mentioned here recently, I have a front-end database which just includes some queries and a bunch of (rather large) linked tables. For reasons of (very poor) performance, I had to do quite a bit of fiddling around to get my VBA code to build queries which would query each table individually, then UNION the results. Doing a single UNION query produced performance I probably could have bettered with a pencil and paper.
Something mentioned in this forum (by either NeoPa or mmccarthy, I think) inspired me to do some experimentation (which I had previously been putting off due to time pressure). This has led to the conclusion that the performance of a UNION query is hugely improved if the tables involved have primary keys. This may not come as a surprise to you, but I just hadn?t had time to check it out.
My big tables don?t have a primary key because a there isn?t room (they?re right up against the 2GB limit) and b it wasn?t necessary when working with one table at a time ? performance was quite acceptable. I?ve only just moved to the split design to try and consolidate everything into a single interface.
What I?d like to know is, have I stuffed up my testing, or am I really likely to see an enormous speed improvement when doing simple queries against a bunch of ?unioned? tables by adding a primary key to the tables? (The primary key that I added was totally unrelated to my test queries or to the real ones I?ll be using.)
推荐答案
PK非常重要并且需要护理只使用longinteger PK！索引你进行搜索的字段！
之后非常快！
:)
没有PK - 没有快速表现的希望！
The PK is very important and take care use only longinteger PK! Index your fields on which you do the search!
It''s very fast after!
:)
No PK - no hope for fast performance!
我想知道的是我是否已经完成了我的测试，或者在对一堆工会进行简单查询时，我是否真的有可能看到极大的速度提升？通过向表添加主键来表？ （我添加的主键与我的测试查询或我将要使用的真实查询完全无关。）
What I?d like to know is, have I stuffed up my testing, or am I really likely to see an enormous speed improvement when doing simple queries against a bunch of ?unioned? tables by adding a primary key to the tables? (The primary key that I added was totally unrelated to my test queries or to the real ones I?ll be using.)
你肯定会看到改进正如PEB所说。如果无法添加主键，请尝试在表中至少索引一个字段。即使是带有重复项的索引字段也应该有所改进。
You''ll definately see an improvement as PEB says. In cases where it''s just not possible to add a primary key, try to index at least one field in the table. Even an indexed field with duplicates should give some improvement.
我可以在这里进行修正，但我很高兴这是沿着正确的轨道。
首先，仅供参考，Access 2K及以上的大小限制为4GB。 97是最后一个限制为2GB。
请注意，为文本字段安装了Unicode功能，这可以使文本数据加倍，除非你使用Unicode压缩，这对大多数人来说应该是安全的英语世界的数据库。
指数 - 主题是什么！
当RDBMS（例如Access）处理记录集时，它会寻找一个有用的索引它目前处理的处理。所以，如果你运行
I''m open to correction here but I''m quite happy this is along the right tracks.
Firstly, FYI, The size limit of Access 2K and beyond is 4GB. 97 was the last one limited to 2GB.
Beware though, a Unicode feature was installed for text fields which can double your text data unless you use ''Unicode Compression'', which should be safe for most databases in the English speaking world.
Indices - what a subject!
When an RDBMS (Access for instance) processes a recordset, it will look for an index that will help the processing it''s currently on. So, if you run
这篇关于UNION中主键的重要性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
