Improving the performance of the BDB system by changing the query processing temporary file structures

A performance study of the BDB system, a highly modular relational database management system, is conducted on the effects caused by changing the temporary file access method of the query processor from the direct access method to the record sequential access method. A built-in software monitor is used to measure query processing response lime. Experimental results show that the performance can be improved for selective type queries. For join type queries, two join algorithms, "nested-loop" and "sori-mcrge," are investigated. Interestingly, the direct access method performed better for the nested-loop join implementation while the record sequential access method performed belter for the sortmerge implementation. The observed outcomes of these experiments are reported and are discussed in [1]. The study is conducted on non-indexed data. The BDB system is benchmarked by conducting a series of controlled experiments. The experiments used three relations, each containing two attributes. The size of the relations varied, collectively, from 1, 100, 500, 1,000, 1,500, 7,500, and 10,000 tuples. For the record sequential access Files, the buffer size is varied from 3K, 6K, 18K, and 30K. Changing the buffer size from 3K up to 30K resulted in only a 1 to 2 percent improvement. This small percentage of improvement seems to contradict the well known fact that larger buffers reduce I/O costs. Some possibile reasons for this contradiction are that the physical data path of the microcomputer is loo small to allow large buffer efficiency and that the operating system reads and/or writes disk sectors which are of fixed size. A set of nine test queries is used to obtain response time measurements from the query processor. Briefly, some the queries selected all of the tuples of a relation, some of selected only one tuple, some selected half of the tuples, and others joined the relations. The results of investigating whether the record sequential access method is belter than the direct access method for supporting temporary files created during query processing have yielded several interesting Findings. First, the investigation revealed some inefficient code such as rereading data already in the buffer area and excessive copying of data from one buffer area to another. When improved, the performance is enhanced by about 85%. Second, the record sequential access method offered a 0-14% improvement over the direct access method for selective type queries. Specifically, for one tuple there was no improvement, for selecting one-half of the relation there was a 10% improvement, and for selecting the whole relation there was a 14% improvement. Two algorithms for the join were investigated. The nested-loop, which handles the many-to-many mapping, performed belter by about 50% when the direct access method is used. The sort-merge algorithm, which handles the one-to-many mapping, performed belter by about 40% when the record sequential access method is used. A possible reason for this is that the nested-loop procedure requires the rereading of one of the temporary files. In order to reread a record sequential File, the file must be closed and then reopened. For direct access files, the File remains open, and the record counter is reset to the first record in the file which eliminates the need to close and reopen the file. Thus, both join algorithms should be included in the BDB system and used appropriately depending on the mapping relationship between the two files being joined. REFERENCES 1. Trueblood, R. and Lai, P., "Improving the Performance of the BDB System by Changing the Temporary File Structure used for Query Processing," Tech. Report No. TR90003, Department of Computer Science, University of South Carolina, Columbia, South Carolina 29208 (1990). Permission to copy without fee all or part o f this material is granted provided that the copies ore not made or distributed for direct com­ mercial advantage, the ACM copyright notice and the title of the publication and its dale appear, and notice Is given that copying in by permission of the Association for Computing Machinery. To copy olhcrwlnc, or to icpublish, rci|iiircn o fee and/or s|>ccific per