Large tables vs. joins

[GSMike]

Can anyone help me understand the issues in the debate between large tables and small, joined tables. I prefer the join method, but a lot of people say that the large table approach is more efficient because the joins themselves require resources that defeat their purpose (of course there are instances such as one-to-many relationships where joins are required).
Would you recommend using joins only where necessary, or would you recommend using them whenever possible so that data can be isolated, making it less resource-intensive to combine with other data down the road.
Seems to me that the join method is preferred if you are a long-term-minded person because suppose somone asks you for a report a month from now and you only need three fields, two from one table and one from another. If both tables are relatively small, the job for the server of accessing, say 30 fields to extract the three needed is preferred over accessing perhaps 500 fields (if the tables have a lot of fields) if both of the tables are large. The principle can become amplified over time.
Of course, either approach requires moderation as it is implemented. I was just looking for some discussion on the issue.
Thanks for your help.
-Mikem

 

[foxdev]

Mikem, SQLS doesn't get bogged down selecting specific columns from among many, but from selecting and joining rows.

SQLS builds its lists by traversing data and index trees. If all the data (say, a million rows) is in one table, it can use its index statistics and (hopefully) access the data it needs by sequential reads. If the data is in separate tables (say, 10 tables with 100,000 rows each), it needs to traverse multiple logical indexes, each with their own statistics and, in theory, each requiring its own execution plan, not to mention the additional step of having to join all the results together.

The only time I can think to recommend having separate tables is situations such as "last year" vs. "current year", where 90+% of the queries are on current year, with only very occasional queries needing last year's data. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org