Slow SQL Server performance with multiple PreparedStatement loop I have a need to update thousands of database records at a time. I am using a loop to generate the necessary PreparedStatement updates, but am hoping for a more efficient method.

Normally, I would try to use a batch operation, but since the number of fields I need to update for each record differs, I need a new PreparedStatement for each update.

As it is, updates around 2,000 records takes 3-4 minutes.

            for (AgentUpdate update : updateList) {

                Agent thisAgent = update.getAgent();

                // Prefix for updating each agent
                StringBuilder sql = new StringBuilder(
                        "UPDATE REP_ASSIST.AGENTS SET\n"
                );

                // Create a map of all changes
                Map&lt;String, String&gt; updateMap = new HashMap&lt;&gt;();
                for (AgentDiffs diff : update.getChanges()) {
                    updateMap.put(diff.getAgentField().toString(), diff.getNewValue());
                }

                // Iterate through the map and build the SQL statement,
                // including all fields to be updated
                Iterator iterator = updateMap.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry pair = (Map.Entry) iterator.next();
                    sql.append(pair.getKey()).append("=?");

                    if (iterator.hasNext()) {
                        sql.append(",\n");
                    }
                }
                sql.append("\nWHERE AGENT_CODE=? AND UPN=?;");
                Utility.printTempMsg(sql.toString());

                // Create the PreparedStatement and fill each ? with the
                // appropriate new value.
                ps = connection.prepareStatement(sql.toString());
                int paramNum = 1;
                for (String fieldName : updateMap.keySet()) {
                    String newValue = updateMap.get(fieldName);
                    ps.setString(paramNum, newValue);

                    // Increase the parameter count
                    paramNum++;
                }

                // We are now to our last two parameters, fill them
                ps.setString(paramNum++, thisAgent.getAgentCode());
                ps.setInt(paramNum, thisAgent.getAgentUpn());

                ps.executeUpdate();
                count++;

                Utility.printTempMsg(sql.toString());

                // Update the progress based on total count so far
                updateProgress(count, totalOperations);


            }


So how does one handle large dynamic updates of this sort?
Here are some ideas to try:


You are not closing your PreparedStatements.  There might possibly be some slow down due to resource consumption or deadlock.  Use the try-with-resources feature so it will automatically close.

for (AgentUpdate update : updateList) {
    try (PreparedStatement ps = connection.prepareStatement(sql.toString())
    {
        // set parameters
        ps.executeUpdate();
    }
}

Alternatively, you are creating a custom query and creating a new PreparedStatement for each record.  Although you are optimizing individual queries by minimizing the number of fields you are updating, you are creating additional overhead by preparing a new statements for each record.  If you update every field for every row, you could re-use the same PreparedStatement, which may end up being more efficient (depending on the number of fields and length of data you are actually updating).  Just overwrite the values in the prepared statement and redo the executeUpdate() on it.

try (PreparedStatement ps = connection.prepareStatement(sql.toString())) {
    for (AgentUpdate update : updateList) {
        // set parameters
        ps.executeUpdate();
    }
}