Re: Slow Query Query

On Oct 29, 6:00 am, "Keith Wilby" <h...@xxxxxxxxx> wrote:
In a survey database I have a query "qcboCriteria" that uses another query
in its criteria:

Select SurveyTakenID From tblSurveyTaken
Where SurveyTakenID Not In
(Select ID From qryPassCount)

qryPassCount itself references another query and qcboCriteria takes minutes
to run, but if I put the SQL code from qryPassCount into the criteria for
qcboCriteria then it takes just a few seconds:

Select SurveyTakenID From tblSurveyTaken
Where SurveyTakenID Not In
(SELECT SurveyTakenID
FROM qryAnswers
WHERE (((Answer)=-1 Or (Answer)=1) AND ((Include)=True))
GROUP BY SurveyTakenID
HAVING (((Count(ID))=(select count ([Include]) from qryQuestions where
[include] = -1)))
WITH OWNERACCESS OPTION;)

Is the slowness of the first example a function of using too many stored
queries? For info, the purpose of the query is to determine which surveys
have a "Yes" answer count that is less than the total number of questions in
the Questions lookup table (and so constitutes a failure). There's probably
a more elegant way to achieve this too but it's the best I could come up
with. I'm more interested in why one method is slow at this point.

Thanks.

Keith.

We could speculate. My speculation is that JET has saved an
optimization plan for the saced quuey qryPassCount which is
inappropriate for its use as a subquery. When using the dynamic SQL
string of the qryPassCount, it's likely to create a new and superior
optimization plan. But that guess has a one per cent chnce of being
right, in my opinion.

Showplan (http://www.ffdba.com/showplan.htm) may help to understand
what's going on.

And a knowledge of what's in qryAnswers and qryQuestions may be
required to answer this post well.

As a rule I never use "NOT IN" which is very often an extremely slow
predicate. "Having" is my second choice for avoiding. And Count(*) is
reputed to be faster than Count(FieldName).

In my experience JOINs will almost almost always win or tie in a race
for speed.


.