Taco, Ok ... this works, but I want to know how it works. For example, if I have a table of 5000 rows, does SQL:
----------------------------------- SELECT TOP 1 * FROM yourTable ORDER BY NEWID() ------------------------------------- select all the rows assign guids (newid()) order all the rows return 1 back to me? I did this query against a table with 340000 records in it on a busy server - that's a pretty good test, right? select top 1 id, user_id, client_id, type from [transaction] order by newid() The execution plan shows that SQL server does: 1. Table Scan at a cost of 5% of batch to count rows and compute table size 2. Compute Guid from existing values at a cost of 0% - (DEFINE(exprXXX=newid())) 3. Sort and TOPN - sorting the batch cost of 77% (Top 1, Order by exprXXX) 4. Parallelism, gather streams (order by exprXXX) cost of 17% (this is the sort operation I presume 5. TOP - get the first "N" rows based on sort. There's some "smart" stuff going on here that prevents it from the need to create 340,000 guids. The query returned in .5 to 2 seconds in each case - not sure why the variance. That's a fairly lengthy query (as much as 2 seconds), but it is unlikely that I would find myself selected random records from a table of 300000+ rows. Normally, it's only a few hundred rows - as in banner or image rotation. I do see the point of others that caching a query of a few hundred rows on the CF server and keeping the "random" part of the selection on the web server may in many cases be more efficient and practicaly. However, it seems to me that this a viable alternative and does not suffer from the lag time I expected - even on a very large query. Thanks for the tip. -Mark Mark A. Kruger, CFG, MCSE www.cfwebtools.com www.necfug.com http://mkruger.cfwebtools.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219617 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
