VB and Access Queries

Need help with the next problem:

I have got an Access (97) Database with one Table in it, the table contains about 1 to 2 million records.

In VB (5.0 with ADO 1.5) I am using ParameterQueries in the Database to return search results.
In of the ParmQueries I am using a 'Like' criteria (I need to be able to search for something like 'Name LIKE a*' or 'Town LIKE b*').

Beacuse the Database will be on CD eventually, I need to speed up performance BIGTIME (returning records with 'Name LIKE a*' takes about half a minute(:, the table has an index consisting of searched for 'Name' and 'Town')

If someone could make some suggestions for speeding up performance I would be very gratefull!!!!!!!
Who is Participating?
simonbennettConnect With a Mentor Commented:
Forget it. With that many records it is going to be slow. You could split your main table up so you get a table per letter of the alphabet, although you will need to use union queries if you need to see the whole data set.

Access 2000 should be faster, and maybe think about upgrading to ADO 2.1.1.

Even if you used SQL Server, a like search even on an index is going to be slow. What about having a single character field to hold the first letter of the town or whatever, index it and search that instead with a seek or an =.

Denormalization it may be but it's the only way...
If your searches are always : like A* or b* and not
  abac* or ib*, inotherwords just one letter*.

Then you could maintain two columns

NameFirst, and TownFirst.. these would be char(1) and
would only contain the first letter of your Name and first
letter of you Town.

Then you could run searches on Namefirst=a or Town=b instead of using like.

Which will be very quick. Put an index on these columns too.
Just means you will have an extra 2 columns to maintain on inserting and updating the table.
Buy a really fast processor and CD-ROM drive :)

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Sorry RBertora - we seem to have answered at the same time....
If you always (or most of the time) need to search on the beginning of a word, you can better use > and < (greater than and less than) or the BETWEEN operator. These make use of the index, where the LIKE operator doesn't. So it would be something like:
SELECT * FROM SomeTable WHERE Name > "a" AND Name < "b"
Of course, if you only know a few letters from somewhere in the middle, this doesn't work.
For towns you could use a lookup table with City-names and ID's. Put the ID's in the place of your Town-column. Now you can do a LIKE search in the lookup table (which should be a lot smaller on account of the double names in the original Town-column) and get the Town ID's that comply, then do a search on the (new) indexed TownID column.
If you really want fast access with this quantity of information, you'd better start thinking about deploying some Database-Server package (but that's probably not what you want to hear).

No problem Simon :)
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.