> 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?
This kind of operation is almost always significantly slower. Your query can take advantage of indexes, but DISTINCT requires an examination of individual values. Your execution plan will tell you why in more detail. > What can I do to speed this up as it is a requirement but > 1:15 is unacceptable. I don't know if there's much you can do, but your execution plan should give you some hints if there is. Otherwise, just for comparison, you might try filtering the records in CF instead of SQL. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:255364 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
