How can I optimize my data for faster searches

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

Kelvin Beaton wrote:
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
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

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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
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

thanks!


"Larry Kahm" wrote in message
news:MpPQh.6140$be2.5965@trnddc08...
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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
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

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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kelvin Beaton" kelvin at mccsa dot com wrote in message
...
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?