hi all, i have one table with huge data 10 million , everytime i need to truncate and load this table. we have composite primary key on 16 columns. probably that is why this is taking more time to load than the other tables with comparitvely less number of records.can anyone please suggests what could be the approach to make the ETL process fast. given the fact that i have implemented multithreading as well. ThanksTarni

What version you are using? http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx ---Minimal Logging changes i http://sqlblog.com/blogs/davide_mauri/archive/2011/06/11/how-the-number-of-indexes-built-on-a-table-can-impact-performances.aspx Have you tried SSIS package with OLEDB provider which has fast load option?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

uri, i am using 2008 enterprise edition of SQL Server. and yes i am using SSIS with "OpenRowset Using FastLoad"as access. also there are 8 parallel threads in my package, to load the same table. ThanksTarni

Hi, Have you tried truncating the data, dropping the Primary Key, importing the data, and then recreating the PK? Aside from the ETL process you may want to change the PK to a surrogate key based on a big int, auto incrementing. Then use the 16 columns to form the clustered index. Having a 16 column primary key can cause problem if you start building other indexes on the table. Seth http://lqqsql.wordpress.com

Tarni Is that possible to drop the index/key before loading and re-create after?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi Uri, thanks for reply. yes in my package i am first droping the index, then truncating the table and after load i am creating the index. Thanks Tarni

Seth, yes exactly i am doing the same truncating the data, dropping the Primary Key, importing the data, and then recreating the PK. but creating the pk with 16 column is requirement, that probably i can not change. ThanksTarni

Take another look at Seth's suggestion, add an identity surrogate key, make sure your 16-field PK is nonclustered, then make the identity your clustered index. Actually, do the clustered index first! Making sure you understand the difference between clustered key and primary key is important here. How long is it currently taking? Another thing to try - if possible, get your data sent to you for import already sorted. Josh