Java Spring jdbcTemplate queryForList slow

Question

I'm using Spring jdbcTemplate to run MySQL queries in my java application. This is my function:

public static ArrayList<Map<String, Object>> query(String q) throws Exception {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    @SuppressWarnings("unchecked")
    List<Map<String, Object>> result = jdbcTemplate.queryForList(q);

    return (ArrayList<Map<String, Object>>) result;

}

In this particular case, I pass the function this query:

"SELECT * FROM test WHERE name IN('string1', 'string2', 'string3', ...)";

Table test only has 6 columns, strings in IN vary from few to 100 symbols. jdbcTemplate.queryForList() takes 280 miliseconds to run the query. NOTE: it takes 280 miliseconds for jdbcTemplate.queryForList() to run, not the whole function query. When I run exactly the same query on MySQL client (HeidiSQL) it only takes 16 miliseconds. UPDATE: Heidi maintains open connection, so that is not a fair comparison.

Question: why is jdbcTemplate.queryForList() so miserably slow in comparison to the same query ran on MySQL client? I'm using it purely for convienence reasons, it can retrieve result in ArrayList<Map<String, Object>> which is what I need. Is there anything I can do to speed it up or should I just drop jdbcTemplate all together and use something else?

UPDATE: OK, so I've changed my function from jdbcTemplate to using plain jdbc. This is how that abomination looks like. It's the reason why I went with jdbcTemplate in the first place. It now takes 200 miliseconds to run the same query - slight improvement, but still too slow...

public static ArrayList<Map<String,Object>> query(String Full_Command) {

    try {

        String URL = "jdbc:mysql://localhost/dbname";
        String USER = "root";
        String PASS = "";
        java.sql.Connection Con = DriverManager.getConnection(URL, USER, PASS);

        //create statement
        Statement Stm = null;
        //Stm = Con.createStatement();
        Stm = (Statement) Con.createStatement();

        //query
        ResultSet Result = null;
        boolean Returning_Rows = Stm.execute(Full_Command);
        if (Returning_Rows) {
            Result = Stm.getResultSet();
        } else {
            return new ArrayList<Map<String,Object>>();
        }

        //get metadata
        ResultSetMetaData Meta = null;
        Meta = Result.getMetaData();

        //get column names
        int Col_Count = Meta.getColumnCount();
        ArrayList<String> Cols = new ArrayList<String>();
        for (int Index=1; Index<=Col_Count; Index++) {
            Cols.add(Meta.getColumnName(Index));
        }

        //fetch out rows
        ArrayList<Map<String, Object>> Rows = new ArrayList<Map<String,Object>>();

        while (Result.next()) {
            HashMap<String,Object> Row = new HashMap<String,Object>();
            for (String Col_Name:Cols) {
                Object Val = Result.getObject(Col_Name);
                Row.put(Col_Name,Val);
            }
            Rows.add(Row);
        }

        //close statement
        Stm.close();

        //pass back rows
        return Rows;

    } catch (Exception Ex) {

        System.out.print(Ex.getMessage());
        return new ArrayList<Map<String,Object>>();

    }

}

UPDATE2: I have not broken down JDBC function into execution times, and this single line is the bottleneck, taking ~190 miliseconds every time:

java.sql.Connection Con = DriverManager.getConnection(URL, USER, PASS);

Any comments?

Solution 1

It's not JdbcTemplate's fault. Here's why:

First of all, you have to discount the overhead in establishing a connection. This overhead can be as much as 500 milliseconds. This is normal.

Second of all, when you run the query on MySQL client, are you running it immediately after you ran your program? Because queries get cached... even at the level of disk I/O. So if you run the same query twice, the second time it will run faster due to caching.

Third of all, are you indexing the column name?

Fourth of all, if the connection overhead really matters that much to you, you could consider conserving a connection or even connection pooling.

Solution 2

Try to increase fetch size (JdbcTemplate.setFetchSize), it might be the bottleneck.

Solution 3

I dont think you can get any faster than raw jdbc call. When you say that you are able fetch it faster with sql client then is it in first attempt? Client and db can cache results, so check then timing of first query run from your sql client after session is created. Also post java logs , that will help to see where time is spent.

Comments

[Caballero]

What exactly is fetch size? Nevermind that, found it - it's a setting.

[Caballero]

I've increased fetch size to 1000000000, still no change.

[Evgeniy Dorofeev]

fetch size is the number if rows driver reads from DB in one go

[Evgeniy Dorofeev]

OK then I'd suggest to try with pure JDBC and see if theres any difference in performance

[Caballero]

Definitely not a connection overhead, I noted that already. I only checked the execution time of the query alone.

[Caballero]

Yes that was first attempt with MySQL client, definitely no caching. What do you mean by java logs?

[Caballero]

See my latest update, found the one line that causes the bottleneck.

[Brian]

So, it is the get-connection that's slow...? That line is establishing the connection between your app and MySQL, and connecting typically is quite slow. Usually apps will use connection pools such as Apache Commons DBCP (there are others) for this reason.

[Caballero]

Yes, the first time I got confused, because I thought connection was initiated by creating jdbcTemplate, not when running the actual query.

[Caballero]

I have narrowed it down to connection overhead already. Although this is a lanuch-do-the-thing-and-close kind of java application, it doesn't run all the time, so would connection conserving or pooling still work in this case?

[ktm5124]

Connection pooling seems like overkill then. All you have to do is get a connection when your application starts, and then reuse that connection for all subsequent queries. Realize that connections take time to establish so you will have to deal with a 200ms wait at the start of your program (which is almost unnoticeable, anyway).

[Caballero]

I understand. This particular application only runs one query anyway. But the thing I don't understand is how PHP is capable of establishing the connection, running half a dozen queries and generating a page in under 100 ms and Java can't even establish a db connection in the same time.

[ktm5124]

It's not Java's fault. I highly doubt that PHP can do a "better job" of establishing connections than Java. I believe the MySQL client takes approximately the same amount of time. I would be very sure of your benchmarks before investigating this claim. You also have to ask yourself, is the PHP script using persistent connections? is it connecting to the same database? does it already reside on the machine that hosts the database? There are a lot of little things that could explain the difference.

[Brian]

does PHP not do connection pooling "invisibly" for you anyway?

[Caballero]

@Brian I suspect it does. That's why it's so fast. I need to do the same thing for java now somehow.

[Brian]

Apache Commons DBCP provides this, it just needs a little explicit config. Plays great with Spring as well, very easy to work with.

[Brian]

btw a little surprised you accepted the answer from someone who responded after we had established it was the DB connection causing the problem?

[Caballero]

@Brian It just so happens I'm trying to implement Apache Commons to do this and it's not working: stackoverflow.com/questions/18103852/…