Why is my Spring Data JPA query 8 times slower than Node.JS + oracledb?

Question

I have created two basic projects to compare frameworks. When doing development on a recent project at work I noticed that the queries were running extremely slow when using Spring Data JPA.

I set up a small experiment to test NodeJS vs Spring Boot in order to find out if it was the database or the framework.

SELECT * FROM v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

This database is located 400 miles away at another facility, introducing around 60-80ms of network latency.

-------------- -------- ------------ 
ID             NOT NULL NUMBER       
AR                      VARCHAR2(10) 
MOD_TIME                DATE         
MOD_UID                 VARCHAR2(10) 
ACTIVE_IND              VARCHAR2(1)  
WORK_ID                 NUMBER       

There are 4533 records in this database in our test environment. We have approximately 9000 in production. This experiment will be run using the test environment.

Spring Setup:

start.spring.io and select Web ,JPA, Oracle Driver, lombok

Created a entity class

@Entity
@Table(name = "t_test")
@Data
public class TTest implements Serializable {

  private static final long serialVersionUID = 3305605889880335034L;

  @Id
  @Column(name = "ID")
  private int id;

  @Column(name = "AR")
  private String ar;

  @Column(name = "mod_time")
  private Timestamp modTime;

  @Column(name = "mod_uid")
  private String modId;

  @Column(name = "active_ind")
  private String activeInd;

  @Column(name = "work_id")
  private Integer wid;

}

and then a simple repository to run the findAll() query

@Repository
public interface TTestRepo extends JpaRepository<TTest, Integer> {}

and finally a controller

@RestController
@Slf4j
public class TestController {

    @Autowired
    TTestRepo repo;

    @GetMapping("/testDb")
    public List<TTest> testDb(){
        return repo.findAll();
    }

}

I used application.properties to connect to the databse

spring.datasource.url=blah
spring.datasource.username=blah
spring.datasource.password=blah
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG

NodeJS setup

Just a simple express app with oracledb installed.

const express = require('express')

var oracledb = require('oracledb')
oracledb.getConnection(
  {
    //removed for obvious reasons
  },
  function(err, connection) {
    console.log('trying to connect...')
    if (err) {
      console.error(err)
      return
    }

    global.connection = connection
  }
)

global.transformResults = function transformResults(result) {
  let finalResults = []
  let obj = {}

  result.rows.forEach((row) => {
    result.metaData.forEach( (meta, j) => {
      obj[meta.name] = row[j]
    })
    finalResults.push(obj)
    obj = {}
  })
  return finalResults
}

// Create express instnace
const app = express()

// Require API routes
const users = require('./routes/users')

// Import API Routes
app.use(users)

// Export the server middleware
module.exports = {
  path: '/api',
  handler: app
}

users.js is just a router or rest endpoint where I run my query

const { Router } = require("express");
const router = Router();

router.get("/testDb", async function(req, res, next) {
    connection.execute(
      "SELECT * from t_test",
      function(err, result) {
        if (err) {
          console.error(err)
          return
        }
        res.json(transformResults(result));
      }
    )
});

module.exports = router;

Benchmark tests

For Spring Data JPA I did this benchmark test

/**
 * @author Jake Perkins on 11/20/2019
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class BenchMark {

    @Autowired
    TestController controller;

    @Test
    public void benchmarkDb(){
        int testLength = 100;
        long startTime = System.currentTimeMillis();
        for(int i = 0; i < testLength; i++){
            controller.testDb();   //Measure execution time for this method
        }
        long endTime = System.currentTimeMillis();
        long durationInMillis = (endTime - startTime);   //Total execution time in milliseconds
        BigDecimal averageInSeconds = BigDecimal.valueOf(durationInMillis/testLength).movePointLeft(3);
        System.out.println(averageInSeconds);
    }

}

Output:

23.463

NodeJS benchmarks were calculated similarly using a difference between start time in milliseconds and end time in milliseconds.

Experiment results

I have run the query 100 times in both environments and have gathered the following average times.

Spring Boot: 23.4 seconds

NodeJS : 2.9 seconds

Oracle SQL Developer : 2.6 seconds

Spring boot is taking roughly 8 times longer than node JS when gathering 4533 records (in my specific case). Why?

Answer 1

My first thought is a difference in array fetch size or prefetch size. This can have a big impact on multi-row query performance over WANs.

From Oracle® Database JDBC Developer's Guide:

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor

From node-oracledb Documentation:

This property sets the size of an internal buffer used for fetching query rows from Oracle Database. Changing it may affect query performance but does not affect how many rows are returned to the application.

The default value is 100.

You can easily change oracledb.fetchArraySize to 10 in the Node.js app and see if performance drops to Spring's.

And you can increase the size to see if you get better performance.

Answer 2

I don't know anything about Spring Boot, but I'd expect the performance of all the options to be closer than what you're seeing. Does Spring Boot create a connection pool by default? If not, you should look into that.

Also, the Node.js code is reusing a single connection. You should be using a connection pool instead. See this series on creating a REST API with Node.js and Oracle Database for more info: https://jsao.io/2018/03/creating-a-rest-api-with-node-js-and-oracle-database/