MySQL,JDBC query execution too long

Question

I've got database querying that has become too slow with my current implementation. I need to get all movies from a database and for each of these movies i need their file data from another table. So for each movie i am doing another query. For each candidate entry i need to do a comparison to every movie in the database. Should this be taking 5-10 seconds to execute for approximately 500 candidates?

// get movies with all their versions
private ArrayList<Movie> getDatabaseMovies(Connection conn) throws Exception {
    PreparedStatement getMoviesStmt = conn.prepareStatement("SELECT movieid, title FROM movies", Statement.RETURN_GENERATED_KEYS);
    ArrayList<Movie> movies = new ArrayList<Movie>();
    try {
        ResultSet rs = getMoviesStmt.executeQuery();
        while (rs.next()) {
            Movie movie = new Movie(rs.getString(2), getDatabaseMovieFiles(conn, rs.getInt(1)));
            movies.add(movie);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        getMoviesStmt.close();
    }
    return movies;
}

public ArrayList<MovieFile> getDatabaseMovieFiles(Connection conn, int movieID) throws Exception {
    ArrayList<MovieFile> movieFiles = new ArrayList<MovieFile>();
    PreparedStatement stmt = conn.prepareStatement("SELECT filename, size, hash, directory FROM file_video WHERE movieid = ?");
    try {
        stmt.setInt(1, movieID);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            MovieFile movieFile = new MovieFile(rs.getString(1), rs.getLong(2), rs.getBytes(3), rs.getString(4));
            movieFiles.add(movieFile);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        stmt.close();
    }

    return movieFiles;
}

The idea of a prepared statement is that we prepare a statement once and reuse it with different values. — Andreas Dolk
– Andreas Dolk, Commented Jun 6, 2011 at 5:53
I don't want to sound overly harsh or flippant, but this is some really basic stuff. Maybe some sort of "introduction to relational databases" is in order? There are plenty of excellent free resources out there. I fear that even though you will get the correct answer here, you will still be missing a lot of the foundation you will eventually need. — Dmitri
– Dmitri, Commented Jun 6, 2011 at 6:19
I guess add 'patronizing', I also didn't want to sound patronizing. — Dmitri
– Dmitri, Commented Jun 6, 2011 at 6:20
@Dmitri, Based on my question what foundational material do you recommend I lookup in particular? — el_pup_le
– el_pup_le, Commented Jun 6, 2011 at 6:23
based on your question? I'd say anything that covers RDBMS theory, a solid SQL tutorial, and an intro book on relational design. There are lots and lots of threads on SO with great recommendations for all of those. — Dmitri
– Dmitri, Commented Jun 6, 2011 at 6:44

Stephen C · Accepted Answer · 2011-06-06 06:09:31Z

4

Should this be taking 5-10 seconds to execute for approximately 500 candidates?

Probably not.

There are two ways to improve this:

Make sure that there is an index on the movieid column of file_video.
Combine the two queries into one by using a JOIN.

You probably should do both.

edited Jun 6, 2011 at 6:09

answered Jun 6, 2011 at 5:51

Stephen C

723k95 gold badges849 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

el_pup_le Over a year ago

I've got this: "SELECT M.movieid, M.title, MF.filename, MF.size, MF.hash, MF.directory FROM movies M, file_video MF WHERE M.movieid = MF.movieid" now i guess i just need to put some java logic around it to give me what i want.

Collectives™ on Stack Overflow

MySQL,JDBC query execution too long

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related