James Smith wrote:
>
> SELECT DISTINCT gd.GenreDisplayName, gd.GenreDisplayID
> FROM ProductData.dbo.t_PI_GenresDisplay gd
> JOIN ProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON
> (gd.GenreDisplayID = lggd.GenreDisplayID)
> JOIN ProductData.dbo.t_PI_Genres g ON
> (lggd.GenreID = g.GenreID)
> JOIN ProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID
> = lmg.GenreID)
> JOIN ProductData.dbo.t_PI_Main m ON
> (lmg.ProductID = m.ProductID)
> WHERE m.checkEdited = 1
> AND m.ProductGroupID = 3
> AND gd.GenreDisplayID != 1
> ORDER BY GenreDisplayName
>
> And it takes 1m 15s to return the 46 rows in the result set.
>
> If I remove the DISTINCT constraint it returns 2732 rows in under a second.
> How can it posibly take so long to remove the duplicates?
Most likely the execution paths between both queries are completely different.
ISTM that without the DISTINCT the query would be driven of an index scan on
t_PI_Main because there are two filters on that (m.checkEdited = 1 and
m.ProductGroupID = 3). But when you add the DISTINCT you have three filters on
t_PI_GenresDisplay (GenreDisplayID != 1, the ORDER BY and the DISTINCT) and the
query is driven from t_PI_GenresDisplay. But that is just speculation, the
proof is in the execution plan of the query.
Anyhow, the question you need to ask yourself is why you need the DISTINCT.
Somewhere, in some table or some JOIN the duplicates are introduced. Find that
place and push the DISTINCT down to there. For instance, if the duplicates are
introduced in t_PI_Main, your query should be:
SELECT gd.GenreDisplayName, gd.GenreDisplayID
FROM
ProductData.dbo.t_PI_GenresDisplay gd
JOIN ProductData.t_PI_L_GenresGenresDisplay lggd
ON (gd.GenreDisplayID = lggd.GenreDisplayID)
JOIN ProductData.dbo.t_PI_Genres g
ON (lggd.GenreID = g.GenreID)
JOIN ProductData.dbo.t_PI_L_MainGenres lmg
ON (g.GenreID = lmg.GenreID)
JOIN (
SELECT DISTINCT ProductID
FROM ProductData.dbo.t_PI_Main
WHERE
m.checkEdited = 1
AND m.ProductGroupID = 3
) m
ON (lmg.ProductID = m.ProductID)
WHERE gd.GenreDisplayID != 1
ORDER BY GenreDisplayName
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255375
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
