Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Index creation taking long time Expand / Collapse
Author
Message
Posted Wednesday, May 07, 2008 1:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 19, 2012 9:22 AM
Points: 52, Visits: 381
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


- Zahran -
Post #496111
Posted Wednesday, May 07, 2008 3:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:39 AM
Points: 287, Visits: 208
Silly question, but why so many indexes?

In some cases, you have NameAddr1, NameAddr2, NameAddr3, NameAddr4, WorkAddr1, 2, 3, 4 etc.

So how often do your queries look for a specific value in NameAddr4?

I get the impression you have an single column index covering just about every column in your table.

Exactly how many of these are actively used for you searches?

Point being if you thin out the number of indexes and possibly consolidate some that better fit the queries (e.g covering indexes, indexes with included columns), you may have a quicker index rebuild task.
Post #496159
Posted Wednesday, May 07, 2008 4:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 19, 2012 9:22 AM
Points: 52, Visits: 381
I am using all these index fields in a WHERE condition. I am using this table for a data conversion purposes. So, I need to search all these fields for invalid records for destination table. Is it recommend to use covering index (Ex : WORKADDR1 + WORKADDR2 + WORKADDR3 + WORKADDR4 ) even though I want to search each fields individually.

- Zahran -
Post #496179
Posted Wednesday, May 07, 2008 5:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:39 AM
Points: 287, Visits: 208
Not sure I understand what you are trying to do/achieve.

Can you give a sample of the sort of processing you are doing?
Post #496205
Posted Wednesday, May 07, 2008 5:47 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 19, 2012 9:22 AM
Points: 52, Visits: 381
Attached a sample of my code

-- 43. Validate English Name Address 1
-- 43.1 NAMADDR1 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,43
,1
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR1,'')='') AND ADDRTYPE='ME'

-- 43.2 NAMADDR1 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,43
,2
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR1) > 40 AND ADDRTYPE='ME'

-- 43.3 NAMADDR1 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,43
,3
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR1)<>NAMADDR1
AND ADDRTYPE='ME'

-- 44. Validate English Name Address 2
-- 44.1 NAMADDR1 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,44
,1
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR1,'')='') AND ADDRTYPE='ME'

-- 44.2 NAMADDR2 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,44
,2
,NAMADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR2) > 40

-- 44.3 NAMADDR2 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,44
,3
,NAMADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR2)<>NAMADDR2
AND ADDRTYPE='ME'

-- 45. Validate English Name Address Line 3
-- 45.1 Validate Null Or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,45
,1
,NAMADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR3,'')='') AND ADDRTYPE='ME'

-- 45.2 NAMADDR1 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,45
,2
,NAMADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR3) > 40

-- 45.3 NAMADDR3 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,45
,3
,NAMADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR3)<>NAMADDR3
AND ADDRTYPE='ME'

-- 46. Validate Name Address 4
-- 46.1 NAMADDR4 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,46
,1
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR1,'')='') AND ADDRTYPE='ME'

-- 46.2 NAMADDR4 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,46
,2
,NAMADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR4) > 40

-- 46.3 NAMADDR4 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,46
,3
,NAMADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR4)<>NAMADDR4
AND ADDRTYPE='ME'

-- 47. Validate Work Address Line 1-4 exeeds 90 charactors
INSERT INTO CustomerReport
SELECT CUSNO
,47
,1
,LTRIM(RTRIM(WORKADDR1))+LTRIM(RTRIM(WORKADDR2))+LTRIM(RTRIM(WORKADDR3))+LTRIM(RTRIM(WORKADDR4))
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN (LTRIM(RTRIM(WORKADDR1))+LTRIM(RTRIM(WORKADDR2))+LTRIM(RTRIM(WORKADDR3))+LTRIM(RTRIM(WORKADDR4)))>90
AND ADDRTYPE='ME'

-- 48. Validate English Work Address1
-- 48.1 WORKADDR1 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,48
,1
,WORKADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR1,'')='') AND ADDRTYPE='ME'

-- 48.2 WORKADDR1 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,48
,2
,WORKADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR1) > 40 AND ADDRTYPE='ME'

-- 48.3 WORKADDR1 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,48
,3
,WORKADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR1)<>WORKADDR1
AND ADDRTYPE='ME'

-- 49. Validate English Work Address2
-- 49.1 WORKADDR2 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,49
,1
,WORKADDR9
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR9,'')='') AND ADDRTYPE='ME'

-- 49.2 WORKADDR2 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,49
,2
,WORKADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR2) > 40 AND ADDRTYPE='ME'

-- 49.3 WORKADDR2 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,49
,3
,WORKADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR2)<>WORKADDR2
AND ADDRTYPE='ME'

-- 50. Validate English Work Address3
-- 50.1 WORKADDR3 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,50
,1
,WORKADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR3,'')='') AND ADDRTYPE='ME'

-- 50.2 WORKADDR3 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,50
,2
,WORKADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR3) > 40 AND ADDRTYPE='ME'

-- 50.3 WORKADDR3 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,50
,3
,WORKADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR3)<>WORKADDR3
AND ADDRTYPE='ME'

-- 51. Validate English Work Address4
-- 51.1 WORKADDR4 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,51
,1
,WORKADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR4,'')='') AND ADDRTYPE='ME'

-- 51.2 WORKADDR4 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,51
,2
,WORKADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR4) > 40 AND ADDRTYPE='ME'

-- 51.3 WORKADDR4 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,51
,3
,WORKADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR4)<>WORKADDR4
AND ADDRTYPE='ME'


- Zahran -
Post #496214
Posted Wednesday, May 07, 2008 6:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
First, you've got one index that's completely a waste (there may be more). Index 11 eliminates the need for index 7 because they both have the same leading edge. So if you pass a query that only uses that first column from 11, it can use that index.

Second, you should check every single one of these columns to see if the selectivity on them is high enough to rate an index. If it's not better than 90%, an index probably won't get used.

Third, that many indexes across 1.2 million rows is probably going to take a while to create. I'd suggest reevaluating your queries to see if they're using the indexes you have and if not, what indexes you actually need.

Yes, covering indexes are better in most instances.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #496260
Posted Wednesday, May 07, 2008 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:39 AM
Points: 287, Visits: 208
Firstly, what sort of cardinality do you have on AddrType.

The sample queries all appear to state a value of 'ME', so is that one of many AddrType values?

My thinking here is the clustering sequence being switched from CustNo, AddrType, to the other way round of AddrType, CustNo. I.E. Keeping all the ME's relatively closer for accessing with fewer reads.

Secondly, as Grant rightly says, are you really using all these indexes or think their presence is helping?

Thirdly is it the CustomerReport table you are later rebuilding the indexes upon, or the RETDCUST_CUSTADDR table? Again the order of things is not totally clear.

Personally, I would see how it runs with significantly less indexing. If as I said above AddrType='ME' is a mere percentage of the 1.2Million rows the clustering order can be a big factor on your perceived application performance end-to-end.
Post #496310
Posted Wednesday, May 07, 2008 9:03 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 19, 2012 9:22 AM
Points: 52, Visits: 381
ADDRTYPE [char(2)] field has only two records. 'ME' and 'MA'.

I am using all these indexes in my query. I just attached a portion of the code.

I validate RETCUST_CUSTADDR table and and insert the records to CustomerReport table for reporting purposes. I don't need any indexes for CustomerReport table.

It think I can avoid some indexes if I use covering index. What would be the advantage if I switch the CUSNO, ADDRTYPE in to ADDRTYPE,CUSNO in the clustered index.


- Zahran -
Post #496421
Posted Wednesday, May 07, 2008 12:41 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
If address type only has two values, in over 1-million rows, then indexing it isn't useful. You'd probably be better off partitioning the table into two tables, one for ME, one for MA. That should simplify what you do significantly.

Second, judging by what the sample queries are doing, unless you are running these over and over and over again, every few minutes/seconds, I don't know that they actually need indexes.

It sure sounds like a daily process thus far, and even without indexes, a million or so rows run once per query per day isn't something that really needs indexes. How often do you run these queries to put the data in your report table?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #496598
Posted Wednesday, May 07, 2008 12:47 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 19, 2012 9:22 AM
Points: 52, Visits: 381
Hi GSquared

This will be run only once in the production.


- Zahran -
Post #496604
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse