拆分数据库性能问题 [英] Split database performance question
问题描述
大家好。
我在分离的Access数据库中看到了其他性能问题消息。有人能指出我对此信息的一个很好的参考吗？
很快，是否有可能克服任何性能问题？例如，我有两个相当大的表（每个超过1000万条记录）在不同的数据库中，具有相同的布局。我感兴趣的领域在两者中都被索引（非唯一）。我可以在一个表中，或在基于任一表的查询中，在一两秒内获得该字段的最小值或最大值。但是如果我使用UNION查询来连接这两个，那么在该查询上请求Min或Max需要我不知道如何。我还没有坐在那里等待它，但我确实等了几分钟。差别只是荒谬。
Hi all.
I have seen mention in other messages of performance issues in a split Access database. Can someone point me to a good reference for info on this?
And just quickly, is it possible to overcome any of the performance problems? For instance, I have two fairly large tables (over 10 million records each) in separate databases, with identical layout. The field I''m interested in is indexed (non-unique) in both. I can get a Min or Max value for the field in either table, or in a query based on either table, in a second or two. But if I use a UNION query to connect the two, requesting a Min or Max on that query takes I-don''t-know-how-long. I haven''t sat around and waited it out yet, but I did wait a couple of minutes. The difference is just ridiculous.
推荐答案
只是一个建议，但你可以先得到两个表的最小值和最大值，然后比较它们。
您可以尝试对生成的查询进行联合查询。记录应该少得多。
Just a suggestion but could you get the min and max of both tables first and then compare them.
You could try a union query of the resulting queries. Should be a lot less records.
大家好。
我在其他消息中看到过提及拆分Access数据库中的性能问题。有人能指出我对此信息的一个很好的参考吗？
很快，是否有可能克服任何性能问题？例如，我有两个相当大的表（每个超过1000万条记录）在不同的数据库中，具有相同的布局。我感兴趣的领域在两者中都被索引（非唯一）。我可以在一个表中，或在基于任一表的查询中，在一两秒内获得该字段的最小值或最大值。但是如果我使用UNION查询来连接这两个，那么在该查询上请求Min或Max需要我不知道如何。我还没有坐在那里等待它，但我确实等了几分钟。差别只是荒谬。
Hi all.
I have seen mention in other messages of performance issues in a split Access database. Can someone point me to a good reference for info on this?
And just quickly, is it possible to overcome any of the performance problems? For instance, I have two fairly large tables (over 10 million records each) in separate databases, with identical layout. The field I''m interested in is indexed (non-unique) in both. I can get a Min or Max value for the field in either table, or in a query based on either table, in a second or two. But if I use a UNION query to connect the two, requesting a Min or Max on that query takes I-don''t-know-how-long. I haven''t sat around and waited it out yet, but I did wait a couple of minutes. The difference is just ridiculous.
MMcCarthy的建议在这里应该有所帮助。
您看到这些的原因性能问题是Access根据有关数据源的信息严重优化查询。我不确切地知道它放弃了什么阶段，但是如果它决定它不能100％可靠地改进基本的SQL它就会按原样使用它。
例如，当它知道记录集是一个带有主键的表时，它通常会通过主键处理数据，因此处理比随机运行数据要快得多，或者更糟糕的是，决定它需要首先对数据集进行排序。
我的猜测是在UNION之后以及它们在远程数据库中的事实，它会忘记它对它的了解源数据集。
MMcCarthy''s suggestion should help greatly here.
The reason that you''re seeing these performance issues is that Access optimises queries heavily depending on information that it has about the data sources. I don''t know exactly at what stage it gives up, but if it decides that it can''t, 100% reliably, improve on the basic SQL it will just use it as is.
For instance, when it knows a recordset is a table with a primary key, it will usually process through the data via the primary key, thereby processing much quicker than running through the data randomly, or worse, deciding it needs to sort the dataset for its use first.
My guess is that after the UNION and the fact that they''re in remote DBs, it ''forgets'' what it knows about the source datasets.
MMcCarthy的建议在这里应该有所帮助。
你之所以重新看到这些性能问题是Access根据有关数据源的信息严重优化查询。我不确切地知道它放弃了什么阶段，但是如果它决定它不能100％可靠地改进基本的SQL它就会按原样使用它。
例如，当它知道记录集是带有主键的表时，它通常会通过主键处理数据，因此处理比随机运行数据要快得多，或者更糟糕的是，决定它需要对数据集进行排序首先使用它。
我的猜测是在UNION之后以及他们在远程数据库中的事实，它会忘记它对源数据集的了解。
MMcCarthy''s suggestion should help greatly here.
The reason that you''re seeing these performance issues is that Access optimises queries heavily depending on information that it has about the data sources. I don''t know exactly at what stage it gives up, but if it decides that it can''t, 100% reliably, improve on the basic SQL it will just use it as is.
For instance, when it knows a recordset is a table with a primary key, it will usually process through the data via the primary key, thereby processing much quicker than running through the data randomly, or worse, deciding it needs to sort the dataset for its use first.
My guess is that after the UNION and the fact that they''re in remote DBs, it ''forgets'' what it knows about the source datasets.
这肯定是我收到的印象 - 例如，它似乎忘记了索引甚至存在。我的意思是，肯定是指数的目标。如果我请求像Min或Max或索引字段这样的内容，它根本不应该转到实际数据，无论它执行的顺序如何。实际上，根据花费的时间（5到10分钟之间）和生成的网络流量（ lot ），我想说在这种情况下它可能会扫描整个表。 br />
至于工会两个最小/最大查询，我很欣赏这个建议，但在这种情况下它是不值得的。我将最小/最大值加载到表单上，因此用户可以选择该范围内的值进行扫描。我已经更改了VBA代码，为每个代码使用单独的查询。上帝知道结果搜索会花多长时间。
啊，我明白了。实际搜索可能需要使用这样的技术 - 查询每个表和UNION结果。呸！大约有六张桌子，还有更多的桌子不时添加。我的想法是有一个UNION查询我可以用于所有的东西而不必一直重写。
哦......好吧...
无论如何，原始问题仍然存在 - 有人能指出我对这些东西的良好参考方向吗？
That was certainly the impression I received - it seems to forget, for instance, that the indexes even exist. I mean, surely that''s what the index is for. If I request somthing like the Min or Max or a of an indexed field, it should never have to go to the actual data at all, regardless of the sequence in which it does so. In fact, based on the time it took (between 5 and 10 minutes) and the amount of network traffic generated (a lot), I would say it probably did scan the entire table in this case.
As for "unioning" the two min/max queries, I appreciate the suggestion but it''s not worth it in this case. I am loading the min/max values onto a form, so the user can then choose values within that range to scan. I have changed the VBA code to use a separate query for each. Lord knows how long the resulting searches are going to take, though.
Ah, I get it. The actual search may need to use such a technique - query each table and UNION the results. Yuck! There are about half a dozen tables, and more being added from time to time. The idea was to have one UNION query I could use for everything and not have to rewrite all the time.
Oh well...
The original question still stands, anyway - can someone point me in the direction of a good reference on this stuff?
这篇关于拆分数据库性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
