JDBC Delete Works Slow

Question

I am having performance problem in JDBC Delete statement on sql server. The table Config contains about 7 million rows, table details:

Columns:

1. TERMINAL_ID (varchar(50))
2. ATTRIBUTE(varchar(50))
3. VALUE (nvarchar(1000))

Index: Clustered Unique Index On TERMINAL_ID AND ATTRIBUTE

The Code Looks like this, where attributes length is 1500 and this program needs about 1 hour to complete, which is very slow for delete:

     PreparedStatement statement = null;
     String sql = "DELETE FROM Config WHERE TERMINAL_ID = ? AND ATTRIBUTE = ?");
     for (String attribute : attributes) {
            if (statement == null) {
                statement = connection.prepareStatement(sqlDelete);
            }
            statement.setString(1, terminalId);
            statement.setString(2, attribute);
            statement.executeUpdate();
      }

When I run this query for 1500 times in Management Studio It needs just seconds to delete. Execution Plan looks like this: The Problem goes off when the number of rows is small.

The Problem goes off when I use createStatement instead of prepareStatement.

Any Idea ?

Answer 1

Try using preparedStatement.addBatch() this may improve performance,

 PreparedStatement statement = null;
 String sql = "DELETE FROM Config WHERE TERMINAL_ID = ? AND ATTRIBUTE = ?");
     for (String attribute : attributes) {
            if (statement == null) {
                statement = connection.prepareStatement(sqlDelete);
            }
            statement.setString(1, terminalId);
            statement.setString(2, attribute);
            statement.addBatch();
      }
statement.executeBatch();
//commit 

Answer 2

I recently ran into a similar issue with very slow DELETE performance through JDBC.

The operation was simple enough: DELETE FROM t WHERE id=?

The id field was the primary key.

However, I noticed in perfmon that the delete operations were triggering full scans.

As I understand it, the underlying problem was that the column was varchar not nvarchar and the JDBC driver was rewriting the delete to do an implicit conversion of id during the query rather than converting the literal value.

This conversion prevented the query from using the index.

When I switched the column to use nvarchar the problem went away.

Character data type conversion when using SQL Server JDBC drivers