JDBC insert - very slow

[Gordon Hughes]

Does anyone have experience of how to speed up the jdbc insert command? I am using SQLite to store data tables for an external application. Even cut down versions of the tables contain between 1.5 and 2 million records and the sizes will grow over time. The Stata version of one of the datasets is about 81 Mb. Currently it is taking about hour to execute the jdbc insert command for a whole table on a pretty fast Windows 10 machine.

The jdbc insert command has a rows(#) option that is said to improve performance. The default value of # is 1. The documentation refers to various limitations on this option including no long strings (not a problem), JVM heap size, and excluding certain jdbc drivers. I will try this but would like to have something to fall back on if it doesn't work.

An alternative option is to use odbc instead as I am using Windows. Mathworks gives advice suggesting that odbc is faster than jdbc provided you don't need cross-platform working. Unfortunately I do, but I might have to sacrifice that if the speed improvement is great enough. Again, does anyone have any experience?

[Gordon Hughes]

Let me answer my own question as it may be useful for others. Adding a suitable value of rows(#) to the jdbc insert command completely transforms its performance - and by much more than I had expected. I started with rows(100) and the insert time for a particular table with about 200,000 rows fell from 30 mins to about 20 secs. I increased the rows parameter to 10000 without any objection from Stata and the insert time fell to a fraction of a second. I haven't gone further as there isn't any point in running into other limitations. The rows(10000) works well with the standard JVM heapsize in Stata which in my case is 4 GB.