Sorry about that. Try this
SELECT 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
group by gd.GenreDisplayName,
gd.GenreDisplayID
ORDER BY GenreDisplayName
>I am running the following query...
>
>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?
>
>What can I do to speed this up as it is a requirement but 1:15 is
>unacceptable.
>
>--
>Jay
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:255416
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
