Improve JDBC performance over Wide Area Networks

Idea created by jbarnum on Jan 23, 2018
    Active
    Score11

    Currently, all JDBC inserts, deletes, and update statements require a separate round trip to FileMaker Sever for each record. This makes it impossible to do efficient transfer of large batches of records across a WAN. An example would be if somebody wanted to use JDBC to sync data between L.A. and New York.

     

    This happens even when using the batch feature of JDBC with PreparedStatements. For instance, this pseudo-code demonstrates how batched statements work:

     

    PreparedStatement ps = connection.prepareStatement("INSERT INTO foo(firstName, lastName) VALUES(?,?)");
    for( Contact eachContact : allContacts ) {
      ps.setString( 1, eachContact.getFirstName() );
      ps.setString( 2, eachContact.getLastName() );
      ps.addBatch();
    }
    ps.executeBatch();
    connection.commit();
    

     

    When operating this way, the JDBC driver has all of the information in advance that it needs to do the operation with a single network call (or at least very few network calls). However, my benchmarks have shown that the executeBatch() call makes separate round trips for each record.

     

    I propose changing the underlying network protocol to send the entire batch of changes to the server, and then having the server-side xDBC listener process move the records one at a time to the FileMaker Server engine. This would give tremendously better performance, since the round trips would happen inside a single machine as opposed to over a WAN.

     

    This would open up opportunties for multiple FileMaker Servers to be deployed across geographies. This would lead to much faster performance and better customer satisfaction at each geographic location, as well as increased sales of FMS.