Jason Fisher wrote: > Very cool to have CROSS JOIN explicitly state your intention there. > Normally, of course, you want to avoid CROSS JOIN like the plague, since the > RDBMS has to full index both tables and cross reference every possible > combination, but in your specific case, the performance will probably be > tolerable. > > In other words, a table with 2,000 rows on a CROSS JOIN with another table > with 2,000 rows would have to cross index 4,000,000 times and then load those > 4 million rows into memory for the queryset. In your case, since you're > looking at a single year, your CROSS JOIN will by only 1x the number of > counties, which is likely under 100, so no biggie from a performance > perspective.
Very true. CROSS JOIN is definitely a feature you want to use with your eyes fully open. Which is why I like that I can spell it out explicitly so that a future developer won't think that I accidentally did it with the "tableA, tableB" syntax with a forgotten inner or outer join in the where clause. You are correct, in this specific case it should be acceptable and it does exactly what needs to be done. There are 58 counties in California. The report can be one or more years, but even reporting all years - the data only goes back to 1999 - so we are only looking at a Cartesian table of 580 rows in memory. It will be many years before this will grow to even 1000 rows. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314855 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
