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 7, 2008 1:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 23, 2012 10:27 PM
Points: 87, 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.



Post #496649
Posted Wednesday, May 7, 2008 2:13 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #496669
Posted Thursday, May 8, 2008 1:20 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
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!!
Post #496904
Posted Thursday, May 8, 2008 5:36 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:59 PM
Points: 15,735, Visits: 28,141
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #497018
Posted Thursday, May 8, 2008 9:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #497185
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse