Improving hash join performance through prefetching

• Published in: Proceedings. 20th International Conference on Data Engineering pp. 116 - 127
• Main Authors: Chen, S., Ailamaki, A., Gibbons, P.B., Mowry, T.C.
• Format: Conference Proceeding
• Language: English
• Published: Los Alamitos CA IEEE 2004; IEEE Computer Society
• Subjects: Applied sciences; Computer science; control theory; systems; Costs; Database systems; Delay; Electric breakdown; Exact sciences and technology; Information systems. Data bases; Memory organisation. Data processing; Partitioning algorithms; Prefetching; Probes; Software; Data path; Data dependency; Pipeline processor; Prefetching; Cache memory; Database; Hashing; Multipath propagation
• ISBN: 9780769520650; 0769520650
• ISSN: 1063-6382; 2375-026X
• DOI: 10.1109/ICDE.2004.1319989

Hash join algorithms suffer from extensive CPU cache stalls. We show that the standard hash join algorithm/or disk-oriented databases (i.e. GRACE) spends over 73% of its user time stalled on CPU cache misses, and explores the use of prefetching to improve its cache performance. Applying prefetching to hash joins is complicated by the data dependencies, multiple code paths, and inherent randomness of hashing. We present two techniques, group prefetching and software-pipelined prefetching, that overcome these complications. These schemes achieve 2.0-2.9X speedups for the join phase and 1.4-2.6X speedups for the partition phase over GRACE and simple prefetching approaches. Compared with previous cache-aware approaches (i.e. cache partitioning), the schemes are at least 50% faster on large relations and do not require exclusive use of the CPU cache to be effective.