Making faster a database

I use VB5 and access.
I have 90,000 records in an access database. One of the field is an string with 600 characters lenght. And when I search for something in that field delay 4 minutes.

How can I make more fast my application???
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zucosAuthor Commented:
Adjusted points to 255
Databases are designed to be applicable on as many problems as possible, that way giving the developer a tool to use for any kind of data processing. BUT! There is a big disadvantage, and that's the bad performance you will experience. What kind of SQL command are you using, maybe can that be optimized, but if not the only solution would be to design your own database specialized for the tasks you want.
I Found I had a similiar problem, just go into access and create an index on the field(s) in question. I found a ten fold increase in speed.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Question ? How did you create a field with 600 Char is length ? A text Field can be 255 char in length and a memo can
take up to 64K of data. If you have created a memo field, you cannot index it.And if you are searching a memo field
you cannot do anything to speed it up.

index ALL fields which uniquely identify each record.

In your case, you MUST index your text field (this is unfortunate, as building that index in access will take time - in another DB environment, that task would be accomplished out-of-hours)

NB Access shouldn't really be used for more than 10,000 records, as it - as vbmaster says - is as compatible as possible - that means SLOW.

I'd suggest SQL Server, or Oracle to be realistic

How are you accessing the database?

ODBC, or direct?

If it's remote then use ODBC if local, then you will have to test querying speeds for yourself.

I recommend ADO every time - be careful to use the correct lock types and cursor types.

A forward-only read-only ADO recordset is about 8 times faster than a Dynamic/Pessimistic recordset.

hope this helps a bit!

Nu :)
If you know what strings you are searching for and these are the ONLY strings that will be searched on,



then you should create an extra field/column in the table to hold just those strings and index the column.  So now you can do an exact match.  Otherwise, it is going to be slow.

You may not be able to change the current setup of your table/db.  I would try to avoid searching based on text in the future if that is possible.

When I was designing my table/field in Access I tried very hard to avoid text search/match in a query.  After long design processes I finally did avoid text query and have all my records indexed on number-fields.

To use indexing, you can replace your memo fields with few text fields:
A) 3 Text(255) fields in one row
B) multiple rows: use 2 fields
   ChunkNum, Integer field and Description, Text(255)
   See table "Declares" in Win32api.Mdb, it's in your \VB\Winapi directory. It uses this technique.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.