May 7, 2008 at 1:37 am
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:
[font="Courier New"]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[/font]
- Zahran -
May 7, 2008 at 3:49 am
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.
May 7, 2008 at 4:37 am
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 -
May 7, 2008 at 5:21 am
Not sure I understand what you are trying to do/achieve.
Can you give a sample of the sort of processing you are doing?
May 7, 2008 at 5:47 am
Attached a sample of my code
[font="Courier New"]-- 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' [/font]
- Zahran -
May 7, 2008 at 6:46 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 7:34 am
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.
May 7, 2008 at 9:03 am
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 -
May 7, 2008 at 12:41 pm
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
May 7, 2008 at 12:47 pm
Hi GSquared
This will be run only once in the production.
- Zahran -
May 7, 2008 at 1:54 pm
Hio
I agree with all the previous statements the other guys made espesially having indexes on individual columns and then a covering index on them as well. There is no real reason for that. If you want to use your covering index suffciently make sure you create the covering index in the order that you will query (left to right).
I also noticed that you do alot of converts ect in your where clause, not a good idea , this increases read IO and CPU IO and increases the cost of the query overall.
Even if this will be executed once a day you don't want batch runs that is worth 1.2 million rows to take to long ,espesially if other batch jobs are dependant on this one's success.
The partitioning comment is very valid but if you do not want to partition make sure you use a clustered index on the addrtype column , since non-clustered indexes are better for row specfic searches and high selectivity values and clusterd indexes are good for that as well but clustered indexes are good for range values.You can also if capcacity allows you to split the table into two base tables each having data with the different addrtype in it (this is where partitioing is so good) so you will work with each addrtype indvidually and this will decrease the row sizes you work with per batch
Have you tried creating the indexes specifying MAXDOP ? ..maybe using multiple threads might help you
Getting back to your question why do the index creation takes so long ...alll these index creation will take long concidering the duplication of indexes you are creating. Also look at your data types and column lenghts this is alo a good indication why indexes might take long. Have you analysed the waitstats of the engine while you create these indexes, maybe you have blocking of some sort or tempdb is under pressure ..you will need to benchmark and get baselines , try different approaches and select the best approach.
May 7, 2008 at 2:13 pm
Zahran (5/7/2008)
Hi GSquaredThis will be run only once in the production.
In that case, indexes are a waste of time. Don't bother with them.
Indexes are mainly useful for data that will be selected (queried) over and over again. If it won't, they're not useful.
- 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
May 8, 2008 at 1:20 am
Bowing to those individuals with better SQL knowledge than me, but surely the supplied statements from zahran is a bit like running down the road to the sweet shop to pick up all the fruit pastiles, then bringing them home, then running down the same shop to pick up the polos, then bringing them home, etc etc. i.e. Populating the report table with umpteen different select/insert statements, as opposed to selecting the whole and scanning through/processing the details once?
Surely there is a way to code a SQL statement that selects all the rows from his source table and then with perhaps Case statements inserting into the report table where each scenario matches his criteria?
SELECT Col1, col2, col3 INTO reporttable FROM sourcetable
WHERE addrtype = 'ME'
CASE LongAddr1
WHEN LongAddr1 > 60 chars THEN
......????
WHEN LongAddr2 > 60 chars THEN
......????
..
..
..
WHEN LongAddr1 Has Arabic chars THEN
......????
WHEN LongAddr2 Has Arabic chars THEN
......????
If this sort of thing is not possible happy to be put right, as SQL is far from my strong point!!
May 8, 2008 at 5:36 am
Don't bow. You're right. 100%.
I think most of us have been focused on the indexes. I'm still a bit bothered by the cluster being on such non-selective columns. It's not going to group the data well. Still, I'd have to see execution plans or a structure with statistics to really pass judgement on which way all these indexes should go. However, the short answer, there are definately some you don't need and there are likely more that won't or can't get used by the optimizer. You have to look at the selectivity of all these indexes to determine if they are actually useful. Then you have to examine the execution plan to see if they are actually used. As much as anything else, that will tell you what to do.
"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
May 8, 2008 at 9:27 am
UDBNT (5/8/2008)
Bowing to those individuals with better SQL knowledge than me, but surely the supplied statements from zahran is a bit like running down the road to the sweet shop to pick up all the fruit pastiles, then bringing them home, then running down the same shop to pick up the polos, then bringing them home, etc etc. i.e. Populating the report table with umpteen different select/insert statements, as opposed to selecting the whole and scanning through/processing the details once?Surely there is a way to code a SQL statement that selects all the rows from his source table and then with perhaps Case statements inserting into the report table where each scenario matches his criteria?
SELECT Col1, col2, col3 INTO reporttable FROM sourcetable
WHERE addrtype = 'ME'
CASE LongAddr1
WHEN LongAddr1 > 60 chars THEN
......????
WHEN LongAddr2 > 60 chars THEN
......????
..
..
..
WHEN LongAddr1 Has Arabic chars THEN
......????
WHEN LongAddr2 Has Arabic chars THEN
......????
If this sort of thing is not possible happy to be put right, as SQL is far from my strong point!!
Yes, it can be made more efficient by doing that. I'm not too worried about that, since this is a one-time data clean-up. There are theoretical and philosophical reasons to make even something like that be performant, but in this case, I didn't want to muddy the water for the original poster by going off onto that tangent.
- 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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply