[Sorry about the embedded image that became a filtered-out attachment on
the list. I had passed my note to a couple of people internally with no
problems, so I naturally... well, I understand the error. I hope this
version is better.]

We have a process to delete ("archive") records from our primary data base.
Lately, we have noticed that the process has slowed down considerably. At
first, we believed that our method was faulty -- we recently changed from
going after the entries by an alternate key to using the primary key. Since
we have a system that feeds our data warehouse, we were able to conduct
some tests there. The amazing thing is that the same routines on a
different instance of the data base (different account, same structure, but
far less data) proved to be about 3.5 times faster. There is not much
variation among the methods within the same instance.

In both instances, the dataset has jumbo chunks. There are three keys.
Omnidex is not a factor in our tests as TPI is turned off and the index
files not present.

We have not repacked the dataset for some time. We believe that a chained
repack is out of the question on the primary instance. It would take longer
than a long weekend, and we would be concerned about disk requirements for
temporary workfile, sorting. So that is out of the question.

All of the tests were run on a machine that has nothing else running on it.

Here is a chart with what we consider the pertinent information. I hope you
receive it formatted to be readable. Any advice? Thanks in advance.

                                                        Dataset
Deletions
                                                              Information
                                                                  per

                                                                Minute
Major instance

Dataset Capacity                                                 67,708,048

Entries before deletions                                         50,327,660

Quantity of Deletions                                               708,485

Jumbo chunks                                                              9

Home-grown program, nobuff I/O on input file, alternate key
                                                                  1,376
*
Home-grown program, standard I/O on input file, primary key
                                                                  1,406
*
SUPRTOOL, chain on alternate key
                                                                  1,457
*
SUPRTOOL, chain on primary key
                                                                  1,400
*
                                                              * Average of
two test cycles


Secondary instance

Dataset Capacity                                                 14,435,048

Entries before deletions                                         11,012,521

Quantity of Deletions                                             1,063,136

Jumbo chunks                                                              2

Home-grown program, nobuff I/O on input file, alternate key
                                                                  5,062
Home-grown program, standard I/O on input file, primary key
                                                                  4,945
SUPRTOOL, chain on alternate key
                                                                  5,136
SUPRTOOL, chain on primary key
                                                                  4,622

--
Leonard S. Berkowitz
Perot Health Care Systems
(Harvard Pilgrim Health Care account)
voice: 617-509-1212
fax: 617-509-1955
pager: 781-226-2431

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *