Post reply

Better insert performance on a table only with a clustered index or a table without any index on SqlServer 2008?

  • August 15, 2013 at 6:46 am

    #1641773

    Think about it: there is going to be more overhead inserting into a table with indexes Vs one with no indexes.

    The probability of survival is inversely proportional to the angle of arrival.

  • August 15, 2013 at 7:43 am

    #1641801

    This is a classic, it depends problem. Depending on how you structure the indexes and how your data is being loaded, yes, clustered indexes actually speed up insert operations. But, depending on how you structure the indexes and how your data is being loaded, clustered indexes actually slow down insert operations. There was a study published a while back, and I can't find the link right now I looked, that showed just how much better clustered inserts were over non-clustered. But, it was dependent on how that cluster was defined. Non-unique clustered indexes are going to be worse. Random or wildly distributed clusters, such as with a GUID, are going to be worse. But if the data is relatively well structured such that the loads are following a pattern that will not require massive page splits and rearrangements, clustered is going to radically outperform a heap. Brad discusses a lot of the internals details of why these things work this way here[/url].

    I did another search. I think this article from Tibor is the one I was thinking of, but maybe not. Anyway, hopefully it adds to the discussion.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 15, 2013 at 8:16 am

    #1641822

    If the heap and the clustered index both start out completely empty with no data page allocations the heap inserts will be faster. Beyond that, as Grant stated, there are any number of variables that can obfuscate any performance measurements.

    There are so many variables to consider before deciding on an indexing scheme for doing lots of inserts. For my tables that are batch loaded at intervals I drop all indexes, truncate the tables, bulk insert then create the indexes (clustered first). For transaction tables you will have other considerations.

    best recommendations if best performance is critical run some test with several methodologies and choose what works best for your data and concurrent environment.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply