Kbase 20206: Building Indexes with Binary Load
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/19/2009 |
|
Status: Verified
GOAL:
Building Indexes with Binary Load in Progress Version 9.1B and above
GOAL:
Improving performance of Binary Load
FACT(s) (Environment):
Progress 9.1B
Progress 9.1C
Progress 9.1D
Progress 9.1E
OpenEdge 10.x
FIX:
It is possible to rebuild the indexes while you run a binary load. Two new parameters are added in Version 9.1B that can be used with the PROUTIL binary load:
build indexes
-SS
The full syntax for the binary load is as follows:
proutil <dbname> -C load <dump file name> [build indexes][-TB <n>][-TM <n>] [-T <temp-dir> | -SS <sort file directory specification>]
If you want to build the indexes as you load the data, use the build indexes option.
As the data is loaded, the keys for each row are constructed and added to a sort file, just as index rebuild does in other versions. At the end of loading the data, the sort files are sorted and the indexes are built, also just as the index rebuild does in other versions.
The fact that binary load with the build indexes option builds indexes while loading data should result in better performance than binary load with a separate idxbuild session: in fact, if idxbuild is run separately, data has to be written to the database during binary load, and then it has to be re-read for sorting during idxbuild; binary load with the build indexes option eliminates the need for the re-read operation.
However (as with all performance considerations) we suggest that you perform thorough testing on your machines in case you are interested in assessing the exact performance gain of binary load with the build indexes option.
In Version 9.1B, the dump file is put in order by one of the indexes in the table. This index can be built while the loading is done without any sorting at all.
Please be aware if you script the binary load process for multiple table loads, Progress will want to automatically truncate the database's BI file between each table load. This truncation process has a 60 second delay(per table) prior to actually truncating the BI file. This overhead needs to be factored into the overall amount of time for the binary load script. To avoid this additional overhead use the "-G 0" parameter with the binary load "build indexes" parameter.
Example: proutil Dbname -C load tablename.bd build indexes -G 0
If the binary dump process created multiple dump files (such as . bd, .bd2, .bd3, etc.) you are not be able to use the build indexes option. Instead, you must use the regular binary load and then the rebuild all indexes option using the PROUTIL idxbuild command.
The -SS option overrides the -T option if used:
The -SS parameter is used to identify the location of a multi-volume sort file specification. This sort file shows the directories and the amount of available space in the directories that can be used for creation of temporary files for sorting. The syntax of the multi-volume sort file specification is the same as that used for index rebuild.
For example, in order to load the customer table, build the indexes, and use a multi volume sort file called /tmp/v91load (the file no longer requires the .srt extension), use the following syntax:
proutil dbname -C load customer.bd build indexes -SS /tmp/v91load
You cannot load a pre Version 9.1B dump file with an earlier PROUTIL utility release. It will not work. However, you can load a pre Version 9.1B dump file with the Version 9.1B PROUTIL utility and get the advantage of building the indexes while you load the dump file. Here, the indexes are built from the sort file (since the name of the index used to dump the table is not known).