splitting a table to aide performance?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: splitting a table to aide performance?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    We have a table TIMED_EVENTS_QUEUE, that stores events and times (like a job schedule). Some of these events will be repeating, (e.g. every day, or just ran 10 times). At the moment this is stored in one table, eg.
    TIMED_EVENTS_QUEUE:
    EVENT_ID
    USER_ID
    EVENT_DESC
    EVENT_TIME
    REPEAT_ON
    REPEAT_INTERVAL
    NEXT_EVENT_TIME
    STATUS
    Now a colleague wants to change this into two tables for performance purposes, e.g.
    TIMED_EVENTS_QUEUE: REPEATING_EVENTS:
    EVENT_ID EVENT_ID
    USER_ID USER_ID
    EVENT_DESC INITIAL_EVENT_TIME
    EVENT_TIME NEXT_EVENT_TIME
    REPEAT_ON INTERVAL
    STATUS a 1-0 relationship
    My query is, if it is a 1-0 relationship and these two tables are always joined when quering or updating, the extra table will be futile, more maintenance (within the application), and use more space. Am I correct, or does anyone see the need for this extra table? Thanks.

  2. #2
    Join Date
    Dec 2000
    Posts
    28
    From the information you have given, it looks like you need to add EVENT_TYPE column and specify if it is time_events or repeating_events and use ONLY ONE table.

    Hope this helps.

    - AD

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Splitting a single table vertically into 2 tables (A,B) with a 1:0,1 relationship is only beneficial in 2 scenarios:

    Scenario 1:

    - You have a table with a number of densely-filled columns and a number of rather sparsely-filled columns.
    - These sparsely-filled columns take up a sizeable amount of room in the row.
    - These sparsely-filled columns are modified and selected much less than the densely-filled columns.

    Basically, the tradeoff is:

    - You are splitting the columns off into another table to save on space in the main table. This will make tablescans faster, allow more of your table in the cache, etc. This benefit is usually not terribly large.
    - When you *do* need this information, you will need to join to this other table. Additionally, *most* DML will will need to occur on 2 tables. This overhead can be substantial.

    Therefore, you had better be saving a *lot* of space and touching that split-off data very rarely to see real benefit from this

    Scenario 2:

    - You have a large table with a lot of information that can be densely-packed, but you generally don't need.
    - There are some specific columns that are only filled for the records you *do* care about.

    For example, you have a database with some huge amount of what we'll call 'leads', or people to cold-call for sales. This data may be populated in some automated fashion. When you actually get somebody on the phone who is interested (in whatever), you get a lot more information on that person. Since this information is *still* pertaining to the lead, you might have originally designed these as columns in the lead table. However, if you split these columns off into another table, you will have a *much* smaller table for your 'customers', which are more important than general leads

    In this case, the benefit is easier to obtain. If most of your DML only deals with those new, 'customer' fields, then those statements will run much faster. But retrieving that 'general' information for the customer (name, etc ) will be *much* slower.

    Of course, what *should* be done in this case is to simply duplicate all the columns from 'lead' into 'customer' and thereafter disregard or delete the 'lead' data for that customer.

    ------------------------

    So, given all that, the general answer is that it is not a good idea. You have to meet some pretty steep criteria for it to make sense, IMHO.

    - Chris

Posting Permissions

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



Click Here to Expand Forum to Full Width