Hi:

Any help with this would be appreciated. Thanks in
advance.

Environment:
SQL 2000 SP3 all up to date on Win2K Server SP4 all up to
date.

Issue:
Trying to import a very large text file (12GB) into a db
table where the data was originally from. The data was
exported based on a selective query (with a WHERE clause),
then the table was truncated, and I am in the process of
reimporting the data. The idea was to delete a bunch of
rows as fast as possible.

The export of the text file took about an hour, the import
is taking six hours and still running.

When I looked at the performance counters, it seemed to be
writing and reading to the drive that holds the tempdb. I
assumed that its writing the data to the tempdb db for the
single shot insert - mind you I did not optimize the
package by tweaking the switch in the package properties
pages.

I am now running out of time, I have to have the data in
by ...hmmm.. half an hour ago. Would it be worth the
while to wait for it or restart the whole thing with
optimized package options for this type of import (which I
have no idea at this point how to do, BOL maybe)?

Thank you for your help.
If you could CC me zebdi (AT) hotmail (DOT) com

A.K.

I presume you are in the .dts NG because you are using DTS?
Which Task?
Why not use TSQL's BULK INSERT command

Does the destination table have
1. Indexes
2. Triggers

Is the destination DB in at most BULK LOGGED mode?

re you doing this in batches

BULK INSERT (BatchSize =)

What about bcp?



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


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

Thank you for the response:



logged.


txt file connection and Transform Data Task in between)




Thanks again Allan.

TSQL's BULK INSERT and the BULK INSERT TASK are the same.

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\dts
sql.chm::/dts_elemtsk2_5m0b.htm

The transform data task with table lock on uses IRowsetFastLoad(BULK COPY
rowsets) which is the same as bcp.

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_8wc2.htm

I personally would use BULK INSERT and I would use batches.

Otherwise the data is going to be inserted in one big lump and that lump
must be stored somewhere first.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


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

A follow-up question is wether exporting to a txt file we
would like to keep, truncating the whole table, then
reimporting the text file is faster than inserting into a
new table the data to keep, deleting the old table, then
renaming the new one. For the second scenario the db will
be switched to bulk-logged mode.

Thank you again.