Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: How to handle high-volume inserts?

    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.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    can you cache them out and bulk insert into the table ? not sure if you have triggers firing though for each insert.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're on SQL Server 2008 you can take advantage of table variable parameters and only execute a single statement (create a table object in memory and pass it as a parameter).

    If you're on 2005 or less; I assume you're not opening and closing a connection for each transaction?
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by gvee
    If you're on SQL Server 2008 you can take advantage of table variable parameters and only execute a single statement (create a table object in memory and pass it as a parameter).
    I'm on SQL Server 2005.

    Quote Originally Posted by gvee
    If you're on 2005 or less; I assume you're not opening and closing a connection for each transaction?
    I'm not; the connection remains open for the lifetime of the program.
    Last edited by kunalashar; 06-18-09 at 02:24.

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    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:
    Code:
            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:
    Code:
            BULK INSERT OrdersBulk 
                FROM 'c:\file.csv' 
                WITH 
                ( 
                    FIELDTERMINATOR = '\t', 
                    ROWTERMINATOR = '\n' 
                )
    Any suggestions?
    TIA.
    Last edited by kunalashar; 06-18-09 at 02:49. Reason: Marking Code

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    The java developers here do single row inserts, but not as high transaction as you are facing, they open conn, do tran, close conn with single row inserts via stored procedure call within a loop, and they pass each column value as a variable through the sproc via data obtained via vendor API.

    I guess you need to find out why it is slow, blocking connections ?, i/o waits ?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - DDL of the table would be handy too (all indexes, fks, triggers etc). These will make a big difference.

    Bulk inserts might be required, but should be a last resort IMHO. Depends on the latency you can accept too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by PMASchmed
    The java developers here do single row inserts, but not as high transaction as you are facing, they open conn, do tran, close conn with single row inserts via stored procedure call within a loop, and they pass each column value as a variable through the sproc via data obtained via vendor API.
    That's probably the slowest way of doing inserts in Java

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •