Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mnemonic
Yak Posting Veteran
58 Posts |
 Posted - 2011-10-12 : 15:34:50
|
| Hey guys and gals,I have a "Rating" table, which is simply an ID column, and a "Rating" Column.When my website pulls up a product on my website, it also does a select that does AVG(Rating) to pull up the average rating for the product.I now want to be able to search for products that meet x rating and above, but I imagine doing that query with a JOIN to my main table that all products are selected from will have a performance hit. What would your suggestions be? I can run a SELECT ID, AVG(Rating) as AverageRating FROM RATINGS WHERE AverageRating > 4 in just the RATINGS table, and then i have all the ID of all i want to select from the other table, i just dont know exactly how to do that. Or is it even worth it? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-12 : 15:46:01
|
| As long as your Product and Ratings tables are properly indexed you won't notice the performance hit. If you have a problem then post the DDL of the two tables including their keys, indexes, and constraints. Also post the actual query you are using.should be a simple:select p.productName, avg(r.rating) as AverageRatingfrom products pjoin ratings r on r.productid = p.productidgroup by p.productNamehaving avg(r.rating) > 4Be One with the OptimizerTG |
 |
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2011-10-12 : 16:00:31
|
| To be specific, this is my query.SELECT Images.UploadDate, Images.ID, Images.ImageThumb, Images.Tags, AVG(Rating) As AverageRatingFROM Ratings INNER JOIN Images ON Ratings.ID = Images.ID WHERE NSFWFlag = '0' Group by Images.UploadDate, Images.ID, Images.ImageThumb, Images.Tags HAVING AVG(Rating) >= @RatingThe estimated query plan says 50% will be from the clustered index on the Images table and 50% from the nonclustered Ratings table. Look good? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-12 : 18:03:12
|
| Looks good but you tell me - did you suffer a performance hit when you JOINed to [Images] ?Be One with the OptimizerTG |
|
|
|
Mnemonic
Yak Posting Veteran
58 Posts |
Posted - 2011-10-13 : 00:23:51
|
| Now? No. If [images] hits 50,000 rows.. we'll see. Thats what im worried about. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-13 : 06:53:06
|
NSFWFlag?? "Post some sample data and expected output and we'll be better able to help you"  - LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
|
|
|
|
Select average rating without JOIN?