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?
dhnkleyConnect With a Mentor Commented:
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.

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.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.