Java – the fastest way to insert a very large number of records into a table in SQL

The problem is that we insert a large number of records (more than a million) into a single table from a Java application These records are created by java code and are not moved from another table, so insert / select will not help

At present, my bottleneck is the insert statement I'm using Preparedstatement to speed up the process, but I can't get more than 50 recods per second on an ordinary server The table is completely uncomplicated and has no index defined

This process takes too long, and the time it takes can cause problems

What can I do to get the maximum speed (insert per second)?

Database: MS SQL 2008 Application: Based on Java, using Microsoft jdbc driver

Solution

Use bulk insert – it's designed for what you're asking for and significantly improves insertion speed

In addition, (to prevent you from really having no index), you may also need to consider adding an index - some indexes (mostly indexes on the primary key) can improve the performance of insertion

The actual rate at which you should be able to insert records will depend on the exact data, table structure and the hardware / configuration of the SQL server itself, so I can't really give you any numbers