[thelist] (SQL) Query with Multi FK's to the same lookup
Scott Dexter
dexilalolai at yahoo.com
Fri May 14 15:00:27 CDT 2004
>
> 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
More information about the thelist
mailing list