Tech Note: Compacting Address Tables

PRODUCT: 4D | VERSION: 12 | PLATFORM: Mac & Win

Published On: October 5, 2010

In 4D v11 SQL, address tables are no longer compacted as part of a database Compact operation, using the Maintenance and Security Center. This change was made in order to preserve record numbers after a compact. However large, empty address tables can have an effect on memory and performance, thus it can still be useful to compact them.

This Technical Note explores this issue and includes a component that can be used to compact address tables.

Download Complete Tech Note and Example: Windows | Mac

Commented by Timothy Penner on April 29, 2013 at 11:06 AM

The code in the tech note works for v11 and v12 up through v12.4 as well as for v13 through v13.1

However the DATA_LENGTH calculation for Alpha fields changed in v12.5+ and v13.1HF1+; in those versions the calculation appears to be alpha_field_length*2 so the following code:

If ($length_l>4)

$length_l:=($length_l-4)/2

should be changed to:

If ($length_l>1)

$length_l:=($length_l)/2

This is lines 46 and 47 of UTIL_SQL_TypeIdToName

Commented by Justin Leavens on April 27, 2013 at 9:15 AM

For some reason this is creating string fields in the destination temp table that are 2 characters smaller than the source, which is resulting in chopped-off string data.Also, there's no error checking to insure that the copying of data to the temp table was successful before the truncation of the original table takes place. So if the copy fails, it's still going to delete all your source data and you come up empty.

Commented by Josh Fletcher on February 7, 2011 at 5:38 PM

IMPORTANT NOTE: Address table compaction will affect 4D Data Log File integration. The Data Log File relies on record numbers in order to integrate data changes. In other words once you compact an address table you will no longer be able to integrate previous Data Log Files into that database. The Technical Note has been updated to reflect this information, please make sure to download the latest version.