x

Optimize nested/iterative query/queries

I have a rather long and complex query I need to optimize if at all possible. The problem I'm running into is that I need to set two constants (an count of a column in a derived table and an average of the same column in a derived table) and then use them to arithmatically derive a column of values in a third query.

Here's the actual query I'm using ... any tips for cleaning it up?

DECLARE @avrating Decimal(38,3); DECLARE @avraters Decimal(38,3);

SET @avrating = Cast((SELECT Avg(Agg.Rating) FROM (SELECT s.id AS id, Rating = AVG(r.calculated_rating) FROM shows AS s INNER JOIN listeners_shows_ratings AS r ON s.id = r.show_id WHERE s.selectable = 'True' GROUP BY s.id) Agg) As Decimal(38,3))

SET @avraters = Cast((SELECT Avg(Agg.Raters) FROM (SELECT s.id AS id, Raters = Cast(COUNT(r.calculated_rating) As Decimal) FROM shows AS s INNER JOIN listeners_shows_ratings AS r ON s.id = r.show_id WHERE s.selectable = 'True' GROUP BY s.id) Agg) As Decimal(38,3))

SELECT s.id, s.name, s.artwork_file_url, (((@avraters * @avrating ) + (AVG(r.calculated_rating)*CAST(COUNT(r.calculated_rating) AS Decimal)))/(@avraters + Cast(Count(r.calculated_rating) As Decimal))) As 'Rating' FROM shows As s INNER JOIN shows_characteristics AS sc ON s.id = sc.show_id INNER JOIN listeners_shows AS ls ON ls.show_id = s.id INNER JOIN listeners_shows_ratings AS r ON s.id = r.show_id WHERE(sc.characteristic_id = 4 OR sc.characteristic_id = 451 OR sc.characteristic_id = 458 AND s.selectable=1) GROUP BY s.id, s.name, s.artwork_file_url ORDER BY 'Rating' DESC
more ▼

asked Oct 20 '09 at 02:19 PM in Default

Eric Mann gravatar image

Eric Mann
59 1 1 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I believe the first 2 sets can be combined into a single SELECT.

DECLARE @avrating Decimal(38,3); DECLARE @avraters Decimal(38,3);

SELECT @avrating = CAST(Avg(Agg.Rating) AS decimal(38,3))
    ,@avraters = CAST(Avg(Agg.Raters) AS decimal(38,3)
FROM
(
    SELECT s.id AS id, Cast(COUNT(r.calculated_rating) As Decimal) AS Raters, AVG(r.calculated_rating) AS Rating
    FROM shows s
    	INNER JOIN listeners_shows_ratings r
    	ON s.id = r.show_id 
    WHERE s.selectable = 'True' 
    GROUP BY s.id
) Agg

Beyond that, I'm not entirely sure I understand the purpose of the code. I reformatted your query trying to figure it out. I agree with Jesse's comment about the AND/OR in the WHERE clause. My main question, though, is this: Why are you calculating the overall average rating and average number of ratings for any given show and then adding that to the calculated rating for a given show?

Here's the reformatted code:

SELECT s.id, s.name, s.artwork_file_url,
    (
    	(
    		(@avraters * @avrating ) +
    		(
    			AVG(r.calculated_rating) * CAST(COUNT(r.calculated_rating) AS Decimal)
    		)
    	)
    	/
    	(
    		@avraters + Cast(Count(r.calculated_rating) As Decimal)
    	)
    ) As 'Rating'
FROM shows s 
    INNER JOIN shows_characteristics sc ON s.id = sc.show_id 
    INNER JOIN listeners_shows ls ON ls.show_id = s.id
    INNER JOIN listeners_shows_ratings r ON s.id = r.show_id 
WHERE(sc.characteristic_id = 4 OR sc.characteristic_id = 451 OR sc.characteristic_id = 458 AND s.selectable=1)
GROUP BY s.id, s.name, s.artwork_file_url 
ORDER BY 'Rating' DESC

Essentially, this query will shift the ratings for individual shows to be closer to the overall average. Is this really what you want it to do?

more ▼

answered Oct 20 '09 at 04:08 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Tom,

I tried your method to bunch the first two queries and it worked beautifully! I've been able to reduce the size of the query by half and the processing time as well. Is there any possibility of reducing it further?
Oct 21 '09 at 02:14 PM Eric Mann
Code reduction? I don't think so. Performance improvement? Review indexes on the tables involved. Check the execution plan.
Oct 21 '09 at 04:39 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

Jesse is right, I did want to use this instead:

WHERE s.selectable=1 AND sc.characteristic_id IN (4, 451, 458)

Just that one change has cleaned things up significantly, and I'll try to use Tom's single SELECT method for the first set of queries.

To answer your question, Tom, yes, the behavior of shifting ratings for individual shows closer to the average is what I expect and want to do. This method of working with ratings is meant to make shows with higher numbers of ratings balance with the shows that have just a handful of ratings (The ratings of a show with 1,000,000 fans don't overly dwarf a show with 2 fans).

It's actually called a "Bayesian Rating" and it used by organizations like Netflix as well as my client.

Does that bring what I'm trying to do into a better light?

more ▼

answered Oct 20 '09 at 08:29 PM

Eric Mann gravatar image

Eric Mann
59 1 1 3

Yes, it does, and thanks for the additional information. Reducing the beginning steps from 2 to 1 should not only reduce the code complexity but also improve performance.

For anyone interested in the Bayesian Average, you can learn more here: http://en.wikipedia.org/wiki/Bayesian_average
Oct 21 '09 at 04:44 AM Tom Staab
Eric, make sure if one of the answers was correct and answered your question that you mark it as the answer by clicking on the tick.
Oct 21 '09 at 07:36 AM Melvyn Harbour 1 ♦♦
Melvyn, I'm still new here and didn't understand the point of the tick or how to comment on other people's messages. My apologies for any confusion this caused.
Oct 21 '09 at 02:15 PM Eric Mann
(comments are locked)
10|1200 characters needed characters left

From the original query, this line looks suspicious:

WHERE(sc.characteristic_id = 4 OR sc.characteristic_id = 451 
OR sc.characteristic_id = 458 AND s.selectable=1)

Do you really mean this?:

WHERE (sc.characteristic_id = 4 OR sc.characteristic_id = 451 
OR sc.characteristic_id = 458) AND s.selectable=1

If so, you'll have to change it, since the logic is very different. Also, if you need to change it, you could shorten it to:

WHERE s.selectable=1 AND sc.characteristic_id IN (4, 451, 458)

If your original logic is as intended, then I recommend using parentheses to separate the ANDs and ORs (even if not needed, they help for readability):

WHERE (sc.characteristic_id = 4 OR sc.characteristic_id = 451 
OR (sc.characteristic_id = 458 AND s.selectable=1))

Cheers, Jesse

more ▼

answered Oct 20 '09 at 03:41 PM

Jesse McLain gravatar image

Jesse McLain
106

(comments are locked)
10|1200 characters needed characters left

Based on my understanding of what you are trying to do I'd probably start by doing something like this:

;WITH cteAverages (id INT, avg_rating DECIMAL(38,3), avg_raters DECIMAL(38, 3)) AS  (  SELECT  Avg(Agg.Raters),  Avg(Agg.Rating)  FROM  (  SELECT  s.id AS id,  Raters = Cast(COUNT(r.calculated_rating) As Decimal)m  Rating = AVG(r.calculated_rating)  FROM  shows AS s INNER JOIN  listeners_shows_ratings AS r  ON s.id = r.show_id  WHERE  s.selectable = 'True'  GROUP BY  s.id SELECT  s.id,  s.name,  s.artwork_file_url,  (((CA.avg_raters * CA.avg_rating) + (AVG(r.calculated_rating) * CAST(COUNT(r.calculated_rating) AS Decimal))) / (CA.avg_raters + Cast(Count(r.calculated_rating) As Decimal))) As 'Rating' FROM  shows As s INNER JOIN  shows_characteristics AS sc  ON s.id = sc.show_id INNER JOIN  listeners_shows AS ls  ON ls.show_id = s.id INNER JOIN  listeners_shows_ratings AS r  ON s.id = r.show_id INNER JOIN  cteAverages CA  ON s.id = CA.id WHERE  (sc.characteristic_id = 4 OR  sc.characteristic_id = 451 OR  sc.characteristic_id = 458 AND  s.selectable = 1) GROUP BY  s.id,  s.name,  s.artwork_file_url ORDER BY  'Rating' DESC 
more ▼

answered Oct 20 '09 at 03:01 PM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

This is logically different than the original query because the CTE will contain multiple rows--one for each s.id whereas the original variables were averages over the entire result set.
Oct 20 '09 at 03:56 PM Tom Staab

Jack,

I tried this method, but it proved unsuccessful. The average rating and number of raters is going to be a constant over all rows of the table - this method doesn't satisfy that and seems to throw off the results.
Oct 21 '09 at 02:13 PM Eric Mann
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x341
x34

asked: Oct 20 '09 at 02:19 PM

Seen: 2491 times

Last Updated: Sep 28 '12 at 03:56 AM