[thelist] (SQL) Query with Multi FK's to the same lookup

[Scott Dexter]

> 
> Prod_ID	OverallRating	FeaturesRating	ValueRating	
> 1		Average		Poor			Excellent
> 2		Good			Good			Good
> 3		Good			Fair
> Excellent
> ...

... Trying to conceptualize this: For each row, there has to be a
lookup to the Description table to get the description text. So at
the minimum, we're looking at rows*ratings columns reads, right?

Well, looking at the Query Analyzer output of the statement, 

SELECT p.prodid, d.Description AS 'Overall', d1.description AS
'Features', d2.description AS 'Value'
FROM tbProd_Rating p INNER JOIN
    tbRatingDesc d ON OverallRating = d.Rating INNER JOIN
    tbRatingDesc d1 ON FeaturesRating = d1.Rating INNER JOIN
    tbRatingDesc d2 ON ValueRating = d2.Rating


That's exactly what we have --

(5 row(s) affected)

Table 'tbRatingDesc'. Scan count 15, logical reads 30, physical reads
0, read-ahead reads 0.
Table 'tbProd_Rating'. Scan count 1, logical reads 1, physical reads
0, read-ahead reads 0.

Now, I wonder if there are optimizer hints we could employ to get
this faster, but I think this is it. I'm sorry it doesn't look as
pretty as you'd like. 

Anyone else have insight? Rudy (I'll fill in the background for ya
offline)?

Scott