Thanks Barney! I added an index to the column and that made a HUGE difference.
I will experiment with grouping also. > -----Original Message----- > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 10, 2008 12:30 PM > To: CF-Talk > Subject: Re: query optimization > > I've had some luck with flip-flopping between > > select distinct(field) > from table > order by field > > and > > select field > from table > group by field > order by field > > In some cases the first was faster, in some cases the second, > depending on indexes, the field type, and the DB server (Oracle vs. > MSSQL). Have to experiment and see. > > cheers, > barneyb > > On Thu, Jul 10, 2008 at 9:11 AM, Chad Gray <[EMAIL PROTECTED]> wrote: > > Is there any way to speed up a query that uses DISTINCT on a large > table. > > > > SELECT DISTINCT(COLORCLASS) > > FROM JobTicket > > WHERE COLORCLASS IS NOT NULL > > ORDER BY COLORCLASS > > > > This query takes about 4 seconds because the table is very large. > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308883 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
