CODEWITHSUNDEEP
javapostgresqljdbc
Arya
Arya

Reputation: 8965

Speeding up sql inserts on postgresql with JDBC?

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;
}

Upvotes: 0

Views: 670

Answers (2)

user140547
user140547

Reputation: 8200

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.

Upvotes: 2

helencrump
helencrump

Reputation: 1389

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

Upvotes: 0

Related Questions