| Author |
Topic |
|
wernerdejong
Starting Member
7 Posts |
 Posted - 2006-07-04 : 10:09:38
|
Hi all,Just wondering if there is something like truncate table with preserve space.Situation.I daily load a number of flat files into a staging area. Before I load these files, the target tables must be emptied. Truncate table is the fastest way to do this since I do not want the transaction log to be full. (Second, the database is in simple mode) However, when I use the delete * from table command, the space reserved in the filesystem / database files and thus remains allocated to the table. (Which is exactly what I want!) Using truncate does not allocate this space but simply removes a header record (I assume) Thus when I load data to the table the space will be claimed over and over again while I already know the table is around 3gb, 400mb, 5mb etc. in size.Thanks in advance for any suggestions.  |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-04 : 10:19:44
|
| Not sure what you are asking here.I think both delete and truncate will deallocate the extents from the table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
wernerdejong
Starting Member
7 Posts |
Posted - 2006-07-04 : 10:26:46
|
Hi Nr,Thanx for your reaction. I realise now that I forgot to post I'm using sql 2005.  When looking at the report generated by SQL server about space used in a database. (hit f7 when selecting a database in management studio, then on the green button report) you can compare the two statements. Truncate will trash the reserved space and delete from will keep the reserved space / extends in the datafile. I know from ppl that Oracle can truncate with preserve space and the report gave me some hope. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-04 : 12:24:42
|
| Sorry - you're right.Don't think there is a way to stop truncate dallocating the extents.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-05 : 05:14:39
|
| I'd be interested to know if inserts are faster after a delete (keep extends) than a truncate (throw away extends)(And is the data you are re-inserting similar, index-wise, to what was there before?)ThanksKristen |
|
|
|
wernerdejong
Starting Member
7 Posts |
Posted - 2006-07-05 : 09:07:28
|
| Hi Kristen,Per request. ;) I've tested both in SSIS with a dataset of 84.5mb = 143.828 rowsI used the oledb object with fastload on, ignore nullsWhen using the truncate table option the entire package tookCompareable run 1: 27.110 secondsCompareable run 2: 28.188 secondsCompareable run 3: 27.687 secondsWhen using the delete from option the entire package tookCompareable run 1: 29.610 secondsCompareable run 2: 29.437 secondsCompareable run 3: 29.688 secondsIt looks like the truncate option is faster overall. However. The delete optionhas a slightly higher insert speed. :) For large datasets (gb's) this could be a big difference since the delete statement overhead will be "earned" back with the actual moving of data. But to test this.... no more time / urge for now. ;) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-10 : 07:37:44
|
| "For large datasets (gb's) this could be a big difference ..."Although the logging "cost" will be pretty high too ...Thanks for that, most interesting.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-10 : 08:45:47
|
| I wouldn't have thought that the delete/insert would be faster even for large datasets but would win if the delete/truncate could be run at a time which wasn't important e.g. if you have a limitted time to populate the table but the delete can be done prior to the window.Would probably have to delete in batches which would make it even slower.If you were replacing a complete database (or filegroup) then maybe restore from a backup with the tables empty but allocated.This way lies madness I feel.I'm doing a truncate / ssis bulk insert on tables around 10 - 20 G so might get round to testing it sometime.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
wernerdejong
Starting Member
7 Posts |
Posted - 2006-07-10 : 09:12:33
|
| If you set the database recovery model to simple, the delete statement wouldn't log as much as when the database is in full or bulk logged mode. :) Anyway, testing would make certain. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-11 : 07:19:39
|
| "wouldn't log as much ..."... provided that you delete in batches :-(Kristen |
|
|
|
|
Truncate with preserve.