Quick question about performance....

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Robin Tucker
    #1

    Quick question about performance....


    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?

    Thanks



    Robin


    Tags: None
    • Erland Sommarskog
      #2
      Re: Quick question about performance....

      Robin Tucker (idontwanttobes pammedanymore@r eallyidont.com) writes:[color=blue]
      > 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?[/color]

      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@sommarsk og.se

      Books Online for SQL Server SP3 at
      SQL Server 2025 | Microsoft Evaluation Center
      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
      Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

        Comment

        • sqlmatt@gmail.com
          #3
          Re: Quick question about performance....

          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.

            Comment

            Previous template Next
            Working...