Delete Table Before Import 1

[sabloomer]

Thank you in advance to anyone that can help,

I have a DTS package that deletes the contents of a table that, holds line level invoice detail, before it imports a new, and large, text file. Processing time is an issue. I have a limited amount of time between when my text file is produced and the users need to be using the database. Here is my question, is there a way to speed up the delete part of the process? I am an Access user trying to make the jump to SQL Server (7.0). I am used to delete queries running in very little time, but it seems the delete part takes as long as the import part. Am I stuck with this, or is there a way to avoid this?

Thank You,

sabloomer

 

[john76]

If you are deleting the entire contents of the table, you could drop and recreate the table instead.
Tends to be a faster operation than DELETE FROM <YourTable>

[COLOR=green]-- Instead of this...[/color]
DELETE FROM MyTable

[COLOR=green]-- Try this: [/color]
DROP MyTable

CREATE TABLE MyTable
(
  [COLOR=red]<Your table DDL commands here>[/color]
)

HTH,
John

 

[JayKusch]

No need to do a delete on the table nor recreate it each time you use it. If you are wanting all the records in the table gone before your next import ... issue a TRUNCATE command as in ...

TUNCATE TABLE MyTable.

The issue at hand w/ using the DELETE command is that it is logging the delete operation against all of the records in the table into the transaction log. Ny using the TRUNCATE, all the recs a re dropped and NO logging is recorded.

In your DTS package, create an "Execute SQL Task", add the truncate code above and then set it as you first step w/ an "ON SUCCESS" workflow going from the task to you first connection that start your DTS process.

Thanks

J. Kusch

 

[sabloomer]

JayKusch,

I was 99.9% sure the logging was adding the extra time, but I wasn't sure how to stop that, or if it was even posible. Thank you for the help.

sabloomer