Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


VB and Access Queries

Posted on 1999-07-20
Medium Priority
Last Modified: 2010-04-30
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!!!!!!!
Question by:in
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1526121
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.

Accepted Solution

simonbennett earned 600 total points
ID: 1526122
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...

Expert Comment

ID: 1526123
Buy a really fast processor and CD-ROM drive :)


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

ID: 1526124
Sorry RBertora - we seem to have answered at the same time....

Expert Comment

ID: 1526125
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).


Expert Comment

ID: 1526126
No problem Simon :)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question