Database performance/space

Discussion in 'Databases' started by rsmith720, Jan 20, 2010.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. rsmith720

    rsmith720

    With regard to performance vs. space on a SQL Server 2005 database which is better? This would apply to an event log of sorts:

    1. Many records with a varchar(2-400) column where every event is logged as a new record.

    or

    2. Fewer records with a text column where every event is appended to the text column.

    Thanks.
     
    rsmith720, Jan 20, 2010
    #1
  2. dmitri

    dmitri DiscountASP.NET Staff

    Writing the new records for each events to a separate varchar(x) column will be better for both: the space and performance. If you append new records to a single column, you violate First Normal Form which tells that your table should not contain repeating groups.
     
    dmitri, Jan 21, 2010
    #2
  3. rsmith720

    rsmith720

    Thank you, that makes sense, although I wasn't referring to non-normalized appending of records within records but rather something more along the lines of this:

    ID varchar(x)
    1 'some event'
    1 'another event'
    1 'final event'
    2 'some event'
    2 'another event'

    vs.

    ID text
    1 'some event'
    'another event'
    'final event'
    2 'some event'
    'another event'

    What I've expressed in example 2 (text) is to update the text column for each event and append the event to the existing data in the text column rather than create a new record.

    Anyway, I do appreciate the analysis and I do agree multiple records with small record lengths are better for space (wasn't sure about performance) over fewer records with large record lengths.
     
    rsmith720, Jan 21, 2010
    #3
  4. dmitri

    dmitri DiscountASP.NET Staff

    Yes, performance is the major difference in those two ways of storing information. In the case you are storing records to separate fields, you just store them, but in the case when you are appending them to text, SQL Server needs to first retrieve the existing records, then apply appending process, and store them back. I am not sure about the exact number, but I believe the first should be several times faster.
     
    dmitri, Jan 21, 2010
    #4
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page