Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: XML Clob or another table?

    I have a couple of DB related questions that I was hoping someone could provide some insight on. Any assistance or referral site/forum case is appreciated.

    Currently we keep track of our user event logs in a table:
    Event (event_id, event_type, event_outcome)

    We will have soon several million users over time generating events.


    Issue: We want to add extra information related to the event. Not large and not commonly searched upon.
    For example:
    if there is an event type “AUTHENTICATION FAILURE” logged then we may want a reason to be logged or
    If there is an event type “ACCEPT TERMS AND CONDS” logged then we currently want a description of the version of the terms and conditions to be logged

    We are considering two possibilities:

    1st case: One is to add another table called Event_info with columns: foreign key of event_id, info_key, info_value

    2nd case: The other is to add a column of type CLOB to the Event table. The clob object will be an xml doc with informational keys&values defined pertaining to the event. (It was mentioned that this will have less storage and insert speed overhead. But I don’t see why. I don’t see the benefit claimed. I would like to get a better understanding of why this could be so and didn’t have much luck googling. )


    In the first case the only time I insert into the Event_info table will be rare.

    How will there be less speed & storage overhead in the second case?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It was mentioned that this will have less storage and insert speed overhead.
    less than what, exactly?
    Ask those who make such a claim to provide reproducible test case that provides proof above is true.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    Quote Originally Posted by anacedent View Post
    >It was mentioned that this will have less storage and insert speed overhead.
    less than what, exactly?
    Ask those who make such a claim to provide reproducible test case that provides proof above is true.
    I guess I need storage space comparison between:
    storing a clob column containing xml of keys and values pertaining to the event (2nd case)
    vs.
    storing keys and values pertaining to an event in a separate table (1st case)

    And also, I would need to know:
    If I am selecting a row from a table that contains a blob column but am not selecting the blob field, will that impact the speed of retrieving that row? (in the case that the blob/clob is not reference in the select or where clause)

    Thanks for your reply! Much appreciated.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While I applaud the desire to maximize performance, I wonder about the decision methodology.
    If both alternatives provide acceptable performance, then implement the easiest solution.
    If only one alternative provides acceptable performance, then the choice is clear & obvious.
    I suspect the lookup times for non-CLOB to be measurably faster than scanning XML strings.

    Please post benchmark test results so we can learn from your efforts.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2011
    Posts
    3
    Quote Originally Posted by anacedent View Post
    While I applaud the desire to maximize performance, I wonder about the decision methodology.
    If both alternatives provide acceptable performance, then implement the easiest solution.
    If only one alternative provides acceptable performance, then the choice is clear & obvious.
    I suspect the lookup times for non-CLOB to be measurably faster than scanning XML strings.

    Please post benchmark test results so we can learn from your efforts.
    Something that need to be tested in this regard:
    1. inserting into a parent and child table vs. inserting 1 record with a clob into a table.
    We have done tests to retrieve data from table with a clob and without a clob column. In the case that you do not have the clob column in the select or in the where clause --those times are almost the same.

    I will need to test number 1 though.

    2. The other thing a friend pointed out is that it may indeed take less storage to save the info "<xml><key>value</key></xml>" in a clob column than to have a separate table INFO with eventid(foreign key), key, value...
    But would it really... I can see how I would test number 1... but how would I even test 2???

    Personally, I think it would be easier to go with the separate table. It's also neater, better for future cases of possible searching needing to be done on the key/value pairs. But because I know that our data will grow large I don't want to take any chances and would like to make a well thought out decision.

    Maybe the storage difference is too negligible to make a difference? Not sure how to come to the conclusion.

    Thanks again anacedent!

Tags for this Thread

Posting Permissions

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