We need a Wiki or something to start compiling all the nifty solutions here. Is anyone on the Evolt.org list? They have a nice self building tips library by posters who just surround things with <tip></tip>. It would be interesting to build a cflist parser that throws something like this into an rss feed. Then the community could offer a tip to the feed here and there. Or not.
-e -----Original Message----- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 10:29 AM To: CF-Talk Subject: RE: Every once in a while, I feel smart. A story of displaying a single random image. 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219642 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
