473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

what is the fast way of search in MS access

222 100+
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
Apr 24 '08 #1
11 5831
Stewart Ross
2,545 Expert Mod 2GB
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.
Apr 24 '08 #2
mshmyob
904 Expert 512MB
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,
Apr 24 '08 #3
wassimdaccache
222 100+
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
Apr 24 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
...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
Hi Wassim. The platform used for the coding is irrelevant; it is straightforward to implement a search algorithm in VBA, VB, C++ or any other language. The very fast search you mention that was written in VB is certain to have used one of the highly efficient search algorithms you will find if you do some research on this topic, such as the use of binary search trees, for example.

In Access, as in other database systems, SQL WHERE clauses are interpreted by the native database engine which is optimised for performance. If the filtering criteria are not too involved then filtering a recordset by applying a WHERE clause is likely to be more efficient than using the basic search methods available on recordsets, because of the native database engine's inherent optimisation.

As you have provided no detail on the kind of search you are performing (whole field, string match of partial field, numeric value match etc) we can only answer in generalities.

I too support what mshmyob says; Access is not the most suitable vehicle for storing 5m records efficiently - that was never its purpose. Access has a limit of 2GB on the size of any one database, and 5m records in a table with any more than 0.5kb of fields per record will push its capabilities over its limit in this one table alone. A back-end database server is indeed a better approach for this volume of data.

-Stewart
Apr 25 '08 #5
NeoPa
32,556 Expert Mod 16PB
I would support most of what's been said on this, but I wouldn't agree that DLookup() is necessarily a slow approach. Certainly it would be when used in a loop to find many records, but if you're after a single record then it can be as fast as any of the other methods mentioned.

In essence, DLookup() gets a very bad press, not because it's inherently bad funcionality, but simply because so many people use it in inappropriate ways.
Apr 25 '08 #6
mshmyob
904 Expert 512MB
As Stewart mentioned - In the scenario you are talking about the reason for the spped here is the back end server/software - Mainly MS SQL.

If you migrate your existing transactional database to MS SQL and convert it (denormalize) it to a datawarehouse (Analytical Database) and then use Analysis Services and even Excell Pivot Table capabilities then you would have no problems. 5M+ records is nothing for MS SQL and Analysis Services. By the way Analysis Services comes bundled with MS SQL.

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
Apr 25 '08 #7
ADezii
8,834 Expert 8TB
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
To the best of my knowledge, the fastest and most efficient Method of Finding a Record in such a large Table, would be the Seek Method of either a DAO or ADO Recordset on an Indexed Field. As far as I am aware of, there is no other Method that would be as efficient or quick.
Apr 25 '08 #8
missinglinq
3,532 Expert 2GB
I agree with Ade about DLookup() getting a bad rep, mainly because people use it for things it wan't intended for. It amazes me to see people using a dozen or more lines of code to loop thru a recordset looking for one value, when a one-line DLookup() does the same thing!

I also agree that with 5m+ records, it's probably time to look at using a different back end.

Linq ;0)>
Apr 25 '08 #9
wassimdaccache
222 100+
Hi all;


Thank you again for your feedback.

anyone has additional information that would help me to export all my data into mysql server ?

my forms, queries & reports will be effected?

By the way WHY Doesn't MICROSOFT ACCESS provide us a file size more than 2 GB ???


Best regards;


WASSIM S DACCACHE
CCE
Apr 25 '08 #10
NeoPa
32,556 Expert Mod 16PB
I expect that's to do with the fact that a 32-bit processor can only access addresses (natively) with signed values in its registers up to that value.

I would have thought 4GB (unsigned) would be a more likely ceiling, but who knows what's gone into the code to keep it simple (probably code added in at a much earlier stage but expensive to upgrade as it would mean finding all occurrences in all the code).
Apr 28 '08 #11
NeoPa
32,556 Expert Mod 16PB
...
anyone has additional information that would help me to export all my data into mysql server ?

my forms, queries & reports will be effected?
...
BTW, I don't have any recent experience with this but I would look for an Access Wizard that might help with migrating the data across.

Certainly there's one for splitting a standard databse to work as a Front-end/Back/end setup. This would be similar to what you need, but the BE would need to be SQL Server rather than Access.

Your FE Access database should be left with all the reports and forms and stuff so they won't ever need to be converted.
Apr 29 '08 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: Sims | last post by:
Hi, I know this is slightly OT but before i can get to the programming part i want to make sure that i have the structure covered. I have a 2D map and items in my list/vector will be lines on...
0
by: SoftComplete Development | last post by:
AlphaTIX is a powerful, fast, scalable and easy to use Full Text Indexing and Retrieval library that will completely satisfy your application's indexing and retrieval needs. AlphaTIX indexing...
8
by: Neil | last post by:
I have a very puzzling situation with a database. It's an Access 2000 mdb with a SQL 7 back end, with forms bound using ODBC linked tables. At our remote location (accessed via a T1 line) the time...
6
by: DC | last post by:
Hi, I am programming a search catalogue with 200000 items (and growing). I am currently using the SQL Server 2000 fulltext engine for this task but it does not fit the requirements anymore. ...
10
by: javuchi | last post by:
I just want to share some code with you, and have some comments and improvements if you want. This header file allocates and add and delete items of any kind of data from a very fast array: ...
1
by: Harry Haller | last post by:
What is the fastest way to search a client-side database? I have about 60-65 kb of data downloaded to the client which is present in 3 dynamically created list boxes. The boxes are filled from 3...
34
by: radink | last post by:
Well, I've got my DB ready to go. Now what? I need to host it on our Win2003 server. How do clients use it? I think im getting more confused as I try to figure this out. We are a small company...
4
by: Ty | last post by:
Hi all Short version of my problem: i have a Datagrid (Flexgrid from ComponentOne) with a Datatable as source. I need to search a row in the datatable, using a primary key column in the...
9
by: Salad | last post by:
I have access, for testing at my client's site, a Win2000 computer running A2003 retail. He recently upgraded all of his other machines to DualCore Pentiums with 2 gig ram and run A2003 runtime. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.