How can I optimize my data for faster searches

[Kelvin Beaton]

I have a table with about 200,000 records and six fields, FirstName,
LastName, SSN, DOB, Gender, AccountNumber.

I need to have about six people be able to search by FirstName, or LastName
or SSN, or DOB.

The data is static so I can give them each a copy of the database with the
data local on their PC.
I have MS SQL Server also if it would help to put the data there.

I'm thinking to use drop down list for them to search.
It would be nice if they were searching by last name, that the first name
could be in the drop down also, but if that makes things even slower, the
we'll have to live with just one field dropdown.

Any words of wisdom on how the make looking up data preactical speed wise?

Thanks

Kelvin

 

[Rick Brandt]

I have a table with about 200,000 records and six fields, FirstName,
LastName, SSN, DOB, Gender, AccountNumber.

I need to have about six people be able to search by FirstName, or
LastName or SSN, or DOB.

The data is static so I can give them each a copy of the database
with the data local on their PC.
I have MS SQL Server also if it would help to put the data there.

I'm thinking to use drop down list for them to search.
It would be nice if they were searching by last name, that the first
name could be in the drop down also, but if that makes things even
slower, the we'll have to live with just one field dropdown.

Any words of wisdom on how the make looking up data preactical speed
wise?
Thanks

Kelvin

Indexes on every field you are searching. Exact match searches are faster
than wild card searches. Possibly would help to compact the file
frequently.

That's about it. Database speed is all about what you have to read from the
disk. Indexes mean reading fewer bytes and compacting means the file is
better organized so the disk heads don't have to move as much.

 

[Allen Browne]

200k records will be just as fast in Access as any other db.

Make sure you have these 4 fields indexed.

Avoid leading wildcards. (Training wildcards can still use the index.)

Create a SQL statement that matches all the criteria entered, when the use
hits the Search button. You can build the search form like this example:
http://allenbrowne.com/ser-62.html
but don't use the leading wildcard.

With that approach, Access should be able to give you the results almost
instantaneously.

 

[Larry Kahm]

quote: " (Training wildcards can still use the index.)"

Yes, training those wild cards can be a problem - whether you are using an
index or a stick!

lol....

Allen typed his quickly and undoubtedly meant "trailing wildcards".

Larry

 

[Kelvin Beaton]

thanks!

quote: " (Training wildcards can still use the index.)"

Yes, training those wild cards can be a problem - whether you are using an
index or a stick!

lol....

Allen typed his quickly and undoubtedly meant "trailing wildcards".

Larry

 

[Paul Shapiro]

The last time I looked an Access combo box was limited to displaying no more
than 64K items. If you have 200K people to search, you might need an
alternative plan. Either add code to the combo box OnChange event so you
don't fill the combo box until the user types a couple of characters
(reducing the row-source list to what matches the data entered so far) or
create a search form where the user fills in text fields with the criteria
and then clicks the Search button.
Paul Shapiro