Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbacarbo99
Starting Member
11 Posts |
 Posted - 2004-11-19 : 12:12:51
|
| I am supporting an application that hourly drops and re-creates its primary table from a text file (feed from another application). The problem is, under heavy load, the application experiences errors in the few seconds it takes to drop, re-create, and load the file.I understand that using the truncate feature is a lot faster. But I'm also told that there are issues w/ using truncate, like loss of logging. But another source tells me that using truncate can totally screw up your backups (I'm guessing since the transaction logs are involved)? This is a mission-critical application, so I cannot risk corrupting my backups.To compare the current data to the data being loaded and only load the changes would be as much of a problem as we are currently experiencing in that it would be a performance hit to the application. So using the truncate feature is the only option I've found to date to speed up this process. I would greatly appreciate any other suggestions.TIA.Adria |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-19 : 12:17:08
|
quote:
But I'm also told that there are issues w/ using truncate, like loss of logging...But another source tells me that using truncate can totally screw up your backups (I'm guessing since the transaction logs are involved)?
Both statements are incorrect. TRUNCATE TABLE is a fully-logged operation, it just does not log each row deletion like DELETE does. It will not affect any kind of backup operation, and is fully restorable/rollback-able. |
 |
|
|
dbacarbo99
Starting Member
11 Posts |
Posted - 2004-11-19 : 12:20:26
|
| THANK YOU VERY MUCH! I'm still very new at this and tend to get confused pretty easily.In your opinion then, would a truncate and load work faster than delete, create, load?Best,Adria |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-19 : 12:32:05
|
| Oh yeah, it's much faster than deleting, and saves you the step of dropping and recreating the table. You might want to look at dropping and recreating indexes too. Indexes will get updated while data is being inserted and will slow down the import process. Drop them, import, then recreate them after, it will go a good bit faster overall. |
|
|
|
dbacarbo99
Starting Member
11 Posts |
Posted - 2004-11-19 : 15:03:01
|
| Thanks again.I am going to give it a try. There are no indexes associated w/ this table, so I shouldn't have to worry about that at least.One thing, I did find the reference that said something about backups:from Ken Henderson's "The Guru's Guide to Transact-SQL""Nonlogged operations [like TRUNCATE TABLE ] tend to be much faster than fully logged operations. And since they gernerate pae allocation loag records, they can be rolled back (but not forward) just like other operations. The price you pay for using them is transaction log recovery. Once you've executed a nonlogged command in a database, you can no longer back up the databses's transaction log -- you must perform full or differential database backup instead."This could be a problem if it's true. This is one table in a very large database. But I cannot afford (performance-wise) to take a full backup (or even a differential one I believe) after the command is executed hourly (actually, every half hour as it is also run on a separate table at the "other side" of the hour).Can anyone refute/defend this statement or offer an alternative?Best,Adria |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-19 : 15:25:53
|
Hmmmm, I didn't realize that (shame on me, I haven't fully memorized his book like I should have by now)  Backups don't use a lot of CPU, and if you have a good I/O setup (SAN, fast RAID) the I/O hit should also be minimal. Another alternative is to move this table to a separate database and create a view in the original DB that points to it. Since this table is not referenced by foreign keys it shouldn't be a problem. Being in a different database also lets you optimize it for bulk loading (simple recovery, truncate log on checkpoint, etc.) and back it up on a completely different schedule, or not at all since you're gonna reload it every hour. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-11-20 : 06:15:45
|
| There was a similar problem to this a while ago where I suggested to use two tables instead of one. Not sure if this is possible for you but what one could do is have two versions of the same table (i.e. mytable_even and mytable_odd) and the insert into the acording table based on the hour of the day. Inserts where datepart(hh) = 1, 3, 5...will go in to mytable_odd and inserts where datepart(hh) = 2, 4, 6...will go in to mytable_even. This way you will have one full hour to do whatever you like with the table that is not used, and there will be no dropping of tables. There will of course be some overhead deciding which table to insert to but this might be acceptable...never tried this myself but I belive it to be worth mentioning.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
drop table vs truncate