delete_census — Rapidly delete “old style” CENSUS rows
int
delete_census
(
|
sname, | |
| from, | ||
through); |
char(3)
sname
;
date
from
;
date
through
;A function to delete an individual's rows from CENSUS for a particular time period. The deleted rows are inclusive of the supplied dates.
This function is useful because deleting multiple non-interpolating CENSUS rows in a single SQL DELETE statement is an operation that takes an amount of time proportional to the square of the number of rows deleted. Use of this function is a substitute for deleting CENSUS rows one at a time.
Eventually most of the non-interpolating CENSUS rows will be removed from Babase, along with their codes. These are the rows associated with analysis of historical data.
To delete more than a years worth of census data it's best to delete a year at a time, and leave a single row undeleted within each year. When done go back and delete the single rows. This can all be done by submitting multiple statements at once so as not to have to continually interact with the system. Not only will this technique minimize the time spent it will also minimize the number of MEMBERS rows created and destroyed, and therefore the number of MEMBERS.Membids used.
-- Deleting CENSUS rows for FOO from 1987-03-18
-- through 1992-01-23, inclusive.
BEGIN TRANSACTION;
SELECT delete_census('FOO', '1987-03-18', '1988-03-17');
SELECT delete_census('FOO', '1988-03-19', '1989-03-17');
SELECT delete_census('FOO', '1989-03-19', '1990-03-17');
SELECT delete_census('FOO', '1990-03-19', '1991-03-17');
SELECT delete_census('FOO', '1991-03-19', '1992-01-23');
DELETE FROM census
WHERE census.sname = 'FOO' AND census.date = '1988-03-18';
DELETE FROM census
WHERE census.sname = 'FOO' AND census.date = '1989-03-18';
DELETE FROM census
WHERE census.sname = 'FOO' AND census.date = '1990-03-18';
DELETE FROM census
WHERE census.sname = 'FOO' AND census.date = '1991-03-18';
COMMIT TRANSACTION;