Hi

I have some querys that do something like this.

SELECT * FROM tblA where FID IN (SELECT FID FROM tblB where user="BOB"
and type=2) AND tblA.type=3

I could change them to

SELECT * FROM tblA where EXISTS (SELECT FID FROM tblB where tblA.FID=
tblB.FID and user="BOB" and tblB.type=2) AND tblA.type=3


Cannot notice any speed improvement, but cannot be certain how to do a
reliable test.

How would you advise testing this to get around things that Access/JET
caches previous results.

Yitzak wrote:
I might try opening up the query designer, dropping both table in,
making sure a relationshop line exits on the FID between both tables,
dropping your fields in that you want to display or filter on. Set the
filter and then run.

Yitzak <terryshamir (AT) yahoo (DOT) co.uk> wrote in news:47956fb5-93c1-4714-87db-
f1f8af167132 (AT) z5g2000yqn (DOT) googlegroups.com:

"WHERE ... IN" is often inefficient to the extreme. "NOT IN" sometimes
finishes but "NOT IN" your lifetime.

Air Code:
SELECT * FROM tblA
INNER JOIN tblB
ON tblA.FID = tblB.FID
WHERE tblA.type=3
AND tblB.user = 'BOB'
AND tblB.type=2

real life -> my tables

SELECT * FROM Schools
INNER JOIN ActualClassOrganization
ON Schools.ID = ActualClassOrganization.School
WHERE Schools.HDSBID > 1150
AND ActualClassOrganization.Program = 1
AND ActualClassOrganization.JK = 19

--
lyle fairfield

- The man who told us that Canada wouldn't go there has now told us that
Canada will be the first to come back. How reassuring!

On 23 Apr, 21:24, lyle fairfield <lylef... (AT) yah00 (DOT) ca> wrote:
Thanks Lyle

I sort of guessed this, but when trying queries times were similar -
but not a good test comparing 2 versions of Query that produce same
results.

Acess/Jet has cached results so next time you do query will be quicker
(is my assumption correct?)

Yitzak <terryshamir (AT) yahoo (DOT) co.uk> wrote in news:7622d3f4-163d-4386-aec4-
8cdd963449d1 (AT) a7g2000yqk (DOT) googlegroups.com:

You mean within the same connection? Or from day-to-day?

Regardless, I think the answer is Maybe.

Jet uses an optimized plan. If conditions change (number of records, value
of parameter, proportion of nulls) then the plan may no longer be optimal
and we may be visiting Crawlsville.

I almost never (maybe never never) use saved queries. I like to understand
what I'm doing and query strings help me do that. Saved queries are often
dialog/wizard created queries; these are always ugly, often inefficient and
sometimes indecipherable. And then there's the inefficient saved
optimization plan potential I mentioned above.
Few agree with me about queries (and a lot of other things).

--
lyle fairfield

- The man who told us that Canada wouldn't go there has now told us that
Canada will be the first to come back. How reassuring!

Yitzak <terryshamir (AT) yahoo (DOT) co.uk> wrote:

Access/Jet does do some caching. To what extent I don't know. But I've noticed
that when rerunning reports a second or third time, they are much faster.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote:

I think I've noticed Jet TMP files in the temp folder but I sure could be wrong.

Agreed.

<chuckle> I'd been programming for at least a decade before Access 1.0 so I was
immediately comfortable with VBA. However it took a while to grasp the concept of
bound controls and labels and such as well as event driven programming.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/