Index build times with the HPL (longish posting)

Hi, I=92ve been doing some performance tests on the High Performance Loader. = (You may have seen my earlier post.) We are running ODS 7.23 on a dual = CPU HP server, running HP-UX 10.20. My tests involve loading a single, = initially empty, table with approx. 1.4 million rows using the HPL. The = data comes from a COBOL program, the data having fixed length fields. Con= sequently, some date and number conversion is required by the HPL. There = are 3 indexes required on the table. No other activity is present on the = server. MAXPDQPRIORITY is set to 90, PDQPRIORITY is set to 100. It is clear from monitoring the index build phase of the load that the = HPL relies on ODS to do the index build if the indexes exist at the time = of the data load (the bulk of the CPU activity is with the oninit rather = than the onpload threads). Onstat -g sql reports that the sql statement = is =91set indexes enabled=92 (or something like this) during the index = build. Note that creation of the indexes requires use of temporary dbspac= es (we have 4 temp dbspaces, each on a separate disk) to sort the key val= ues. However, if the you compare the elapsed times for building the indexes = after the load using a conventional SQL (create index ...) statement with= the time taken for building the indexes via the HPL i.e. the indexes are= created before the load starts, there is a significant time difference. = The times are as follows: Load time with no indexes: 276 secs Time to build indexes via SQL: 218 secs Load time with indexes in place, but disabled by the HPL: 276 secs Time for HPL to enable the indexes: 287 secs This leads me to suggest that if you are loading a table from empty with = the HPL, and wish to build indexes on the table, that you do so using an = SQL statement after the data is loaded. There are disadvantages with this= as it can mean more work if the data is not clean and you need to use = the HPL discard processing during the index build. However, I am intrigue= d as to why there is such a performance hit when using the HPL to build = indexes. The only things I can think of are: 1) The time to validate each key value for violation checking of unique = indexes. 2) Less resources available on the server because the HPL is running at = the same time. Has anyone else got any suggestions or comments? Regards Martyn