Subject | Re: SQL Query |
---|---|

Author | t_bhoy |

Post date | 2006-09-15T21:06:13Z |

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer

<svein.erling.tysvaer@...> wrote:

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

<svein.erling.tysvaer@...> wrote:

>the IN

> Hmm Alan, assuming that a surveyparticipant only makes the same

> userresponse once for one particular question, you can eliminate

> completely:IN:

>

> 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

>is not

> 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

> a big problem for the counting (every record counted once), butyour IN

> clause is evaluated for each row in 'response r' and then thistime

> consumption multiplies (i.e. r2.userresponse is the problem,be

> r.userresponse is OK). I expect surveyparticipant to be far more

> selective than userresponse, hence my two suggestions above should

> quicker.then

>

> There may still be other ways to improve your select further, but

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