1

I have a very simple query that takes 70 seconds when running through a very simple bare bones Spring Boot app using a direct JDBC connection, but when run directly on Oracle SQL Developer, it only takes 13 seconds. There are 7000 records in the table if that makes a difference.

The query is that I run from SQL Developer is very simple.

select id from task

I hit Ctrl+End when in SQL Developer to get all rows in the table, there are 7000 records returned.

My slower Java code consists of a Dao using Spring JDBC template.

@Autowired
private JdbcTemplate jdbcTemplate;

private static final String GET_ALL_TASKS = "SELECT id FROM task";

public List<Task> getAllTasks() {
   RowMapper<Task> rm = new TaskRowMapper();
   return this.jdbcTemplate.query(GET_ALL_TASKS, rm);
}

I have this POJO.

@Data
@AllArgsConstructor
public class Task {
  private Bigdecimal id;
}

And I have a controller that just calls the Dao.

@RestController 
public class TestController {

@Autowired 
private TaskDao taskDao;

@RequestMapping("/") 
public void test() { 
    taskDao.getAllTasks(); 
}

My TaskRowMapper class is empty and doesn't do anything as I was concerned maybe it was the cause of the slow performance.

With virtually no processing at all on the Java side, I am very confused as to how Java can run the query over 5x slower.

Has anyone got any suggestions, or is it a case that I have an unreasonable expectation to hope that my bare bones Java app should be able to run an Oracle query in only a second or two more than it takes to run on Oracle directly?

user2254180
  • 844
  • 13
  • 30
  • 1
    Can you tell exactly what's taking that time in the Java application by logging some stuff in several places? If it's acquiring the connection(s), the objects creation, etc.? Moreover, is your `ID` field indexed? I wonder why it would take even `13` seconds to fetch those. I'm assuming you don't have any crazy network settings or stored procedures kicking off when you execute queries against the server; if so, then those numbers might be OK to start with. – x80486 Aug 19 '19 at 15:20
  • 1
    So after you press CTRL+END, in how much time all data is populated in SqlDeveloper? – Popeye Aug 19 '19 at 15:37
  • 2
    Does increasing [the fetch size](https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html#setFetchSize-int-) make any difference? (You can change it to match SQL Developer's, and/or set SQL Developer's to 10000 for testing maybe.) – Alex Poole Aug 19 '19 at 15:39
  • @x804486 The ID field is not indexed and the Oracle data type used is NUMBER(10,0) - the table schema was auto generated by Hibernate (before my time working on this project). No crazy network settings or stored procedures used. Should an index matter here though? Regardless of whether the table is well defined or not, the queries should run in the same time since they are both executing directly? – user2254180 Aug 19 '19 at 15:40
  • @tejash - yes, once I hit Ctrl+End, I can scroll to see all 7000 records instantly after the 13 seconds it takes to run the query via Sql Developer – user2254180 Aug 19 '19 at 15:41
  • My assumption is that SQL Developer performs some kind of implicit pagination. After you navigate through your data it fetches additional pieces of it. But when you connect from your application it eagerly gets all the rows. – Vladimir Pligin Aug 19 '19 at 15:47
  • @AlexPoole - setting the fetch size in my Java code worked a charm. Do you want to write an answer so I can accept and give you credit? – user2254180 Aug 19 '19 at 15:51
  • 2
    It's really a duplicate - it's a well-known 'issue' and setting. (I say 'issue' because it's a trade-off; higher fetch size means fewer roundtrips to the database but more memory is needed to hold the results, so there isn't a universal right setting). Why your query takes 13 seconds anyway is probably a bigger issue, but not what you asked about... – Alex Poole Aug 19 '19 at 16:05
  • 1
    Default behaviour of SqlDeveloper fetches 50 records at a time. – Popeye Aug 19 '19 at 17:04

0 Answers0