why run a query takes so long time?

[Guest]

guys, I had a query that required to match some key words of one field to
another. It works well but it takes unreasonable long time to complete.
Namely, about 25-30 mins of waiting. About the date, one field contains about
85000 record (original data) and the other about 2800 (key words). I've done
everything that Access recommend and use performance analyzer thoroughly(eg:
index, compact, simplify criteria, show only necessary field). For the same
exercises, I use another data software called filemaker and it only takes
about 1.5 mins. In fact, I did not do any specific setting for filemaker...

Does anyone know any trick setting in Access to speed up?

 

[Albert D.Kallal]

I would first ensure that the two fields in question are indexed. (are
they?).

85,000 records is quite a small file, and you should be able to match the
whole mess in less then 10 seconds.

In fact, I would bet this should only take about 1 second on a good
machine....

Give some more details as to how you are doing this....and check if those
fields are indexed...

 

[Guest]

Hi Albert

I have to say I am quite new to Access. But I am pretty sure they are both
indexed. I just used the performance analyzer and program indexed them for
me. I did a double check through Table Design View and again confirmed that
they are indexed and duplicate allowed.

By the way, the 85000 records are in text, namely news headlines and the
other field contains public traded company name. Matching is intended to find
among 85000 news headline how many public companies ( and what specific
company) appeared.

I used like operator in the criteria field. Sth like this:

Field name: Headline
Table: News1999
Criteria: Like "*" & [company name] & "*"

Any suggestion? thanks a lot

 

[Albert D.Kallal]

Field name: Headline

Table: News1999
Criteria: Like "*" & [company name] & "*"

Ah, this explains the problem!

A index does work on "part" of a string.

So,
you can have a headline of

Dog Bites Mailman

You can match with

Dog*

Dog bites*

However, the word "bites" is in the middle of the string, and will not be
indexed.

*bites*

So, the above pattern match will search, but NOT use a index.

The above means that the full string must be searched, and a index can not
be used.

You solution is

a) always match a full, or from the "start" eg: company*

b) Use a database that supports what is called "text" indexing.

Sql server supports this, So, you can use ms-access with sql server, but
the default "JET" engine used with a mdb files does not have text indexing.

c) roll your own text indexing system. You should be able to write
something in less then 2 hours of your time.
(hint" use the split command in ms-access,and write out each key word to
a lookup table, and then run your quires on that....).
To be fair, the "JET" engine with ms-access is not designed for "text"
indexing.

So, this explains your poor performance here.

You should ensure that you done a compact and repair, but at the end of the
day, you can match the "start" of the field, or the "whole" field with
indexes, but when you search the "middle", then indexes are not used...