Hmm Alan, assuming that a surveyparticipant only makes the same
userresponse once for one particular question, you can eliminate the IN
completely:

select count(r.userresponse) as numberofanswers
from response r
join response r2 on r2.surveyparticipant = r.surveyparticipant
join question q on q.questionid = r2.questionid
where r.userresponse = ?CurrentResponse
and r2.userresponse = ?Responseid
and q.questionid = ?dropdownquestionid

If this is not the case, you may want to use an EXISTS rather than IN:

select count(userresponse) as numberofanswers
from response r
where r.userresponse = ?CurrentResponse
and exists(select * from response r2
join question q on (q.questionid = r2.questionid
where r.surveyparticipant = r2.surveyparticipant
and r2.userresponse = ?Responseid
and q.questionid = ?dropdownquestionid)

Your problem is that there are many identical userresponses. That is not
a big problem for the counting (every record counted once), but your IN
clause is evaluated for each row in 'response r' and then this time
consumption multiplies (i.e. r2.userresponse is the problem,
r.userresponse is OK). I expect surveyparticipant to be far more
selective than userresponse, hence my two suggestions above should be
quicker.

There may still be other ways to improve your select further, but then
we need to see the plan for these new suggestions.

HTH,
Set

t_bhoy wrote:

> Hi, I have a query that is taking quite a bit of time to compute and
> was wondering if anyone has any pointers on how to improve on this.
>
> The query itself is to return the number of responses from the
> databse from a question, but only from participants selecting a
> particular response of another question. I have read on here counts
> are expensive and so are INs, so using both in the same query is
> probably bad.
>
> The query
>
> select count(userresponse) as numberofanswers
> from response r
> where r.userresponse = ?CurrentResponse and
> r.surveyparticipant in (select r2.surveyparticipant
> from response r2
> inner join question q on (q.questionid = r2.questionid and
> r2.userresponse = ?Responseid and
> q.questionid = ?dropdownquestionid
> )
> )
>
> Plan
> PLAN JOIN (Q INDEX (RDB$PRIMARY4),R2 INDEX (RDB$FOREIGN28))
> PLAN (R INDEX (RDB$FOREIGN28))
>
> Adapted Plan
> PLAN JOIN (Q INDEX (PK_QUESTION),R2 INDEX (FK_RESPONSE_POSRESPONSE))
> PLAN (R INDEX (FK_RESPONSE_POSRESPONSE))
>
>
> I have tested against interbase 6 and firebird 1.5. The above plan
> was created against firebird.
>
> any pointers would be gratefully accepted.
>
> Alan