Re: 42 Nightly scheduled DTS jobs - performance issues

#1

When you say the data in the files keeps changing, how much is changed?

You could save a lot of time by doing insert and updates for new and changed
records, instead of a truncate and insert for all records.

We run a similar nightly processes and we cut the copy time from 5 hours
down to 2. This is without having to drop and re-create indexes as well.

--
Thanks
Phill Carter
#562421

"determine" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,
I have 42 text files. Records in each table are from 25,000 - 3.5
million. Data in these text files keep changing everyday. I have 42 tables

(set of temp and base) on sql server database. I run DTS jobs to load the
data from these text files into the temp tables. My temp tables doesn't
have the indexes. After the load is complete I truncate the data in the
base table and load the new data from temp table.

Quote:

There has been lot of performance issues. Some jobs complete fast and
couple of them take about 10 hours.

Is there an effective way of doing this. I want to redo the whole system.
I'm on SQL Server 2000/service pack 4 on windows NT operating system. Could

it be hardware problem. Do I need to upgrade the server.

Quote:

Please help.

Thanks a lot.

#2

When you say the data in the files keeps changing, how much is changed?

You could save a lot of time by doing insert and updates for new and changed
records, instead of a truncate and insert for all records.

We run a similar nightly processes and we cut the copy time from 5 hours
down to 2. This is without having to drop and re-create indexes as well.

--
Thanks
Phill Carter
#562421

"determine" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,
I have 42 text files. Records in each table are from 25,000 - 3.5
million. Data in these text files keep changing everyday. I have 42 tables

(set of temp and base) on sql server database. I run DTS jobs to load the
data from these text files into the temp tables. My temp tables doesn't
have the indexes. After the load is complete I truncate the data in the
base table and load the new data from temp table.

Quote:

There has been lot of performance issues. Some jobs complete fast and
couple of them take about 10 hours.

Is there an effective way of doing this. I want to redo the whole system.
I'm on SQL Server 2000/service pack 4 on windows NT operating system. Could

it be hardware problem. Do I need to upgrade the server.

Quote:

Please help.

Thanks a lot.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: 42 Nightly scheduled DTS jobs - performance issues

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: 42 Nightly scheduled DTS jobs - performance issues - 03-31-2004 , 07:11 PM

Re: 42 Nightly scheduled DTS jobs - performance issues - 03-31-2004 , 07:11 PM