Index creation after of before populate?

Question

Post reply

Index creation after of before populate?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
September 13, 2010 at 8:54 am

#140651

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 1

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 SSC Guru Points: 95460 More actions · Answer 2

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

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.

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 4

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

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

Learn to play, play to learn !

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[/url]

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

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dung Dinh SSCrazy Points: 2513 More actions · Answer 6

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. SSCertifiable Points: 5173 More actions · Answer 7

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!

🙂

.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

Any partitioning involved ... search BOL for "sliding windows"