Text Field Filter vs. Number Field Filter on same table



Alright, I'm at my wits end with this. I can a text file which
contains somewhere in the neighborhhod on 1.6m records. Originally, I
went down the import path, but obviously the import performance was
slow. I ended up going the linked file route which I originally
discounted due to concerns with search/filter performance. Here is the
quandry - I have a simple form connected to the linked table which
allows the user to enter a value into 1 of 2 fields (same table). One
field filters the result is of type text and uses a "like" filter with
wildcards. The results are returned in a matter of seconds. The second
field filters a numeric long integer field - the results are returned
in a minute or two. Everything I know about databases (right or wrong)
says that a filter or query on a numeric field should always
outperform a text search. I've tried converting the search field to
type long using the clng function and converting the numeric table
field to s string using cstr (via a query). Neither field is indexed
since it is linked Any ideas?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question