Indexing on Time Based Log Table

[dougcoulter]

Hello again everyone - I have a question regarding the most efficient way to work with a table that stores time series application log data. The table is currently non-indexed. It receives at most a new inserted row every second (but generally about a dozen rows per minute). Each row is date and time stamped, but does not have a primary key. The data is stored in an MSDE database, but archived to a separate SQL Server instance when the MSDE is close to reaching its capacity (don't ask). When an archive takes place, all of the data from the MSDE table will be inserted into the separate SQL Server instance, and then deleted from the MSDE database.

In my testing, I have witnessed that deleting all of the rows from the MSDE database (after inserting them into the separate SQL Server instance) - approximately 250,000 rows - takes about 13 minutes to complete.

Finally, to my questions...! First, how can improve this performance? Would an index help? I realize that the delete action is generating quite a few entries in the transaction log, so I was wondering if there is a way to turn off transaction logging during this particular action (since the data has already been archived at that point)?

Any thoughts would be greatly appreciated!

Thanks...

 

[Walid]

Don't use DELETE command; it is logged, since you already saved the records somewhere else you don't care to recover using the log file.
Instead use TRUNCATE command, this one is not logged so it will run much faster.



Walid Magd
Engwam@Hotmail.com

 

[dougcoulter]

Thank you Walid - that is exactly what I was looking for . Sorry for the simple question.

 

[Walid]

you very welcome

Walid Magd
Engwam@Hotmail.com