Speeding up sql inserts on postgresql with JDBC?

Question

I have two methods below for checking if a match is in the database and if not if would call the insert method. My program has to go through thousands of rows and it takes a very long time. Am I doing this incorrectly? Anything I can do to significantly make this faster?

public Boolean isMatchIdInDatabase(String matchId) throws SQLException
{
    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    Boolean exists = false;

    try
    {
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection(url, props);
        pst = conn.prepareStatement("SELECT COUNT(*) FROM match where match_id = ?");
        pst.setString(1, matchId);

        rs = pst.executeQuery();
        while (rs.next())
        {
            exists = rs.getBoolean(1);
        }

    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
    finally
    {
        pst.close();
        rs.close();
        conn.close();
    }

    return exists;
}

public Boolean insertMatchId(String matchId, String name, Timestamp birthdate, String bio, String accountId) throws SQLException, ClassNotFoundException
{
    Connection conn = null;
    PreparedStatement pst = null;
    Boolean exists = false;

    try
    {
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection(url, props);
        pst = conn.prepareStatement("INSERT INTO match (match_id, name, birthdate, bio, account_id) values(?, ? , ?, ?, ?)");
        pst.setString(1, matchId);
        pst.setString(2, name);
        pst.setTimestamp(3, birthdate);
        pst.setString(4, bio);
        pst.setString(5, accountId);

        pst.executeUpdate();

    }
    finally
    {
        pst.close();
        conn.close();
    }

    return exists;
}

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

Are you calling first isMatchIdInDatabase then insertMatchId for many records? Possible duplicate: Efficient way to do batch INSERTS with JDBC

It is an expensive operation to open a connection and query for a single record. If you do that thousands of times, it gets very slow. You should try to restructure your query so that you only use one SELECT. Then you can collect the records which you have to insert and doing it with batch insert.

edited May 23 '17 at 11:51

Community

1
1

answered Jun 25 '15 at 16:11

user140547

7,750
3
28
80

I think the link you gave me should do it – Arya Jun 25 '15 at 16:17
added details on connections – user140547 Jun 25 '15 at 16:20

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You could try changing your SQL query that inserts the row to insert only if the row isn't in the database by using WHERE NOT EXISTS. This post seems to be relevant - I know it's for MySQL instead of PostgreSQL but the principles should be the same. MySQL Conditional Insert

edited May 23 '17 at 10:26

Community

1
1

answered Jun 25 '15 at 16:11

helencrump

1,351
1
18
27

Speeding up sql inserts on postgresql with JDBC?

2 Answers2