Ever since this feature was introduced, there has been considerable confusion over what it does. Probably because of the terminology. Most of us use the term "prefetch" when referring to a technique for speeding up file I/O or, possibly, instruction processing in a CPU. But as Ken Paul of Adager said in responding to a question about TurboIMAGE Prefetch, "Unfortunately, this is not what is meant by the TurboIMAGE Prefetch flag."

Ken went on to give a great explanation of TurboIMAGE prefetch:

First, Prefetch is a flag that can be ENABLED for your database via DBUTIL. It was introduced way back in TurboIMAGE version C.03.13, which corresponds, to MPE/iX version 3.1. By default this flag is DISABLED for your TurboIMAGE databases and must be explicitly ENABLED using DBUTIL.

TurboIMAGE is currently 'single threaded' when it comes to doing DBPUTS, DBDELETES and most recently Critical Item DBUPDATES. This means that only ONE person is actually adding or deleting or updating a critical item at a time. To accomplish this, TurboIMAGE uses a semaphore lock known as the PUT/DELETE semaphore. This lock is applied at the beginning of the transaction and released at the end while several blocks are updated within the database to ensure data integrity.

The Prefetch flag tells TurboIMAGE when to apply this semaphore lock. Before Prefetch, and with Prefetch disabled, TurboIMAGE locks the PUT/DELETE semaphore before reading, updating and writing every data block that is modified by the transaction. After this is done, the PUT/DELETE semaphore is unlocked.

With Prefetch enabled, TurboIMAGE reads all the data blocks that are needed for the complete transaction before the PUT/DELETE semaphore is locked. Once the semaphore is locked, TurboIMAGE updates and writes the data blocks, which are modified by the transaction before unlocking the semaphore.

It is the holding of the semaphore lock for a shorter period of time that could cause more throughput of data on your system, but it is not guaranteed. There is the possibility that when the data is read or 'Prefetched' before the semaphore lock is applied that it may be flushed out of memory by another process before TurboIMAGE is able to update it. If this is the case, TurboIMAGE must go back out to disc to read in the block again, which could have a negative impact on your database performance.

The Prefetch flag has always been a 'your mileage may vary' flag since its introduction. Certain conditions need to exist on your system in order to possibly gain benefit from turning on this flag.

First, the system should have 'adequate' (a wonderfully nebulous word) memory available to handle the increased data page locality as well as adequate processor capability to handle increased concurrency of processes. Second, the application should make numerous calls to DBPUT, DBDELETE or, most recently, DBUPDATE to critical items. Third, multiple users must be processing data before a benefit can be realized.

The HP Database Lab has said that they have seen a 5 to 10 percent improvements in the number of transactions that can be processed in benchmarks as a result of enabling Prefetch. They also mention that it helps in situations where you have excess CPU time and you are running update (I think they mean DBPUT/DBDELETE/CIUDBUPDATE) intensive applications.

My suggestion on the Prefetch flag is to enable it and see if your environment changes noticeably. It can always be turned off if any problems appear.