Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-10-01 : 11:07:48
|
| ...on the Advanced Tab of a Data Pump Task in SQL 7a.k.a Insert Batch Size on the Options Table of a DPT in SQL 2K...How does this effect clustered indexes?I am thinking that if I set this to 0, then the entire batch commits at once. Therefore, the clustered index will only be rebuilt once. Setting this to something other than 0 (say 10) would cause the data to be reordered once for every 10 rows of the source.I have a table that was created (by another) without a clustered index. The reasoning was that having one would slow the load process (DTS Data Pump Task from a staging table) down too much ("Dude, it will take like an extra 30 minutes!!"). I disagree and say that the addition of the clustered index will on 'cost' as much as the time it takes to 'create clustered index' (approx 5 to 10 seconds). Does that sound right?Jay White{0} |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-01 : 11:18:17
|
| Depends on the size of the table and distribution of data.Having a clustered index means that when rows are inserted the data pages have to be reordered and split.Unless you are inserting in the order of the clustered index (e.g. an identity) then it is a good idea to not have a clustered index.In fact if you are worried about speed don't have any indexes at all and add them afterwards if necessary.About the rebuilding of the index at the end if the insert is a single batch.One would hope so and I suspect it does - depends how dts controls it.If it just starts a transaction then does a lot of inserts then the clustered index must be maintained (if unique) as it will fail at the offending insert not at the commit.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-01 : 11:25:32
|
| So let's say you've got a freshly truncated table with a non-unique clustered index.Option A. You drop the clus. idx. Then Data Pump. Then re-create the idx.Option B. You set the 'Insert Commit Size' to 0 and Data Pump.Option C. You set the 'Insert Commit Size' to 1 and Data Pump.Obviously, C will be slower than B. Do you think B will be slower than A or the same? Sounds like you are saying A would be the fastest.Maybe Page47 should take some of his own advice and test for himself ...Jay White{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-01 : 12:00:50
|
| Don't really know - A shouldn't be much slower but may be lots quicker.If you are that worried about performance why noy go for bcp (and A) which should be quicker than any of these.>> Maybe Page47 should take some of his own advice and test for himself ... Yep theory is fun but unfortunately...==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Insert Commit Size