aggregate functions - How to make subquery fast -

for author overview looking query show authors including best book. problem query lacks speed. there 1500 authors , query generate overview taking 20 seconds.

the main problem seems te generating average rating of books per person. selecting following query, still rather fast

select     person.id pers_id,     person.firstname,     person.suffix, person.lastname,     thriller.title,     year(thriller.orig_pubdate) year,     thriller.id thrill_id,     count(user_rating.id) nr,     avg(user_rating.rating) avgrating      thriller  inner join      thriller_form      on thriller_form.thriller_id = thriller.id inner join      thriller_person      on thriller_person.thriller_id = thriller.id      , thriller_person.person_type_id = 1  inner join      person      on person.id = thriller_person.person_id left outer join     user_rating     on user_rating.thriller_id = thriller.id      , user_rating.rating_type_id = 1 thriller.id in     (select top 1 b.id thriller b     inner join thriller_person c on b.id=c.thriller_id     , person.id=c.person_id) group     person.firstname,     person.suffix,     person.lastname,     thriller.title,     year(thriller.orig_pubdate),     thriller.id,     person.id order     person.lastname 

however, if make subquery little more complex selecting book average rating takes full 20 seconds generate resultset. query follows:

select     person.id pers_id,     person.firstname,     person.suffix,     person.lastname,     thriller.title,     year(thriller.orig_pubdate) year,     thriller.id thrill_id,     count(user_rating.id) nr,     avg(user_rating.rating) avgrating      thriller  inner join      thriller_form      on thriller_form.thriller_id = thriller.id inner join      thriller_person      on thriller_person.thriller_id = thriller.id      , thriller_person.person_type_id = 1  inner join      person      on person.id = thriller_person.person_id left outer join     user_rating     on user_rating.thriller_id = thriller.id      , user_rating.rating_type_id = 1 thriller.id in     (select top 1 b.id thriller b     inner join thriller_person c on b.id=c.thriller_id     , person.id=c.person_id     inner join user_rating d on b.id=d.thriller_id     group b.id     order avg(d.rating)) group     person.firstname,     person.suffix,     person.lastname,     thriller.title,     year(thriller.orig_pubdate),     thriller.id,     person.id     order     person.lastname 

anyone got suggestion speed query?

calculating average requires table scan since you've got sum values , divide number of (relevant) rows. in turn means you're doing lot of rescanning; that's slow. can calculate averages once , store them? let query use pre-computed values. (yes, denormalizes data, denormalizing performance necessary; there's trade-off between performance , minimal data.)

it might appropriate use temporary table store of averages.