Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Maplewood, MN
    Posts
    2

    Unanswered: Inserts with and without a clustered index...

    Hi,
    I created 2 identical tables with just 12 records each. Then I created a clustered index in one table, but not on the other. I then tried inserting 2 identical records in both the tables. I guessed that in the table without the index the insertion would be faster, because there is no overhead of re-ordering the table based on the clustered index. I found that the insertion is actually faster on the table with the clustered index. How is this possible? Isn't inserts supposed to faster on heap files?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    A while ago I looked into the same matter but found that inserting rows in a table with/out indices took an equal amount of time, that is, the overhead of updating indices didn't change anything. I had quite a bit more rows than 12 though.

    Having that said; I'm not exactly sure how to interpret these kind of results anyway. There could be a lot of influences that affect the sql-processes running in the background that again influence the select or insert results. The only thing I concluded was that, in _my_ case, it didn't matter that much.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, if your table structure is like this
    Code:
    create table dbo.t1 (
       f1 int identity(1,1) not null primary key clustered,
       f2 char(1) null)
    then there is no difference. But if it's like this
    Code:
     create table dbo.t2 (
       f1 uniqueidentifier not null primary key clustered,
       f2 char(1) null)
    then your INSERTS are going to be slower than if you remove CLUSTERED from your PK.

    In addition, it's not the presence of the clustered index that imposes overhead, it's non-clustered indexes that require any action query to be accompanied by an implicit update of index pages.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Oct 2004
    Location
    Maplewood, MN
    Posts
    2
    Thanks guys for the replies. But, doesn't having a clustered index mean that the records in the table are to be physically sorted? Therefore even if there is enough space on the page which should contain the record being inserted, the DBMS would still have to search for that page and insert the record, as opposed to simply appending it at the end, in the case of a heap file (unindexed file).
    Maybe I see this in my example because the table is too small and everything fits into one disk page. How do you think my observations would have been if the table had a million records?

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It still goes to the last extent allocated to the table but does a simple check - is the new value greater than the last value? If the answer is YES - that's the end of the story. Else, - then we are gonna be talking about the overhead of the clustered index.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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