SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index creation taking long time


Index creation taking long time

Author
Message
CoetzeeW
CoetzeeW
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 161
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.



GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59971 Visits: 9730
Zahran (5/7/2008)
Hi GSquared

This 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
UDBNT
UDBNT
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 208
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!!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101101 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59971 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search