Index performance while doing insert

I have a table with 4 columns and I have 3 indexes on this table:

   CREATE TABLE [dbo].CustomerInfo(
        ID [int] IDENTITY(1,1) NOT NULL,
        UserHashID [varchar](20) NOT NULL,
        ShippingID [int] NOT NULL,
        Received [bit] NOT NULL,
     CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_ShippingID] ON [dbo].[CustomerInfo]  (  [ShippingID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashID] ON [dbo].[CustomerInfo]  (  [UserHashID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashIDShippingID] ON [dbo].[CustomerInfo]  (    [UserHashID] ASC,   [ShippingID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

I am inserting about 4-5 million records and this process takes about 45 min. I've realized that if i drop the indexes, the insert is way faster (2-3 min).

Wondering if there are any side effects by dropping the index, doing the insert and rebuilding the indexes after the insert is done. This whole process will take me maybe 5 min compared with 45 min if I have the indexes enabled.

---

No, there are no side effects of dropping the index, doing the insert and re-building the indexes after the insert is done (Assuming nothing else needs to access the table while you perform the insert).

This is quite a common pattern.

[That all said, I'm surprised at the time difference on a table with 4 columns and 3 indexes. Can you post your schema and index definitions]

As pointed out by @PJ8912, there may be some difference in Transaction log logging, depending on how often you backup the transaction log.

Update : Unrelated, but this index

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashID] 
    ON [dbo].[CustomerInfo]  ([UserHashID] ASC)

is redundant as it is covered by this index:

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashIDShippingID] 
    ON [dbo].[CustomerInfo]  ([UserHashID] ASC, [ShippingID] ASC)

---

Depending on the level of transaction logging, the TLogs could fill up with recreating the indexes regularly. If you truncate the indexes to eliminate them, then that operation won't be logged.

The statistics for the execution plans may not be up-to-date after the new indexes are created. You may want to update statistics with FULL SCAN mode.

链接地址: http://www.djcxy.com/p/95682.html

上一篇: 频繁删除后需要重新组织/重建索引？

下一篇: 插入时的索引性能