sets of fields? For example, I have a tree structure with a table for
adjacency information and a table for "node properties". I can ask
questions about the structure of the tree and generally manipulate nodes
without touching the "node property" table, however, if I want to fetch
"node properties" I have to perform a join with the adjacency table. So,
which is more efficient? One table with lots of fields or 2 related tables
that must be joined in order to execute a query?
Thanks
RobinRobin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> Is it better to have one table with lots of fields or many tables
> containing sets of fields? For example, I have a tree structure with a
> table for adjacency information and a table for "node properties". I
> can ask questions about the structure of the tree and generally
> manipulate nodes without touching the "node property" table, however, if
> I want to fetch "node properties" I have to perform a join with the
> adjacency table. So, which is more efficient? One table with lots of
> fields or 2 related tables that must be joined in order to execute a
> query?
There is not any clear-cut answer to that question. I would say that
rather than looking at performance at first hand, it is better to look
at other aspects. Which model describes the data best? Which model is
easiest to work with? Which model adheres best to the normal forms?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am guessing you did not design this and you are asking because you
don't like the design? Really, if you want to make a case for
efficiency, you should model both ways with the same data and look at
the execution plan. I think the main reason to keep it in two different
tables is for future maintenance and locking, such that one table can
be operated on while not disturbing the other.
No comments:
Post a Comment