Slow Query Query

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.

Re: Slow Query Query

On Oct 29, 6:00 am, "Keith Wilby" 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.

Re: Slow Query Query

"lyle" wrote in message
news:1193654649.120995.156220@k79g2000hse.googlegroups.com.. .
>
> 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.

Could you expand on this please Lyle? Why is it inappropriate?


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

These are both based on flat tables with text fields.

>
> 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).

Duly noted, thanks. I did have Count(*) originally but changed it in my
quest for speed. It didn't appear to make any difference but I'll change it
back.

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

I must admit that I did try to use joins but was unable to get them to play
(my "bad", my logical powers only extend so far). Perhaps I'll take another
look at that method now that the dust has settled on the project. Thanks
for your comments, much appreciated.

Keith.

Re: Slow Query Query

"Keith Wilby" wrote in
news:4725c3b6$1_1@glkas0286.greenlnk.net:

> "lyle" wrote in message
> news:1193654649.120995.156220@k79g2000hse.googlegroups.com.. .
>>
>> 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.
>
> Could you expand on this please Lyle? Why is it inappropriate?

Google Groups are great except when you write a thousand word reply and it
is sent to a virtual wasteland never to be seen again.

So now comes a précis.

The JET query engine is very powerful and complicated. When it first runs a
saved query it takes sometime to create a plan for doing so in the most
efficient and fastest way possible. It saves the plan. So next time the
query is run, there is not the overhead of creating the plan.
But what if we run the saved query in a different context, in your case, as
a sub-query? Jet's plan may not be optimal in this context. But if the plan
is there, it's likely that Jet will use it, regardless.
When you use an sql string as the sub-query, JET is unlikely to associate
that string with the saved query. So it creates a new plan, and that new
plan could result in faster execution than the old plan.

But, as I said, I think this has about a one per cent chance of being
correct.


--
lyle fairfield

Re: Slow Query Query

if the base table for qryPassCount is tblSurvey (where tblSurvey is the
table that the other query is based on) you could try a self join and
exclude the records you need to exclude like this:

select t1.* from tblSurvey t1 Left Join
(select * from tblSurvey where ...) t2 on t1.SurveyTakenID =
t2.SurveyTakenID Where t2.SurveyTakenID Is Null

"Not In" is an excludion statement. As Lyle points out, Join statements
are faster. The trick then, is to write a join statement that excludes
the desired records. Self Joins are real good for that.

Rich

*** Sent via Developersdex http://www.developersdex.com ***