Dump and Loads vs Index Rebuild and Row ID

[jennid]

Hello-We are on version 10.2B0709. We have type 2 storage areas. Database size is 350 GB. Database resides on an IBM AIX system.

We have been told by two different Progress consultants that since we are on type 2 storage areas, we should not need to do dump and loads...that doing an index rebuild will give us the same performance improvements we have typically seen after doing a dump and load. However, after doing index rebuilds, we only see slight improvements in performance...nothing as noticeable as when we used to do dump and loads.

First question - is this true that we no longer need to do dump and loads?

One reason that we have avoided doing dump and loads in the last year or so is because we have implemented a product called PRO2SQL (a Bravepoint product). It replicates data from our Progress database to a SQL reporting database. Other uesrs of PRO2SQL have told us that after doing a dump and load they have had to completely rebuild their SQL reporting database because PRO2SQL uses the rowid/recid as the unique record identifier between the Progress db and SQL.

Second question - Is there any way to do a dump and load and retain the original rowid/record ID's when the data is loaded back into the database?

Thanks.

 

[cj_brandt]

We have performed a dump and load for several databases (all below 150gb) that were already on type II storage because we changed OS platforms and we didn't see much of a change. Nothing like when we migrated from Type I to Type II. In a couple weeks we'll be going through the same process with a couple 600gb databases. If the thread is still active, I could post our results.

It would be good to investigate what is causing the performance issues. disk io, server issue, bad code, high reads.... Then you might have a better idea of whether an index rebuild or dump and load or something else may help. Do you have db stats during the times of poor performance ?

You won't be able to dump and load a table and keep the same recid's. I don't know what options you would have with the bravepoint product, we don't use it.

 

[TomBascom]

You might want to talk to consultants with DBA experience

No, you cannot preserve recids (or rowids).

With type 2 areas the need to dump & load will often be *less* frequent but I wouldn't go so far as to say it is eliminated. There is still the possibility that "logical scatter" might be a problem. Particularly if the most frequently used read indexes are in a different ordering than the ordering that records are created in. You might also have issues if rows per block is set wrong or the toss limit is too low.

A nice thing about type 2 areas is that, all other things being equal, dumping and reloading tends to be a whole lot faster than it is with type 1 areas.

In my experience index rebuild does basically nothing for performance. It is something that I mostly only ever use if there is an index corruption issue that needs fixing or a new index that needs activating. And in most of those cases the online alternatives are more useful.