--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

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

Thanks a lot Svein.

I tried the first query which worked great on the question in which
the selection was chosen on, but didnt work on the rest, but a quick
modification to

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
r2.userresponse = ?Responseid
and q.questionid = ?dropdownquestionid
and q.question = ?questionid

done the trick. I have gone from at least 7 minutes on a large
survey to bang on 5 seconds :)