fastest way to delete thousands of rows from postgres db tables using java

Question

fastest way to delete thousands of rows from postgres db tables using java.Presently i am using the following code snippet:

query = "DELETE FROM NODE WHERE SEQUENCE_NO = " + sequenceNo;
            insertOrDelete(query);




insertOrDelete(String query){
    statement = createStatement();
    numRowUpdated = statement.executeUpdate(query);
    closeStatement(statement);
            }

java relates to javascript like car relates to carpet. Pick one. — zerkms
– zerkms, Commented Aug 22, 2012 at 4:30
Impossible to help improve delete performance without knowing the target database. Come on! Put some thought in your question. — Strelok
– Strelok, Commented Aug 22, 2012 at 5:04

Axel · Accepted Answer · 2012-08-22 06:25:22Z

2

It's not a Java problem - it's the database.

Make sure there is an index on SEQUENCE_NO. Beyond that, ask the database gurus (depending on the database, there might be things that can improve performance, but in the end it comes all down to database administration: chosing the correct data type for SEQUENCE_NO, keeping meta data up to date etc.).

You probably should add an SQL tag to your question.

answered Aug 22, 2012 at 6:25

Axel

14.2k6 gold badges55 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mauhiz · Accepted Answer · 2012-08-22 05:13:46Z

1

If you are already using a single query, then whether it is Java or anything else, I don't think it can get much faster.

Some tips :

use a PreparedStatement. It may be slightly faster, and it will be cleaner/safer/better
close the statement in a finally

answered Aug 22, 2012 at 5:13

mauhiz

5015 silver badges14 bronze badges

Comments

PepperBob · Accepted Answer · 2012-08-23 17:29:28Z

1

You might want to consider building a statement like

DELETE FROM NODE WHERE SEQUENCE_NO In (...);

This would do your job in one commit instead of committing several times which would perform better in any case.

answered Aug 23, 2012 at 17:29

PepperBob

7073 silver badges9 bronze badges

Collectives™ on Stack Overflow

fastest way to delete thousands of rows from postgres db tables using java

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related