Evaluation of Methods for Rapidly Inserting Data into an Oracle Relational Database

摘要

Relational databases are a useful means of storing data in a convenient form for querying and selecting specific subsets from that data. In the Intelligence, Surveillance and Reconnaissance Division (ISRD) there are numerous applications that generate large volumes of data that need to be collected and analyzed, and the best way to store them is in such databases. Relational databases are used to store such data as they improve certain analytical tasks through querying. In the literature there is ample documentation of methods by which queries on a database may be optimized. However, there is scant information on the optimal technique for the initial insertion of data at high speed. This paper addresses the problem by investigating numerous insertion methods and comparing their performance for a given data set. The work was carried out on standard PCs running a commercially available database product (Oracle) and using common languages (such as Java, Pro(star)C, OCI, PL/SQL, SQL(star)Loader, JDBC, and SQLJ), and therefore may be of interest to the wider DSTO community. The insertion methods evaluated were string concatenation, bind variables, bulk binding, prepared statements, batch processing, and appending data. It was found that the optimal method for rapid insertion used bulk binding in prepared statements with infrequent COMMIT commands, using either Pro(star)C or OCI. Sample code for each of these cases is supplied in Appendix B. Appendix A provides a description of languages and utilities used in the study. The study also showed that measures such as binding, bulk processing and preparing statements have significant impacts as they reduce the number of calls to the SQL engine during execution. Other measures, such as using well-formed input files and accurately specifying parameter sizes, reduce preprocessing and also improve performance. (1 table, 3 figures, 9 refs.).