Slow JDBC & Hibernate query performance (compared to PhpMyAdmin)

Question

I am working on a Spring MVC/Hibernate web app and the DB queries are awfully slow. I already searched, but none of the performance problems people asked about here seem to match my problem (suggestions included issues with model mapping or connection pooling).

No matter if I use plain JDBC or Hibernate to query the DB it takes equally long. To make sure it is not related with the Spring framework I wrote this simple application:

public class JDBCTest {

  static final String JDBC_DRIVER = ...;  
  static final String DB_URL = ...;
  static final String USER = ...;
  static final String PASS = ...;

  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    double time;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      start = System.currentTimeMillis();
      conn = DriverManager.getConnection(DB_URL,USER,PASS);
      System.out.println("Conn. duration: " + (System.currentTimeMillis() - start));
      stmt = conn.createStatement();
      String sql = "SELECT * FROM temperature WHERE SensorId = 1 AND metered >= '2016-09-15 14:00:00' AND metered <= '2016-09-16 20:00:00'";
      start = System.currentTimeMillis();          
      ResultSet rs = stmt.executeQuery(sql);
      System.out.println("Query duration: " + (System.currentTimeMillis() - start));
...

While opening the connection takes 200-300ms (seems reasonable), the query takes 1500-2000ms.

When executing the very same query in PHPMyAdmin it says the query took 0.0312 seconds (compared to >1 second with JDBC).

What could be the issue? No matter if it is a minimalistic JDBC app as shown above, the Spring app using JDBC or using Hibernate - the query always takes roughly the same amount of time.

It doesn't seem to have to do with the timestamps either, as even simple "SELECT * FROM temperature" queries are many times slower than what PHPMyAdmin takes.

Which database do you use (this is very relevant)? See [here](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=oldest#tab-top) for some hint, what information you should provide to troubleshoot query performance problems with Oracle. Also try to narrow the problem, do you have this behaviour with plain JDBC (i.e. without Spring)? — Marmite Bomber, Mar 30 '17 at 13:33
It is MySQL 5.7.17. And yes, these problems occur even with plain JDBC (w/o Spring, w/o Hibernate, w/o DataSource), see code example above. — Pawel Os., Mar 30 '17 at 13:46
How may rows are returned with the WHERE condition? How much rows have the whole table. If the number of returned rows ist small, do you have an index on `SensorId, metered`. And - sorry for asking it, but it happens - are you sure connected to the same database with your PhPAdmin? — Marmite Bomber, Mar 30 '17 at 14:15
"opening the connection takes 200-300ms" is NOT reasonable. It is normally much, much faster than that. This makes me suspect a network issue like very slow ping, low bandwidth, slow VPN, paranoid firewall which insists in reverse-DNSing EVERYTHING, etc, between the machine that runs your Java code and the DB server. If phpmyadmin runs on the same server as the DB, or on a gigabit LAN, its network accesses will be lightning quick in comparison. — bobflux, Mar 30 '17 at 14:23
No, there is no index on SensorId and metered. Yet, it certainly is the same database. The DB runs on a computer in our LAN, so networking should not be an issue, yet I tried connecting a DB running on localhost (application, DB, PhpMyAdmin - all of them running on localhost): same behaviour. PHPMyAdmin is way quicker than JDBC. It returns roughly 60k rows (out of 700k). — Pawel Os., Mar 30 '17 at 14:26
By default MySQL Connector/J retrieves all rows at once, if this query selects 60k rows, then they will all be materialize at once (probably contrary to what PHP My Admin does), which might also result in delays because of full garbage collection cycles until sufficient memory has been allocated. — Mark Rotteveel, Mar 30 '17 at 15:38

score 1 · Answer 1 · answered Apr 01 '17 at 05:28

For that query, this composite index is optimal:

INDEX(SensorId, metered)

Why would a query run faster the second time? Usually because of caching. Or it could be that one driver gets the rows incrementally; the other gets them all at once.

If you want to discuss further, please provide SHOW CREATE TABLE and EXPLAIN SELECT ...;

Slow JDBC & Hibernate query performance (compared to PhpMyAdmin)

1 Answers1