什么是MS访问中的快速搜索方式 [英] what is the fast way of search in MS access

问题描述

亲爱的专家;


请问我想知道哪种方式最适合在2003年访问包含5 000 000条记录的表格


这些方式很好用：


Dlookup（）;


在记录集中设置所有记录后使用rs！find进行搜索。


使用过滤器打开表单（是最快的方式）




任何人都可以给我他关于访问搜索的反馈，以及最好和更快的使用方式


>
先谢谢你。


WASSIM S DACCACHE

CCE

解决方案

嗨Wassim。 DLookup在操作中非常非常慢 - 它实际上在每次调用时打开和关闭整个记录集。不要将它用于任何形式的5m记录搜索。


表单过滤应该非常有效（因为这实际上只是将WHERE子句应用于记录集）。


代码中的Find方法可能不是答案，好像有多个记录包含您要搜索的值，您将不得不重复应用Find，直到没有更多匹配，如果在现场没有订购记录，搜索到的性能将受到严重影响。


-Stewart


ps订购数据可以在代码中实现非常有效的搜索（例如，二进制搜索将在不超过23个记录查找的5m记录中找到任何匹配）并且这些搜索可能胜过任何其他方法 - 但数据确实必须在您所在的字段上排序搜索这些将被使用。

哇5M记录。您正在进入数据仓库领域和事务数据库之外。你可以不清理它吗？如果您需要所有历史记录，我会将其卸载到数据仓库（BI）并使用类似Analysis Services的东西。


那么您将获得数据仓库和事务数据库的速度和历史记录（访问）会更有效率。


我的价值仅为2美分。


欢呼，

首先，我想对你的帖子提出反馈意见。



所以当我正在谈论的时候我感觉不舒服5M的记录同时最好的方法是在sql中使用where子句并过滤查询来搜索记录。如果我错了，请纠正我。


我发布这个主题是因为我看到一些软件在Visual Basic 6.0上写入并使用sql server作为能够查找记录或数据的数据库时留下了深刻的印象在一个简单的PIV上运行2到3秒之间的1000万记录。


Visual Basic 6.0是不同的，然后VBA访问或sql server可以提供数据其他功能??


我很高兴收到你关于这个主题的消息。


最好的问候;

wassim s daccache

cce



我

Dear Experts;


Please I would like to know which way is the best to search a table in access 2003 that contains 5 000 000 records

are these ways good to use:

Dlookup() ;

search using rs!find after set all records in a recordset.

Opening a form using filter (is the fastest way)



Can anyone give me his feedback about search in access and what is the best and the faster way to use


Thank you in advanced.


WASSIM S DACCACHE
CCE

解决方案

Hi Wassim. DLookup will be very, very slow in operation - it is in effect opening and closing the whole recordset each time it is called. Don''t use it for any form of search with 5m records.

Form filtering should be very efficient (as this in effect just applies a WHERE clause to the recordset).

The Find method in code may not be the answer, as if there is more than one record with the value you wish to search for you will have to apply Find repeatedly until there are no more matches, and if the records are not ordered on the field searched performance will suffer badly.

-Stewart

ps on ordered data it is possible to implement very efficient searches in code (e.g. binary search will find any match in 5m records in no more than 23 record lookups) and these may outperform any other method - but the data really must be ordered on the field on which you are searching for these to be used.

Wow 5M records. You are getting into the realm of a datawarehouse and out of a transactional database. Can you not clean it up some. If you need all that history I would offload it to a datawarehouse (BI) and use something like Analysis Services.

Then you would have the speed and history from the datawarehouse and your transactional database (Access) would be more efficient.

Just my 2 cents for what its worth.

cheers,

first of all I would like to apreciate your feedback about my post.


so what i undestood that access is feeling fatigue when i''m talking around 5M of records meanwhile the best way is using where clause in sql and filter a query to search a record. Please correct me if i am wrong.

I posted this subject because i was impressed seeing some software writen on Visual basic 6.0 and using sql server as database that capable of finding a record or many records between 10 million within 2 or 3 second runing on a simple PIV.

does Visual basic 6.0 is different then VBA on access or sql server can provide data other features ??

I''ll be more than happy to hear from you about this subject.

best regards;

wassim s daccache
cce



I

这篇关于什么是MS访问中的快速搜索方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！