Slow searching on last name when last name is sorted

  • Thread starter Thread starter vmoore
  • Start date Start date
V

vmoore

I'm using Access 2003
connected to SQL Server 2000

I have a form that has contact information on it that is sorted by
last name, then first name, then middle initial. I have 17,000
records.

When I search for "Smith" in the last name field (I have 50 Smith's),
it takes about 30 seconds to complete. If I search on "Smith" when
the contact fields are not sorted, the search completes instanteously.

Can anyone suggest a way to speed up my searches?

vmoore
 
17,000 names is very small.

I have systems with 10 users,a nd 100,000 records, and the searching is
absolute instant, and we are NOT yet even using sql server, but just plan
old access files sitting on a server!

You need to build some type of search screen, as throwing a user into a huge
data file is not very good.

Here is some ideas as to what you can, and should consider:

http://www.attcanada.net/~kallal.msn/Search/index.html
 
Thanks for the advice!

In this case, I don't think the size is the issue. If the table isn't
sorted, then the search happens instantaneously. In fact, if I remove
sorting on first name, then searching on first name happens fast as
well. It seems that searching on a sorted field causes a lot of
overhead - even though the fields are indexed in the table. Is there
any way to sort the table on the SQL Server side so that Access
doesn't have to do it?

vmoore
 
Back
Top