what is the fast way of search in MS access

[wassimdaccache]

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

[Stewart Ross]

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.

[mshmyob]

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,

[wassimdaccache]

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

[Stewart Ross]

...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

[NeoPa]

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.

[mshmyob]

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

[ADezii]

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.

[missinglinq]

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)>

[wassimdaccache]

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

[NeoPa]

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).

[NeoPa]

...
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.