Delphi-Deleting a record not freeing disk space
Jeff Jacobson
group of records, I am deleting them one at a time using 'Table9.Delete;'.
The problem is that the size of the table never decreases. Currently at 25 mg
and the table is empty.
Any ideas on how to reclaim this space?
Thanks in advance,
Jeff J
---------------------------------------------------------------------
No cute drawings or funny quotes - Just me.
Steve
je...@decisionsys.com (Jeff Jacobson) wrote:
>I am appending and deleting about 7500 records at a time. When
deleting a
>group of records, I am deleting them one at a time using
'Table9.Delete;'.
>The problem is that the size of the table never decreases. Currently
at 25 mg
>and the table is empty.
>
>Any ideas on how to reclaim this space?
When you delete a record in most databases, the record is not
actually deleted. It is simply _marked_ as deleted. This is similar
to the DOS delete command (for files) and provides a degree of fault
tolerance. Most DBMS's have a command to reclaim the space taken by
deleted records. They rewrite the entire file, leaving out the
deleted records. In FoxPro this is called Pack.
I do not know if you can pack a table from Delphi, but you could
alwasy use the DBMS that created the table to do this. Even if you
can do this in a program, I wouldn't make a practise of packing the
file on every delete. First, this would slow down the program
considerably and second, the disk space taken is a small price to pay
for the safety of being able to undelete a record.
Steve
Leng Vang
je...@decisionsys.com (Jeff Jacobson) wrote:
>I am appending and deleting about 7500 records at a time. When deleting a
>group of records, I am deleting them one at a time using 'Table9.Delete;'.
>The problem is that the size of the table never decreases. Currently at 25
mg
>and the table is empty.
>
>Any ideas on how to reclaim this space?
>
>
>Jeff J
>---------------------------------------------------------------------
>No cute drawings or funny quotes - Just me.
>
>je...@decisionsys.com
Paradox doesn't actually remove the record from the table when a record is
deleted. It just marks as deleted. Unfortunately, I can't find the Pack
command in Delphi's TTable or any other database related component. This
command is available in the Paradox Engine. You can however quickly reclaim
the space by going to the Database Desktop and restructure the table with Pack
table option selected.
Good luck.
Steve Koterski
: I am appending and deleting about 7500 records at a time. When deleting a
: group of records, I am deleting them one at a time using 'Table9.Delete;'.
: The problem is that the size of the table never decreases. Currently at 25 mg
: and the table is empty.
: Any ideas on how to reclaim this space?
What is the table type (dBASE, Paradox, InterBase, etc)? This is a key
question.
In dBASE tables, when you "delete" a record, it is not physically removed
from the table. A record is deleted by inserting an asterisk (ASCII deci-
mal 42) in the first byte of the record. Such records marked for deletion
are then suppressed from display and use, by remain in the table file. In
dBASE (the software), such records may still be displayed by toggling the
setting represented by SET DELETED or undeleted by the RECALL command.
Only by issuing the PACK command are the marked records finally physically
removed from the file and the space freed. In Delphi, there is no prov-
ision for toggling the display of these records or for recalling them
(short of using file operations and manually toggling that first byte in
each record to a space, or a non-deleted state). And physically removing
records marked for deletion is a matter of making a call to the BDE func-
tion DbiPackTable.
So, if you are using a dBASE table, the above is the most likely reason
for what you are seeing. If you are using another table type, the problem
would lie elsewhere.
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski _/ The opinions expressed here are _/
_/ kote...@borland.com _/ exclusively my own _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Gabriel Beccar-Varela
>I am appending and deleting about 7500 records at a time. When deleting a
>group of records, I am deleting them one at a time using 'Table9.Delete;'.
>The problem is that the size of the table never decreases. Currently at 25 mg
>and the table is empty.
>
>Any ideas on how to reclaim this space?
>
In situations where all of the records are deleted, or you know you will delete all
records in a table, I found it to be much more efficient (at least in informix) to drop
the table and recreate it. Droping the table actually deletes the file(s). It takes a
second. This assumes that you saved the structure of the table someplace and that you
can recreate on the fly.
--
Gabriel Beccar-Varela
E-Mail gab...@emf.net
Iman L. Crawford
>Any ideas on how to reclaim this space?
>
If you are using Paradox tables there are two dll's
Tutility.dll, and TWW.dll, or something close.
Anyway these two dll's can be used to verify the
integrity of paradox tables, rebuild corrupt
pdx tables, and pack them.
If anyone knows how to make these work. Let me
know. I've gotten do far, but the docs are
dreadful and I get error messages that are not
explained.
Iman L. Crawford
ilcr...@arn.net
Al Heithaus
a
>group of records, I am deleting them one at a time using
'Table9.Delete;'.
>The problem is that the size of the table never decreases. Currently at
25 mg
>and the table is empty.
>
>
>
>Jeff J
>---------------------------------------------------------------------
>No cute drawings or funny quotes - Just me.
>
>je...@decisionsys.com
Lots of databases don't pack information when records are deleted, but
rather mark the records as reusable for future insertions. Helps
performance.
Said databases usually have a "pack" or "rebuild" command that removes
unused records.
You can also cook one up that reads from one file, writes to another,
deletes original and renames the new one, if push comes to shove.
--Al--
Erik Turner
: je...@decisionsys.com (Jeff Jacobson) wrote:
: >I am appending and deleting about 7500 records at a time. When deleting a
: >The problem is that the size of the table never decreases. Currently at 25 mg
: >and the table is empty.
: >
: >Any ideas on how to reclaim this space?
: In situations where all of the records are deleted, or you know you will delete all
: records in a table, I found it to be much more efficient (at least in informix) to drop
: the table and recreate it. Droping the table actually deletes the file(s). It takes a
: second. This assumes that you saved the structure of the table someplace and that you
: can recreate on the fly.
I had this same problem. The "off-line" way to compact the table is to go
into Data Desktop with no other BDE apps running (close ReportSmith and
your application) and restructure the table. Once the restructure dialog
box is displayed choose the Pack Table check box in the lower left corner
and then choose "Save". This will shrink the disk files containing the
database.
The "on-line" way (according to one of the Steve's) is to call the
DbiRestructure routine. All the documentation that I could find on this
routine was in the VCL source and consisted of the prototype. I started
to try to figure out how to call the routine and quickly got overwhelmed
by the complexity.
If anyone has gotten this to work, let me know.
Later,
Erik Turner
etu...@ccd.harris.com
Kevin Morwood
>In article <jeffj.22...@decisionsys.com>,
> je...@decisionsys.com (Jeff Jacobson) wrote:
>>I am appending and deleting about 7500 records at a time. When
>deleting a
>>group of records, I am deleting them one at a time using
>'Table9.Delete;'.
>>The problem is that the size of the table never decreases. Currently
>at 25 mg
>>and the table is empty.
>>
>>Any ideas on how to reclaim this space?
>
>actually deleted. It is simply _marked_ as deleted. This is similar
>to the DOS delete command (for files) and provides a degree of fault
>tolerance. Most DBMS's have a command to reclaim the space taken by
>deleted records. They rewrite the entire file, leaving out the
>deleted records. In FoxPro this is called Pack.
>
Be careful here. If you are using Delphi against Paradox tables and you
delete a record...it is DELETED. The space that it took up in the file is not
freed, that much is true, but unlike dBase tables, you cannot recover the
record once you have deleted it. And as far as I know there are NO SQL
databases that support this 'mark-for-deletion' scheme. dBase tables use the
'mark-for-deletion' concept that you describe. There is no fault tolerance
for these types of databases. Anyone with BTrieve experience can chime in for
that info...please.
As for the space that is left behind...upon close examination of the TDatabase
and TTable components I find that Borland did not 'surface' the table compact
function. In order to pick out the 'deadspace' in the table you will have to
open restructure dialog on that table and click on the 'Pack...' checkbox.
Once you do this then click on Save. Nothing changes with the structure but
the table will be packed. Warning...this can take a while...especially on a
25Mb table. This pack method will take care of both Paradox and dBase table
types.
=======================================
Kevin Morwood
Are we having fun yet?
Steve Koterski
: In article <3um44c$b...@itchy.itsnet.com>, rwo...@itsnet.com (Steve) wrote:
: >In article <jeffj.22...@decisionsys.com>,
[...]
: As for the space that is left behind...upon close examination of the TDatabase
: and TTable components I find that Borland did not 'surface' the table compact
: function. In order to pick out the 'deadspace' in the table you will have to
: open restructure dialog on that table and click on the 'Pack...' checkbox.
: Once you do this then click on Save. Nothing changes with the structure but
: the table will be packed. Warning...this can take a while...especially on a
: 25Mb table. This pack method will take care of both Paradox and dBase table
: types.
This was not included in the stock TTable component because it is table-
specific. The stock TTable component includes only that functionality
that applies to *all* the table types the BDE supports, excluding those
features or idiosyncracies that apply only to one or another table type.
This exclusion includes creating Paradox tables with auto-increment fields
and packing dBASE tables.
If this process of inclusion and exclusion were not used, we would have
wound up with numerous TTable variants, one for each table type. Having
one generic TTable has the advantage of simplification using Delphi with
databases, but has the drawback that it leaves it to the user to create
any TTable custom components that incorporate table-specific features or
operations needed.
Per Ola Svensson
Paradox tables from within an application.
I don't know who wrote it and unfortunately I don't even
remember where I got it from, but it seems to be freeware.
Drop an email and I'll send it to you..
P O Svensson, po.sv...@mailbox.swipnet.se
Kevin Morwood
koterski@genghis (Steve Koterski) wrote:
>
>This was not included in the stock TTable component because it is table-
>specific. The stock TTable component includes only that functionality
>that applies to *all* the table types the BDE supports, excluding those
>features or idiosyncracies that apply only to one or another table type.
>This exclusion includes creating Paradox tables with auto-increment fields
>and packing dBASE tables.
>
>If this process of inclusion and exclusion were not used, we would have
>wound up with numerous TTable variants, one for each table type. Having
>one generic TTable has the advantage of simplification using Delphi with
>databases, but has the drawback that it leaves it to the user to create
>any TTable custom components that incorporate table-specific features or
>operations needed.
>
Well, if you go grab a copy of the Paradox manuals you will find that the
compact() function works for both dBase AND Paradox tables. I have had
numerous discussions about this with developers. It seems people have a hard
time reading manuals. B->
And since the actual table work is being done by IDAPI I don't think it should
have been too much trouble for the class designers 'wrap it' into the class.
Anyway, it would have been great if they (the Delphi team) could have
supported the feature in the same manner that Paradox did. Don't you think
so?
=================================================
Kevin Morwood