Can I get "BULK INSERT"-like speeds when inserting from Java into SQL Server?
<p>During my quest to find the fastest method to get data from Java to SQL Server, I have noticed that the fastest Java-method I can come up with, is still 12 times slower than using BULK INSERT.</p>
<p>My data is being generated from within Java, and BULK INSERT only supports reading data from a text file, so using BULK INSERT is not an option unless I output my data to a temporary text file. This in turn, would of course be a huge performance hit.</p>
<p>When inserting from Java, insert speeds are around 2500 rows per second. <em>Even</em> when I measure the time <em>after</em> the for loop, and just before the executeBatch. So &quot;creating&quot; the data in-memory is not the bottleneck.</p>
<p>When inserting with BATCH INSERT, insert speeds are around 30000 rows per second.</p>
<p>Both tests have been done on the server. So network is also not a bottleneck. Any clue as to why BATCH INSERT is faster? And, if the same performance can be attained from within Java?</p>
<p>This is just a big dataset that needs to get loaded once. So it would be OK to temporary disable any kind of logging (already tried simple logging), disable indexes (table has none), locking, whatever, ...</p>
<p><strong>My test-setup so far</strong></p>
<p>Database:</p>
<pre><code>CREATE TABLE TestTable   
   (  Col1 varchar(50)
    , Col2 int);  
</code></pre>
<p>Java:</p>
<pre><code>// This seems to be essential to get good speeds, otherwise batching is not used.
conn.setAutoCommit(false);

PreparedStatement prepStmt = conn.prepareStatement(&quot;INSERT INTO TestTable (Col1, Col2) VALUES (?, ?)&quot;);
for (int i = 1; i &lt;= 10000; i++) {
    prepStmt.setString(1,&quot;X&quot;);            
    prepStmt.setInt(2,100);
    prepStmt.addBatch();
}
prepStmt.executeBatch();
conn.commit();
</code></pre>
<p>BULK INSERT:</p>
<pre><code>// A text file containing &quot;X 100&quot; over and over again... so the same data as generated in JAVA
bulk insert TestTable FROM 'c:\test\test.txt';
</code></pre>