JDBC Delete Works Slow

Question

I have a performance problem in the JDBC Delete statement on sql server. The Config table contains about 7 million rows, table data:

Columns:

• TERMINAL_ID (varchar (50))
• Attribute (VARCHAR (50))
• VALUE (nvarchar (1000))

Clustered Unique Index TERMINAL_ID AND ATTRIBUTE

The code looks like this: the length of the attributes is 1500, and it takes about 1 hour to run this program, which is very slow to remove:

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 1,500 times in Management Studio, it only needs a few seconds to delete. The implementation plan is as follows: The problem disappears when the number of rows is small.

The problem disappears when I use createStatement instead of prepareStatement.

Any idea?

Answer 1

I found a problem and solved it. The problem was the preparedStatement.setString () method, which generated different requests, and the execution plan was different.

I opened the SQL Server activity monitor where the query was executed

DELETE FROM Config WHERE TERMINAL_ID = @ P0 AND ATTRIBUTE = @ P1

So, I right-clicked on the query and opened up an execution plan that looks like this:

As I already guessed, SQL Server called the CONVERT_IMPLICIT function for each row and ran the scan in the clustered index. This software is a third party, so I had to change the column to nvarchar and the problem disappeared.

Answer 2

Try using preparedStatement.addBatch() , this can 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 3

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

The operation was quite simple: REMOVE FROM t WHERE id =?

The id field was the primary key.

However, I noticed in perfmon that delete operations trigger a full scan.

As I understand it, the main problem was that the column was varchar, not nvarchar, and the JDBC driver rewrote the deletion to perform an implicit identifier conversion during the query, rather than converting the literal value.

This conversion did not allow the query to use the index.

When I switched the column to use nvarchar, the problem disappeared.

Character data type conversion using SQL Server JDBC drivers

Answer 4

Try disabling auto-messaging for this connection, and then do it manually after following all of your uninstall instructions:

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

I shortened exception handling for short. See the JDBC tutorial for a more detailed example.

Answer 5

Move prepareStatement outside the loop.

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