JDBC & MySQL read performance

Question

I really seem to have a big problem here. I'm using MySQL to store part-of-speech tagged sentences in a table. The Table looks like this:

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| idTitle    | varchar(25)      | NO   | PRI | NULL    |       |
| idReview   | int(10) unsigned | NO   | PRI | NULL    |       |
| idSentence | int(10) unsigned | NO   | PRI | NULL    |       |
| content    | text             | NO   |     | NULL    |       |
| POSInfo    | text             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

These are the indexes on the table:

+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| reviewsentences |          0 | PRIMARY                     |            1 | idSentence  | A         |          23 |     NULL | NULL   |      | BTREE      |         |
| reviewsentences |          0 | PRIMARY                     |            2 | idTitle     | A         |       32087 |     NULL | NULL   |      | BTREE      |         |
| reviewsentences |          0 | PRIMARY                     |            3 | idReview    | A         |     2470720 |     NULL | NULL   |      | BTREE      |         |
| reviewsentences |          1 | fk_ReviewSentences_Reviews1 |            1 | idTitle     | A         |         983 |     NULL | NULL   |      | BTREE      |         |
| reviewsentences |          1 | fk_ReviewSentences_Reviews1 |            2 | idReview    | A         |      494144 |     NULL | NULL   |      | BTREE      |         |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

I'm trying to read the reviewsentences that blong to a certain review an add them to the a review object. I'm accessing the Database via JDBC and the reads take forever!! I'm talking 2 minutes for 26 rows! This is the java code I'm using to query the database:

public List<Review> fillupReviews(List<Review> reviews, boolean tagged){

    try {
        Statement stmt = dbConnection.createStatement() ;


        for (Review review : reviews) {
            ResultSet rs=null;
            if(tagged==true){
                rs = stmt.executeQuery("SELECT idSentence, POSInfo FROM reviewsentences WHERE idTitle="+review.getMovieID()+" and idReview="+review.getReviewID()+";") ;
            }else{
                rs = stmt.executeQuery("SELECT idSentence, content FROM reviewsentences WHERE idTitle="+review.getMovieID()+" and idReview="+review.getReviewID()+";") ;
            }

            while(rs.next()){
                review.addTaggedSentence(rs.getInt(1),rs.getString(2));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return reviews;
}

If I access the same table with the same query via the MySQL Workbench it takes 0.296 seconds?? So my guess is there has to be something seriously wrong! But I really can not see what goes wrong or what to change to speed this darn thing up. Please can someone give me a hint?

It's me again, I finally found the solution! Is called Prepared Statement!! <-- who would have guessed!? Here is the code:

public List<Review> fillupReviews(List<Review> reviews, boolean tagged){

        try {

            PreparedStatement selectReview=null;
            if(tagged==true){
                selectReview = dbConnection.prepareStatement("SELECT idSentence, POSInfo FROM reviewsentences WHERE idTitle= ? AND idReview= ?;");
            }else{
                selectReview = dbConnection.prepareStatement("SELECT idSentence, Content FROM reviewsentences WHERE idTitle= ? AND idReview= ?;");
            }

            for (Review review : reviews) {

                selectReview.setString(1, review.getMovieID());
                selectReview.setInt(2, review.getReviewID());

                ResultSet rs = selectReview.executeQuery();

                while(rs.next()){
                    review.addTaggedSentence(rs.getInt(1),rs.getString(2));
                }
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return reviews;
    } 

Now this hole thing runs like hell (nearly as fast as MySQL Workbench does[0.3 sec]). What i do not exactly get is why a normal statement is so slow? Has someone an explanation for that?

Answer 1

First, are you timing just this method call?

Where do you get the db connection, are you timing just the time to execute the query or the time to get the connection also?

Are you using connection pooling? Maybe there is an issue there, try getting a new connection first to narrow it down.

Regardless it shouldn't take this long, something is wrong, I suspect with your connection setup maybe the way java is finding mysql (is it local, are you using dns etc).

Also I would use prepared statements, they are more secure and better performing.

Also what driver are you using?