Joined: 30 Nov 2010 Posts: 28 Location: Chennai, Tamilnadu,INDIA
Could someone help in getting the optimized value for BUFND and BUFNI values for a Skip Sequential proceessing. I came accross a document in the forum and found the formula for calculating BUFND ad BUFNI values but it did not say for which accessing mode this formula would stick.
1. For BUFND use the number of CIs/CA in the data component
2. For BUFNI divide HURBA for index by index CIsize and add 5
Joined: 06 Jun 2008 Posts: 8339 Location: Dubuque, Iowa, USA
For optimizing direct access, you need at least one BUFNI for each level of the index (see the LISTCAT output for the number of levels of the index) and one BUFND; additional BUFNI will help more than BUFND since the index is being used to get to the appropriate DATA buffer.
For optimizing sequential access, the number of CI per CA may overdo it some (I typically find best results from 50 to 100 BUFND and if the data CI size is 4K you'd allocate 180 BUFND), while you don't really use the index buffers so BUFNI can be small.
Skip sequential processing combines the best (or worst) of each, so you probably ought to allocate plenty of BUFND and BUFNI in such a case. Look into LSR as well -- depending upon the amount of skipping being done, LSR might help. Knowing the data access pattern can help, but if your program starts near the beginning of the file, reads 3 records sequentially, goes near the end and reads 2 records sequentially, then skips to the middle of the file and reads 4 records -- there's not much that will improve performance for such a read pattern!
Joined: 14 Mar 2007 Posts: 8687 Location: Back in jolly old England
Robert, as the OP has stated skip-seq I have nearly always found that allocating large buffer numbers can also detriment performance as all of the buffers are refreshed at each change of access method.