using instr in join

[Frank]

Hi there,

I'm trying to create a rather simple query and though it seems to run (no
errors, just an endless wait - 6 hrs so far), it's taking forever and have
not seen a completed result yet.
The [Reference 4] field has up to 100 characters, and in there somewhere is
an ID that is needed to match to the [Item Reference]. There are about 200K
Ref 4 records, and 10K Item Ref records.

SELECT DailyOps.[Case No], DailyOps.Amount, DailyOps.[Case Type],
DailyOps.[Reference 4], [TLM N298].[Item Reference], [TLM N298].[Account
Short code], [TLM N298].[Break Type], [TLM N298].[Item Extra String 10], [TLM
N298].[Account Number]
FROM DailyOps
LEFT JOIN [TLM N298]
ON instr(DailyOps.[Reference 4],[TLM N298].[Item Reference])>0;

Should this be taking this long? does anyone have any ideas on making this
run faster? Does the query look sound?
Thanks for any help.
Frank

 

[Marshall Barton]

I'm trying to create a rather simple query and though it seems to run (no
errors, just an endless wait - 6 hrs so far), it's taking forever and have
not seen a completed result yet.
The [Reference 4] field has up to 100 characters, and in there somewhere is
an ID that is needed to match to the [Item Reference]. There are about 200K
Ref 4 records, and 10K Item Ref records.

SELECT DailyOps.[Case No], DailyOps.Amount, DailyOps.[Case Type],
DailyOps.[Reference 4], [TLM N298].[Item Reference], [TLM N298].[Account
Short code], [TLM N298].[Break Type], [TLM N298].[Item Extra String 10], [TLM
N298].[Account Number]
FROM DailyOps
LEFT JOIN [TLM N298]
ON instr(DailyOps.[Reference 4],[TLM N298].[Item Reference])>0;

Should this be taking this long? does anyone have any ideas on making this
run faster? Does the query look sound?

The query looks legal, but that kind of thing is bound to be
slow. After all, that ON clause calculation has to be done
2,000,000,000 times. I would not expect it to take hours,
but it will be slow. If your data is on a networked
machine, maybe the network is overloaded or just not fast
enough.

I doubt it, but, depending on how near the ID is to the
beginning ot the field, it might speed it up a little if the
field, [Reference 4] was indexed and you changed the ON
clause to:

ON DailyOps.[Reference 4] LIKE "*" & [TLM N298].[Item
Reference] & "*"

I guess slow is one of the penalities of imbedding one value
in another value.