Important DTS issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Important DTS issue

Hi all,
I am facing a problem in my DTS. This is a production issue and pretty serious.
I have a DTS which basically performs the following tasks.
1. Load data from *.csv files to staging table.
These csv files come from host system and are very large. We load these in two table whose structure is as fallows
tbl_WorkService tbl_WorkServiceProduct
intFNN intFNN
intDetailID intDetailId
dtmLastDate strPCMCode
strSerType intPSN
Initially we update intFNN in tbl_WorkServiceProduct from tbl_WorkService and the join is
on intDetailId.
2. We then move data from tbl_WorkServeiceProduct to main table. No of rows on tbl_WorkService will be 11 millions and tbl_WorkServiceProduct around 88 millios and there are no defined primary keys although logically intFNN in tbl_WorkService is unique.Ther’s no primary key in tbl_WorkServiceProduct.Currently this DTS is taking around 60 hrs to run where Update intFNN only take around 35 hrs. Also the size of LDF file increases abnormally and the disk becomes full. I need to find solution for the following problems.
1. How to reduce the time for execution?
2. How to decrease the size of LDF(around 35 GB will do.) Herrs my solution
instead of updating in staging and then moving to main why not to directly insert into main using INSERT INTO tbl_main….SELECT …query. Using this I am getting time reduced but the size of LDF is still not controllable.. please suggest something…
Are you loading the data into tables which has indexes?
If yes, please delete the index before insert or drop and recreate the tables before load… otherwise your dts will not use FAST LOAD even if you chek the check box of FAST LOAD in dts properties. 1. When you are loading the data from excel using DTS make sure you use FAST LOAD option… 2. Create the indexes on destination tables and make sure you have index on column for which you are using in your where clause.
3. Try updating in batches to keep ldf file in control and increase ldf file size so that it will grow while update/insert process running.
Mohammed U.
]]>