I need to load nearly 1.2 million records to one of my table. So, I drop the indexes and load the data to the table. When I try re-create the indexes, it is taking nearly one hour to create the indexes. I feel it is too long. I have attached the source code. Is there any way to speed up.

Source Code:

ALTER TABLE RETDCUST_CUSTADDR
ADD CONSTRAINT [PK_RETDCUST_CUSTADDR] PRIMARY KEY  CLUSTERED
        (
                [CUSNO],
                [ADDRTYPE]
        )  WITH FILLFACTOR = 100
        ON [primary]
        
-- 02.        IX_RETDCUST_CUSTADDR_HOMEAREA
CREATE  INDEX [IX_RETDCUST_CUSTADDR_HOMEAREA]
ON [dbo].[RETDCUST_CUSTADDR](HOMEAREA)
WITH FILLFACTOR = 100

-- 03.        IX_RETDCUST_CUSTADDR_NAMADDR1
CREATE  INDEX [IX_RETDCUST_CUSTADDR_NAMADDR1]
ON [dbo].[RETDCUST_CUSTADDR](NAMADDR1)
WITH FILLFACTOR = 100

-- 04.        IX_RETDCUST_CUSTADDR_NAMADDR2
CREATE  INDEX [IX_RETDCUST_CUSTADDR_NAMADDR2]
ON [dbo].RETDCUST_CUSTADDR(NAMADDR2)
WITH FILLFACTOR = 100

-- 05.        IX_RETDCUST_CUSTADDR_NAMADDR3
CREATE  INDEX [IX_RETDCUST_CUSTADDR_NAMADDR3]
ON [dbo].[RETDCUST_CUSTADDR](NAMADDR3)
WITH FILLFACTOR = 100

-- 06.        IX_RETDCUST_CUSTADDR_NAMADDR4
CREATE  INDEX [IX_RETDCUST_CUSTADDR_NAMADDR4]
ON [dbo].[RETDCUST_CUSTADDR](NAMADDR4)
WITH FILLFACTOR = 100

-- 07.        IX_RETDCUST_CUSTADDR_WORKADDR1
CREATE  INDEX [IX_RETDCUST_CUSTADDR_WORKADDR1]
ON [dbo].[RETDCUST_CUSTADDR](WORKADDR1)
WITH FILLFACTOR = 100

-- 08.        IX_RETDCUST_CUSTADDR_WORKADDR2
CREATE  INDEX [IX_RETDCUST_CUSTADDR_WORKADDR2]
ON [dbo].[RETDCUST_CUSTADDR](WORKADDR2)
WITH FILLFACTOR = 100

-- 09.        IX_RETDCUST_CUSTADDR_WORKADDR3
CREATE  INDEX [IX_RETDCUST_CUSTADDR_WORKADDR3]
ON [dbo].[RETDCUST_CUSTADDR](WORKADDR3)
WITH FILLFACTOR = 100

-- 10.        IX_RETDCUST_CUSTADDR_WORKADDR4
CREATE  INDEX [IX_RETDCUST_CUSTADDR_WORKADDR4]
ON [dbo].[RETDCUST_CUSTADDR](WORKADDR4)
WITH FILLFACTOR = 100

-- 11.        IX_RETDCUST_CUSTADDR_WORKADDR1_WORKADDR2_WORKADDR3_WORKADDR4
CREATE  INDEX [IX_RETDCUST_CUSTADDR_WORKADDR1_WORKADDR2_WORKADDR3_WORKADDR4]
ON [dbo].[RETDCUST_CUSTADDR](WORKADDR1,WORKADDR2,WORKADDR3,WORKADDR4)
WITH FILLFACTOR = 100

-- 12.        IX_RETDCUST_CUSTADDR_HOMPHONE
CREATE  INDEX [IX_RETDCUST_CUSTADDR_HOMPHONE]
ON [dbo].[RETDCUST_CUSTADDR](HOMPHONE)
WITH FILLFACTOR = 100

-- 13.        IX_RETDCUST_CUSTADDR_BUSPHONE
CREATE  INDEX [IX_RETDCUST_CUSTADDR_BUSPHONE]
ON [dbo].[RETDCUST_CUSTADDR](BUSPHONE)
WITH FILLFACTOR = 100

-- 14.        IX_RETDCUST_CUSTADDR_POSTCODE
CREATE  INDEX [IX_RETDCUST_CUSTADDR_POSTCODE]
ON [dbo].[RETDCUST_CUSTADDR](POSTCODE)
WITH FILLFACTOR = 100

-- 15.        IX_RETDCUST_CUSTADDR_POBXNO
CREATE  INDEX [IX_RETDCUST_CUSTADDR_POBXNO]
ON [dbo].[RETDCUST_CUSTADDR](POBXNO)
WITH FILLFACTOR = 100

-- 16.        IX_RETDCUST_CUSTADDR_POBXAREA
CREATE  INDEX [IX_RETDCUST_CUSTADDR_POBXAREA]
ON [dbo].[RETDCUST_CUSTADDR](POBXAREA)
WITH FILLFACTOR = 100

-- 17.        IX_RETDCUST_CUSTADDR_PEGNHOLE
CREATE  INDEX [IX_RETDCUST_CUSTADDR_PEGNHOLE]
ON [dbo].[RETDCUST_CUSTADDR](PEGNHOLE)
WITH FILLFACTOR = 100

-- 18.        IX_RETDCUST_CUSTADDR_MOBILENO
CREATE  INDEX [IX_RETDCUST_CUSTADDR_MOBILENO]
ON [dbo].[RETDCUST_CUSTADDR](MOBILENO)
WITH FILLFACTOR = 100

-- 19.        IX_RETDCUST_CUSTADDR_FAXNO
CREATE  INDEX [IX_RETDCUST_CUSTADDR_FAXNO]
ON [dbo].[RETDCUST_CUSTADDR](FAXNO)
WITH FILLFACTOR = 100

-- 20.        IX_RETDCUST_CUSTADDR_PAGERNO
CREATE  INDEX [IX_RETDCUST_CUSTADDR_PAGERNO]
ON [dbo].[RETDCUST_CUSTADDR](PAGERNO)
WITH FILLFACTOR = 100

-- 21.        IX_RETDCUST_CUSTADDR_MODIFDTE1
CREATE  INDEX [IX_RETDCUST_CUSTADDR_MODIFDTE1]
ON [dbo].[RETDCUST_CUSTADDR](MODIFDTE1)
WITH FILLFACTOR = 100

Hi,

1.2 million records is quite a considerable amount of records when the number of indexes are high such as 21 in your case. Try not creating indexes for fields which are not used frequently in queries (such as in JOINs or WHERE conditions), or on fields which are of a large size. This would only take up more space on your hard disk and more time in rebulding indexes (such as what's happening right now) and not be used at all.

Also in a separate note, are you using FILL FACTOR = 100, since you intend to use this table as readonly?

Regards

---

Gogula G. Aryalingam (MVP - SQL Server)

Hi Gogula,

I am using all these index fields in WHERE condition. Some of these fields contains NCHAR(66) data type (Ex : NAMADDR1,2,3,4 / WORKADDR1,2,3,4).

Yes, this table is a read only table. That's why I am using FILL FACTOR = 100
 

I totally agree with Gogula's comments.

If you use more than one column in your WHERE clauses,  you may be able to remove some of columns from indexes as other columns can be used for searching..

 Additionally,  one of the reason of long time needed for creatiion of Clustered Index.  This moves the data into different pages, I suggest that you should create the clustered index before loading the data.

You have an index on WorkAdd1, Workadd2... I think you should remove the some two columns unless you see a real benifit. - Actually you can add the indexes later if you need

I suggest that you should remove especially the larger columns from having indexes.

 

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!