Performance gain

How much of a performance gain I can get on 1.2 million rows insert via DTS
package If I,
- Drop 1 clustered and 2 non-clustered index on the table I am loading
- 2-3 foreign keys referencing other tables
- Replace the delete table with truncate table
- Do a bulk insert
- Re-create the indexes
- Re-create the foreign keys
Thanks for any input.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
>
Well best way will be to try it. ;-)
BUT, in my experience what you're doing will help.

> - Drop 1 clustered and 2 non-clustered index on the table I am loading
This helps, but mostly if the imported data is already sorted on the
clustered index. Otherwise, rebuilding the clustered index may eat up any
savings.

> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
HUGE help

> - Do a bulk insert
Definitely faster w/o indices.

> - Re-create the indexes
> - Re-create the foreign keys
>
In general you should see a big improvement, especially if the clustered
index doesn't have to do much data movement.

> Thanks for any input.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You have given us too little data to assist you properly. The biggest thing
missing is how many rows are ALREADY in the table and how many rows are in
the referencing tables?
In general if you are inserting an amount that is a reasonably large
fraction of the total rows already in the table, then it is better to
drop/rebuild. But in this case if your table already has a billion rows,
adding 1.2 million would be much faster keeping things as they are.
TheSQLGuru
President
Indicium Resources, Inc.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
> - Drop 1 clustered and 2 non-clustered index on the table I am loading
> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
> - Do a bulk insert
> - Re-create the indexes
> - Re-create the foreign keys
> Thanks for any input.
|||On Thu, 10 May 2007 09:10:01 -0700, DXC
<DXC@.discussions.microsoft.com> wrote:

>How much of a performance gain I can get on 1.2 million rows insert via DTS
>package If I,
>- Drop 1 clustered and 2 non-clustered index on the table I am loading
>- 2-3 foreign keys referencing other tables
>- Replace the delete table with truncate table
>- Do a bulk insert
>- Re-create the indexes
>- Re-create the foreign keys
>Thanks for any input.
Besides two good answers already, how long does it take now?
J.