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


Index creation after of before populate?


Index creation after of before populate?

Author
Message
ricardo_chicas
ricardo_chicas
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 694
Hello all,
What do you think is better, create indexes for temp tables after or before we populate them,
I tend to believe that create them before the inserts could be better, since we won't be blocking temp, but i have heard some other opinions
What do you think?

Thanks
Tim Walker.
Tim Walker.
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 984
My preference, if this is possible to do in your scenario, is:

1. Create table and unique clustered index
2. Insert data IN ORDER OF CLUSTERED INDEX
3. Create non clustered indexes

This way, you avoid fragmentation and unecessary sorting,

If you have to sort the data to achieve step 2, it doesn't matter so much which way around you do it.

.
WayneS
WayneS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10142 Visits: 10575
If you are inserting a large amount of data, you will probably find it faster to add the index afterwards.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64953 Visits: 19118
Ultimately you need to test and get an idea of when it's quicker which way. It often does depend on how much data, and your disk I/O.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ricardo_chicas
ricardo_chicas
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 694
Actually my concern is that we have several dbs and ost of users, so I really need to be careful with the use of temp db, I like the approach of
Create table
create clustered index
Inserts
nonclustered
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12590 Visits: 8930
ricardo_chicas (9/13/2010)
Actually my concern is that we have several dbs and ost of users, so I really need to be careful with the use of temp db, I like the approach of
Create table
create clustered index
Inserts
nonclustered


In general that would be the prefered order if you load a full set. It will get you most optimal indexes with regards to fragmentation.
Keep in mind loading in order of the clustering index will have advantages too with regards to space usage and IO.

However, tempdb is always a special case, so profound testing may be in order !

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Dung Dinh
Dung Dinh
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 1231
Which approach if we populate large data into a table many times?
Eg: Today I populate 2GB data follow below steps
Create Clustered Index on table
Insert data
Create Non-Clusterd Index(s)
Tomorrow, I will also populate data into the table.
Thanks,
Tim Walker.
Tim Walker.
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 984
The same principle 'probably' applies

i.e. Create table and clustered index, populate IN CLUSTERED INDEX ORDER, create additional indexes

But...

(1) With this much data you may well be sorting it first unless you are getting it straight out of another indexed tables (e.g. aggregation). If you have a sort step you'll need to test whether sorting is more intensive than creating the clustered index afterwards. It may well be.

(2) With this much data there may be a better way of doing this - e.g. only populating changes - you'd have to write logic for this

(3) With more complex requirements, as previous posters have said, you should test different techniques and see what works best - There are lots of potential differences vetween environments.

There is no 'best' way of doing things which always applies!

:-)

.
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12590 Visits: 8930
Any partitioning involved ... search BOL for "sliding windows"

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
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