Skip to main content

How to handle high-volume inserts?

2 replies [Last post]
johncougar
Offline
Joined: 2009-06-16

I'm relatively new to MSSQL, and I'm trying to improve the DB write performance.

I have some legacy Java code that's generating about 1500 records every second. These 1400 records are generated by multiple Java threads. Right now, my approach is to create an SQL statement in each thread, to write each record into the MSSQL DB; this is proving to be terribly slow.

What are my options specific to MSSQL and Java? How do I insert so many records into the DB without seriously impacting performance?

Thanks in advance.

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
peter__lawrey
Offline
Joined: 2005-11-01

If Java is your performance bottleneck (which I doubt) you can use a profiler to help identify where the most CPU is being used.
However if your program is spending most of its time waiting for the database, this is really a database tuning question.
A trick which works for transactional databases is to batch up a large number of updates in one transaction. You should be able to try groups of 10 - 1000 updates in the same transaction.

johncougar
Offline
Joined: 2009-06-16

I understand that batch inserts could be faster; it's going to be a challenge to figure out the right "delay" while I cache records before insertion. My client applications need *near" real time read access to the data that's being inserted (the data is actually GPS data - latitude and longitude, amongst 50 fields - of 1500 vehicles worldwide, that are being tracked in real time).

Would it be faster/more efficient to do bulk inserts directly from my Java program, like so:
conn = getMySqlConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
conn.setAutoCommit(true);
stmt.addBatch("INSERT INTO survey(id, name) VALUES('11', 'Alex')");
stmt.addBatch("INSERT INTO survey(id, name) VALUES('22', 'Mary')");
stmt.addBatch("INSERT INTO survey(id, name) VALUES('33', 'Bob')");
stmt.executeBatch();

Or to store the records in CSV files, and periodically do a BULK INSERT (maybe from a seperate Java program or within SQL server itself... like a cron job), like so:
BULK INSERT OrdersBulk
FROM 'c:\file.csv'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

Any suggestions?
TIA.