Results 1 to 3 of 3

Thread: Doubt in Design

  1. #1
    Join Date
    Oct 2002
    Posts
    10

    Question Unanswered: Doubt in Design

    I am currently in the process of designing tables for a specific application. I landed up with a table which has roughly around 50 fields. On analysis I found that 25 fields were frequently used during processing while the balance 25 fields were required only ocassionally.

    The doubt is, If I split the table into two tables having a 1 to 1 relation so that the record length of frequently used table is small, will it give any advantage in performance over the usage of table having all 50 fields.


    Any DB good designers please clarify.


    Thanks
    M.Prakash

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Doubt in Design

    I have made a copy of this thread in Database Concepts and Design forum ...

    Well, my opinion ...

    The extent of performance difference between the two depends on whether your application is OLTP or DSS ....

    If your application is OLTP and properly indexed for your search, there may not be much difference in performance ....

    For DSS, yes, I think there will be a significant performance imporvement(for queries involving freq used columns) if the table is split into two, because the number of pages to be read will be smaller when compared to the one with all 50 columns ...

    Do others agree ?


    Cheers

    Sathyaram



    Originally posted by muthuprakash99
    I am currently in the process of designing tables for a specific application. I landed up with a table which has roughly around 50 fields. On analysis I found that 25 fields were frequently used during processing while the balance 25 fields were required only ocassionally.

    The doubt is, If I split the table into two tables having a 1 to 1 relation so that the record length of frequently used table is small, will it give any advantage in performance over the usage of table having all 50 fields.


    Any DB good designers please clarify.


    Thanks
    M.Prakash

  3. #3
    Join Date
    Dec 2002
    Posts
    2

    Re: Doubt in Design

    Originally posted by muthuprakash99
    I am currently in the process of designing tables for a specific application. I landed up with a table which has roughly around 50 fields. On analysis I found that 25 fields were frequently used during processing while the balance 25 fields were required only ocassionally.

    The doubt is, If I split the table into two tables having a 1 to 1 relation so that the record length of frequently used table is small, will it give any advantage in performance over the usage of table having all 50 fields.


    Any DB good designers please clarify.


    Thanks
    M.Prakash
    As Sathyaram suggests it mostly comes from how the table is actualy used. FOr instance on OLTP (online transaction processing) where the information is about a customer and the application usually askes for information on a single customer number for inq or upate then it is unlikely to make much difference.

    If as also suggested you are doing DSS (descision support) where you are querying several columns at once then there are various options. For instance if you always query on three columns then I would cluster data on those colums. This is the best possible performace for a mixed OLTP/DSS application.

    And further if the application is more "adhoc" in nature based on 25 small columns you could (if they are very small) build an alternate index on these columns with the most frequently search data in the start of the index. This way the queries can be solved using just the index and no data reads are required.

    What you want to avoid for sure is big DSS queries that walk across many pages of data when also trying to do OLTP. Often the two databases are separated and tuned for each purpose. Doing DSS on a OLTP database can kill both activities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •