Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Index Guidelines

    Hi folks, ya all are just fine i hope.
    I've a question regarding indexes.
    I've a table that is very often updated. My boss asked me to keep a log of each record when the table is updated. I've created an AFTER UPDATE trigger on the table and throw the old record into a log table on each update. The table would get large earlier. I want the updates on the original table fast, so i don't wanna add any kind of indexes on the log table. The log table will be scarcely queried only if the user has made a mistake and we want the old record back.
    Now, i've column named event(DATETIME) log table that records CURRENT_TIMESTAMP. Should i create a clustered index on this column so the records get in sequence (the values in the column will be unique due to time and seconds being stored). I could be able to access records fastly in the log table on the basis of EVENT column. But how much it would cost the updates on the original table??
    My major concern is to keep the updates on original table fast while logging the old records.
    Usually it's said not to created indexes on date fields. Do i need to create non-clustered index or i am following the correct path.


    Any tips and guidlines; highly appreciated.


    Howdy!

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    If you're accessing the table using a datetime-value on the datetime-field you could create an index on the datetime-field. If you're sure the content of the datetime-column is unique you could create a clustered index instead. However, I would think the records inserted from the old table will be placed in the correct order already.

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    If you're accessing the table using a datetime-value on the datetime-field you could create an index on the datetime-field. If you're sure the content of the datetime-column is unique you could create a clustered index instead. However, I would think the records inserted from the old table will be placed in the correct order already.
    Thanx for ur reply.
    If i create non-clustered index, it would get updated upon each insert into the table, may slow down the updates on original. But if i created clustered index; the records being inserted into the log table would be normal as in order of datetime. What would be of more cost; non-clustered or clustered?
    I guess clustered would rather be good and i could be able to avoid table scan if i query the log table containing millions of records. I am rather concered how much clustered index on EVENT column may cost the updates on original table.


    Howdy!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before you do much of anything, I'd suggest that you check out Lumigent's Log Explorer. It will allow you to "work backward through time" to see what happened, when, by which user in your database. Not cheap, but a really wonderful tool in my opinion.

    If you decide to stay with a logging table, I'd recommend using an IDENTITY column in your history table. It will show you the sequence in which the rows were added, which is all that you really need, and it has negligable performance cost even for very large, very active tables.

    -PatP

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    Log Explorer is great. A cheaper solution is ApexSQL's SQL Log tool http://www.apexsql.com

Posting Permissions

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