1

I have a Java message listener, which upon receiving a message starts bulk inserting thousands of records into a table. After that it calls a few stored procedures sequentially that make some updates on the same table.

The problem I run into is that if I have multiple concurrent listeners, then the insert fails with a timeout. Note that the table has a field upload and stored proc. alter data only for rows with a given upload. Also I'm using SQLServerBulkCopy from Microsoft's JDBC driver.

Can changing the isolation level in stored procedure resolve the timeout issue? Or is there another way to improve this flow?

DDL

CREATE TABLE test.dbo.airport (
iata varchar(4) NOT NULL,
name varchar(255) NOT NULL,
city varchar(255) NOT NULL,
state varchar(255) NOT NULL,
country varchar(255) NOT NULL,
latitude varchar(255) NOT NULL,
longitude varchar(255) NOT NULL,
upload varchar(255) NOT NULL,
CONSTRAINT PK_iata_upload_name PRIMARY KEY (iata,name,upload)
) GO
CREATE INDEX IDX_city ON test.dbo.airport (city) GO
CREATE INDEX IDX_city_upload ON test.dbo.airport (city,upload) GO
CREATE INDEX IDX_country_upload ON test.dbo.airport (country,upload) GO
CREATE INDEX IDX_upload ON test.dbo.airport (upload) GO;

Java code (called by multiple threads at the same time):

private void upload(Resource resource, String upload) {
    try(var connection = dataSource.getConnection();
        var bulkCopy = new SQLServerBulkCopy(connection);
        var record = new AirportRecord(resource.getInputStream(), upload)) {
        log.info("start upload " + upload);

        SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
        copyOptions.setBatchSize(batchSize);
        copyOptions.setBulkCopyTimeout(100);

        bulkCopy.setDestinationTableName("airport");
        bulkCopy.setBulkCopyOptions(copyOptions);

        bulkCopy.writeToServer(record);

        log.info("start procs " + upload);
        CallableStatement cStmt = connection.prepareCall("{call updateCountry(?)}");
        cStmt.setString(1, upload);
        cStmt.execute();
    }
}

stack trace:

Exception in thread "pool-1-thread-17" com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.checkForTimeoutException(SQLServerBulkCopy.java:1199)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$300(SQLServerBulkCopy.java:63)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:715)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:733)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1669)
  • @DavidBrowne-Microsoft, I'm not inserting one record at a time, that piece of code is reading batchSize lines from file and is inserting them (docs.microsoft.com/en-us/sql/connect/jdbc/…). About ` have a single writer thread ` - it's going to be very slow and uploads will pile up in the queue – A5300 Jan 1 at 17:27
  • OK. The indexes on that table will probably prevent concurrent bulk inserts. I would probably stage the bulk insert into a temp table, and then insert into the target table and call the stored procedure in a transaction. – David Browne - Microsoft Jan 1 at 17:39
  • And remember to not to use a PreparedStatement to create a temp table, or won't end up session-scoped, and will be dropped at the end of the Statement execution. – David Browne - Microsoft Jan 1 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.